MySQL5.7中JSON的基本操作MySQL从5.7版本开始支持JSON格式的数据，操作非常方便。创建表时，字段类型可以直接设置为json类型。比如我们创建一个表：mysql>CREATETABLE`test_user`(`id`INTPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(50)NOTNULL,`info`JSON);json类型字段可以为NULL插入数据：mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaoming','{"sex":1,"age":18,"nick_name":"Xiaomeng"}');json类型字段必须是有效的json字符串。您可以使用JSON_OBJECT()函数构造一个json对象：mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaohua',JSON_OBJECT("sex",0,"age",17));使用JSON_ARRAY()函数构造一个json数组：mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaozhang',JSON_OBJECT("sex",1,"age",19,"tag",JSON_ARRAY(3,5,90)));现在查看test_user表中的数据：mysql>select*fromtest_user;+----+----------+--------------------------------------------+|编号|姓名|信息|+----+------------+----------------------------------------+|1|小明|{"age":18,"sex":1,"nick_name":"小萌"}||2|小华|{“年龄”：17，“性别”：0}||3个|小张|{“年龄”：19，“性别”：1，“标签”：[3,5,90]}|+----+------------+----------------------------------------+3rowsinset(0.04sec)查询表达式：对象为jsoncolumn->'$.key'，数组为jsoncolumn->'$.key[index]'mysql>selectname,info->'$.nick_name',info->'$.sex',info->'$.tag[0]'fromtest_user;+------------+---------------------+--------------+--------------------+|姓名|信息->'$.nick_name'|信息->'$.sex'|信息->'$.tag[0]'|+------------+--------------------+--------------+--------------------+|小明|《小萌》|1|空||小华|空|0|空||小张|空|1|3|+------------+--------------------+--------------+----------------+3rowsinset(0.04sec)equivalentIn:theobjectisJSON_EXTRACT(jsoncolumn,'$.key'),数组是JSON_EXTRACT(jsoncolumn,'$.key[index]')mysql>selectname,JSON_EXTRACT(info,'$.nick_name'),JSON_EXTRACT(info,'$.sex'),JSON_EXTRACT(info,'$.tag[0]')fromtest_user;+------------+----------------------------------+----------------------------+------------------------------+|姓名|JSON_EXTRACT(信息,'$.nick_name')|JSON_EXTRACT(信息,'$.sex')|JSON_EXTRACT(信息,'$.tag[0]')|+------------+------------------------------------+----------------------------+------------------------------+|小明|《小萌》|1|空||小华|空|0|空||小张|空|1|3|+------------+--------------------------------+---------------------------+-----------------------------+3rowsinset(0.04sec)但是看到上面的"xiaomeng"是带双引号的，这不是我们想要的，可以使用JSON_UNQUOTE函数去掉双引号mysql>选择名字，JSON_UNQUOTE(info->'$.nick_name')fromtest_userwherename='xiaoming';+------------+--------------------------------+|姓名|JSON_UNQUOTE(info->'$.nick_name')|+------------+-----------------------------------+|小明|小萌|+------------+-----------------------------+集合中的1行(0.05秒)也可以直接使用运算符->>mysql>selectname,info->>'$.nick_name'fromtest_userwherename='xiaoming';+------------+----------------------+|姓名|信息->>'$.nick_name'|+------------+----------------------+|小明|小萌|+------------+--------------------+1rowinset(0.06sec)当然也可以使用属性作为查询条件mysql>selectname,info->>'$.nick_name'fromtest_userwhereinfo->'$.nick_name'='Xiaomeng';+----------+--------------------+|姓名|信息->>'$.nick_name'|+-----------+--------------------+|小明|小萌|+------------+----------------------+1行set(0.05sec)值得一提的是，可以通过虚拟列快速查询JSON类型的指定属性创建一个虚拟列：mysql>ALTERTABLE`test_user`ADD`nick_name`VARCHAR(50)GENERATEDALWAYSAS(i??nfo->>'$.nick_name')VIRTUAL;使用和普通类型的列查询时注意使用operator->>是一样的：mysql>selectname,nick_namefromtest_userwherenick_name='Xiaomeng';+------------+------------+|姓名|昵称|+-----------+------------+|小明|小萌|+------------+-------------+1rowinset(0.05sec)更新使用JSON_INSERT()插入新值，但不会覆盖现有值mysql>UPDATEtest_userSETinfo=JSON_INSERT(info,'$.sex',1,'$.nick_name','小花')whereid=2;查看结果mysql>select*fromtest_userwhereid=2;+----+--------+--------------------------------------+------------+|编号|姓名|资讯|昵称|+----+--------+--------------------------------------------+------------+|2|小华|{"age":17,"sex":0,"nick_name":"小花"}|小花|+----+--------+----------------------------------------------+------------+集合中的1行（0.06秒）使用JSON_SET()插入新值，并覆盖已有值mysql>UPDATEtest_userSETinfo=JSON_INSERT(info,'$.sex',0,'$.nick_name','XiaoZhang')whereid=3;查看结果mysql>select*fromtest_userwhereid=3;+----+------------+---------------------------------------------------------------+------------+|编号|姓名|资讯|昵称|+----+------------+------------------------------------------------------------+-------------+|3|小张|{"age":19,"sex":1,"tag":[3,5,90],"nick_name":"小张"}|小张|+----+------------+-----------------------------------------------------------+------------+1行（0.06秒）使用JSON_REPLACE()只替换现有值mysql>UPDATEtest_userSETinfo=JSON_REPLACE(info,'$.sex',1,'$.tag','[1,2,3]')whereid=2;查看结果mysql>select*fromtest_userwhereid=2;+----+--------+--------------------------------------------+------------+|编号|姓名|信息|昵称|+----+--------+---------------------------------------------+------------+|2|小华|{"age":17,"sex":1,"nick_name":"小花"}|小花|+----+--------+-------------------------------------------+------------+1rowinset(0.06sec)可以看到tag没有被更新和删除使用JSON_REMOVE()删除JSON元素mysql>UPDATEtest_userSETinfo=JSON_REMOVE(info,'$.sex','$.tag')whereid=1;查看结果mysql>select*fromtest_userwhereid=1;+----+-----------+----------------------------------+-----------+|编号|姓名|资讯|昵称|+----+------------+-------------------------------+------------+|1|小明|{"age":18,"nick_name":"小萌"}|小萌|+----+------------+----------------------------------+------------+一组中的1行（0.05秒）
