mySql 5.7 json 类型操作

1、建表

CREATE TABLE `t_json_test` (  
  `id` int(11) NOT NULL,  
  `c_json` json DEFAULT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  

2、插入数据

insert into t_json_test values (1, '{"p1":1,"p2":"ppp111"}'), (2, '{"p1":2,"p2":"ppp222"}');

3、查询

select json_extract(c_json, '$.p1') as p1, json_extract(c_json, '$.p2') as p2 from t_json_test;  
select c_json->'$.p1' as p1, c_json->'$.p2' as p2 from t_json_test;

4、给JSON字段插入新值，但不覆盖

update t_json_test set c_json=JSON_INSERT(c_json, '$.p3', "PPP333") WHERE ID = 1; 

5、给JSON字段插入新值，如果已存在，则覆盖已存在的值

update t_json_test set c_json=JSON_SET(c_json, '$.p3', "PPP3444") WHERE ID = 1;

6、只替换已存在的值

update t_json_test set c_json=JSON_REPLACE(c_json, '$.p4', "PPP666") WHERE ID = 1;

7、删除属性，可删除多个

update t_json_test set c_json=JSON_REMOVE(c_json, '$.p2', '$.p3') WHERE ID = 1;

8、以json属性为条件查询

SELECT * FROM t_json_test where json_extract(c_json, '$.p2') like '%p%'

若有凝问或错误，请指出，我好及时改正，让我们一起进步!
email ： vinson_bs@126.com
qq ： 103 586 2795
敲门砖： 代码谱写人生