欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySql 5.7 json 类型操作

程序员文章站 2024-01-17 13:26:22
...
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 : binary_space@126.com
qq     : 1035862795
敲门砖: 代码谱写人生





相关标签: mysql json