jsonmysql字段默认值_超详细的mysql数据库json字段类型语
法⼤全
概述
以前，我们⼀般选⽤ MySQL 的 VARCHAR 或 TEXT 等作为这类复合数据字段的类型。从5.7.8开始，MySQL将 JSON 作为标准的字段类型之⼀。
与JSON格式的纯⽂本字段相⽐，JSON类型的字段有以下优势：
⾃动校验JSON格式。如果添加的数据不符合JSON规范将会报错。
注意：MySQL中合法的JSON字符串格式与我们通常处理的JSON数据可能有些不同。某些场景下，我们习惯将JSON字符串解析成对象({…})或数组([…])，⽽不考虑单个值的情况(如：“1”)。
存储格式经过优化。读取JSON内容项的速度更快。
因为MySQL提供的内部数据结构允许通过内容项的key或index直接访问⽬标数据，⽽⽆需将处理其它数据。⽐以前的上层应⽤读取整块内容再解析的⽅式更快。
⼀、对记录的操作
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;-- 查询keySELECT 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,)-- ⽣成⼀个包含指定元素的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,)-- ⽣成⼀个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"id": 87, "name": "carrot"}
3.JSON_QUOTE 加”号
-- JSON_QUOTE(json_val)-- 将json_val⽤"号括起来。SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]"
json值的类型有哪些三、搜索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'); -- 1SELECT 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]'); -- 20SELECT 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 = '["abc", [{"k": "10"}, "def"], {"x":"abc"},
{"y":"bcd"}]';SELECT JSON_SEARCH(@j3, 'one', 'abc'); -- "$[0]"SELECT JSON_SEARCH(@j3, 'all', 'abc'); -- ["$[0]", "$[2].x"]SELECT JSON_SEARCH(@j3, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"SELECT JSON_SEARCH(@j3, 'all', '10'); -- "$[1] [0].k"SELECT JSON_SEARCH(@j3, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"]SELECT JSON_SEARCH(@j3, 'all', '%b%', NULL,
'$[2]'); -- "$[2].x"
四、修改json值函数
1.JSON_ARRAY_APPEND 指定位置追加数组元素
-- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)-- 在指定path的json array尾部追加val。如果指定path是⼀个json object，则将其封装成⼀个json array再追加。如果有参数为NULL，则返回NULL。SET @j4 = '["a", ["b", "c"], "d"]';-- SELECT
JSON_ARRAY_APPEND(@j4, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]SET @j5 = '{"a": 1, "b": [2, 3], "c": 4}';SELECT
JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}SELECT JSON_ARRAY_AP
PEND(@j5, '$', 'z'); -- [{"a": 1, "b": [2, 3], "c": 4}, "z"]
2.JSON_ARRAY_INSERT 指定位置插⼊数组元素
-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)-- 在path指定的json array元素插⼊val，原位置及以右的元素顺次右移。如果path指定的数据⾮json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插⼊尾部。SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]SELECT
JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); --["a", {"b": ["x", 1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]
3.JSON_INSERT 指定位置插⼊
-- JSON_INSERT(json_doc, path, val[, path, val] ...)-- 在指定path下插⼊数据，如果path已存在，则忽略此val(不存在才插⼊)。SET
@j7 = '{ "a": 1, "b": [2, 3]}';SELECT JSON_INSERT(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}
4.JSON_REPLACE 指定位置替换
-- JSON_REPLACE(json_doc, path, val[, path, val] ...)-- 替换指定路径的数据，如果某个路径不存在则略过(存在才替换)。如果有参数为NULL，则返回NULL。SELECT JSON_REPLACE(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}
5.JSON_SET 指定位置设置
-- JSON_SET(json_doc, path, val[, path, val] ...)-- 设置指定路径的数据(不管是否存在)。如果有参数为NULL，则返回NULL。SELECT JSON_SET(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3], "c": "[true, false]"}
6.JSON_MERGE 合并
-- JSON_MERGE(json_doc, json_doc[, json_doc] ...)-- merge多个json⽂档。规则如下：-- 如果都是json array，则结果⾃动merge为⼀个json array；-- 如果都是json object，则结果⾃动merge为⼀个json object；-- 如果有多种类型，则将⾮json array的元素封装成json array再按照规则⼀进⾏mege。SELECT JSON_MERGE('[1, 2]', '[true, false]'); -- [1, 2, true, false]SELECT
JSON_MERGE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}SELECT JSON_MERGE('1', 'true');
-- [1, true]SELECT
JSON_MERGE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]
7.JSON_REMOVE 指定位置移除
-- JSON_REMOVE(json_doc, path[, path] ...)-- 移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。SET @j8 = '["a", ["b", "c"], "d"]';SELECT JSON_REMOVE(@j8, '$[1]'); -- ["a", "d"]
8.JSON_UNQUOTE 去”号
-- JSON_UNQUOTE(val)-- 去掉val的引号。如果val为NULL，则返回NULL。SELECT JSON_UNQUOTE("\"123\""); -- 123
五、返回json值属性的函数
1.JSON_DEPTH 深度
-- JSON_DEPTH(json_doc)-- 获取json⽂档的深度。如果参数为NULL，则返回NULL。-- 空的json arr
ay、json object或标量的深度为1。SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); -- 1 1 1SELECT JSON_DEPTH('[10, 20]'),
JSON_DEPTH('[[], {}]'); -- 2 2SELECT JSON_DEPTH('[10, {"a": 20}]'); -- 3
2.JSON_LENGTH 长度
-- JSON_LENGTH(json_doc[, path])-- 获取指定路径下的长度。如果参数为NULL，则返回NULL。　-- 长度的计算规则：-- 标量的长度为1；-- json array的长度为元素的个数；-- json object的长度为key的个数。SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); -- 3SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); -- 2SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); -- 1
3.JSON_TYPE 类型
-- JSON_TYPE(json_val)-- 获取json⽂档的具体类型。如果参数为NULL，则返回NULL。select JSON_TYPE('[1,2]'); -- ARRAY
4.JSON_VALID 是否有效json格式
-- JSON_VALID(val)-- 判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。SELECT
JSON_VALID('{"a": 1}'); -- 1SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); -- 1
六、函数说明
后⾯会分享更多devops和DBA⽅⾯的内容，感兴趣的朋友可以关注⼀下~

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。