mysql如何插入更新搜索json数据类型的字段？

mysql从5.7开始已经支持JSON类型的字段，之前的不支持json数据类型。

一,对记录的操作

1.创建有json字段的表

-- 创建表

CREATE TABLE t_json(id INT PRIMARY KEY, sname VARCHAR(20) , info JSON); 2.插入记录 -- 插入含有json数组的记录 INSERT INTO t_json(id,sname,info) VALUES( 1, 'name1', JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())); -- 插入含有json对象的记录 INSERT INTO t_json(id,sname,info) VALUES( 2, 'name2', JSON_OBJECT("age", 20, "time", now())); INSERT INTO t_json(id,sname,info) VALUES( 3, 'name3', '{"age":20, "time":"2018-07-14 10:52:00"}'); 3.查询记录 -- 查询记录 SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json; SELECT sname,info->'$.age' FROM t_json; -- 查询key SELECT id,json_keys(info) FROM t_json; 4.修改记录 -- 增加键 UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.1') WHERE id = 2; -- 变更值 UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.2') WHERE id = 2; -- 删除键 UPDATE t_json SET info = json_remove(info,'$.ip') WHERE id = 2;

二,创建json值函数

1.JSON_ARRAY 生成json数组 -- JSON_ARRAY(val1,val2,val3...) -- 生成一个包含指定元素的json数组。 SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); -- [1, "abc", null, true, "10:37:08.000000"] 2.JSON_OBJECT 生成json对象 -- JSON_OBJECT(key1,val1,key2,val2...) -- 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。 SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"age": 20, "time": "2021-06-18 14:49:57.000000"} 3.JSON_QUOTE 加"号 -- JSON_QUOTE(json_val) -- 将json_val用"号括起来。 SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]"

三,搜索json值函数

1.JSON_CONTAINS 指定数据是否存在 set @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; -- JSON_CONTAINS(json_doc, val[, path]) -- 查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。 SELECT JSON_CONTAINS(@j, '4', '$.c.d'); -- 1 2.JSON_CONTAINS_PATH 指定路径是否存在 -- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) -- 查询是否存在指定路径，存在则返回1，否则返回0。如果有参数为NULL，则返回NULL。 -- one_or_all只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。 SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 1 SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.c.d'); -- 1 3.JSON_EXTRACT 查找所有指定数据 -- JSON_EXTRACT(json_doc, path[, path] ...) -- 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。 set @j2 = '[10, 20, [30, 40]]'; SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); -- 20 SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); -- [20, 10] SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); -- [30, 40] 4.JSON_KEYS 查找所有指定键值 -- JSON_KEYS(json_doc[, path]) -- 获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。 SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); -- ["a", "b"] SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); -- ["c"] SELECT id,json_keys(info) FROM t_json; 5.JSON_SEARCH 查找所有指定值的位置 -- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) -- 查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。 -- one_or_all："one"表示查询到一个即返回；"all"表示查询所有。 -- search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。 -- path：在指定path下查。 SET @j3 = '...

点击查看剩余70%