mysqljsoninvalidjsontext_MySQL中JSON类型应⽤
MySQL 从 5.7.8 开始，⽀持原⽣的 JSON 数据类型。可以⾼效的访问 JSON ⽂档中的数据。与在字符串列中存储 JSON 格式字符串相⽐，JSON 数据类型有以下优势：
可⾃动验证存储的 JSON 数据格式是否正确。
优化的存储格式。存储在 JSON 列中的 JSON ⽂档将转化为内部格式，以允许对⽂档元素进⾏快速访问。当服务器读取以⼆进制存储的JSON 值时，不需要再对⽂本解析后取值。⼆进制格式的结构使服务器能够直接通过键或数组索引查⼦对象或嵌套值，⽽⽆需在⽂档之前或之后读取所有值。
JSON 列占⽤的空间与 LONGBLOB 或 LONGTEXT 基本相同。
在 MySQL 8.0.13 之前，JSON 列不能定义⾮ null 的默认值。
以下测试 MySQL 版本：8.0.16, 个别测试会与较⽼的版本结果不⼀致。
JSON 数组包含由逗号分隔并由中括号包裹的值列表
["abc", 10, null, true, false]
JSON 对象包含⼀组由逗号分隔并且由⼤括号包裹的键值对
{"k1": "value", "k2": 10}
JSON 对象中的键必须是字符串。JSON 数组中的元素和 JSON 对象键值允许嵌套。
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
JSON 值在插⼊前会校验正确性。
create table t1 (jdoc JSON);
insert into t1 values
('{"key1":"value1","key2":"value2"}');
insert into t1 values
('"stringvalue"');
insert into t1 values
('[1, 2,');
/**插⼊的value值中下标6的位置有错> 3140 - Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.*/
JSON_TYPE()函数需要 JSON 参数，并尝试将其解析为 JSON 值。如果值有效，则返回值的 JSON 类型，否则报错。
mysql> SELECT JSON_TYPE('["a", "b", 1]');
> ARRAY
mysql> SELECT JSON_TYPE('"hello"');
> STRING
mysql> select json_type('{"key":"value"}') type;
> OBJECT
mysql> SELECT JSON_TYPE('hello');
> ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.
JSON_ARRAY()函数可以将传⼊的参数(允许 null)转化为⼀个 JSON 数组
mysql> SELECT JSON_ARRAY('a', 1, NOW());
> ["a", 1, "2019-07-13 15:56:47.000000"]
JSON_OBJECT()函数可将传⼊参数(允许 null)转化为⼀个 JSON 对象
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
> {"key1": 1, "key2": "abc"}
/*** JSON对象中key值不能为null，value可以为null*/
mysql> SELECT JSON_OBJECT('A','a',null,null,'C','c');
> JSON documents may not contain NULL member names.
JSON_MERGE_PRESERVE()接受两个或多个 JSON ⽂档并返回组合结果(关于 JSON 值的合并，下⾯会有更加详细的测试)
mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
>["a", 1, {"key": "value"}]
JSON 值可以赋给⽤户⾃定义变量
mysql> set @j = JSON_OBJECT('key','value');
mysql> select @j;
> {"key": "value"}
然⽽，⽤户定义的变量不能是 JSON 数据类型，因此尽管前⾯⽰例中的@j 看起来像 JSON 值并且具有与 JSON 值相同的字符集和排序规则，但它没有 JSON 数据类型。相反，JSON_OBJECT() 的结果在分配给变量时会转换成字符串。
ps：官⽅⽂档上的这段话可能只是提⽰⼀下⽤户变量的数据类型不能是 JSON，但是使⽤起来好像也没什么问题。
下⾯的例⼦可以看到，变量是正确的 JSON 格式数据时，使⽤ JSON 函数对其操作都没问题。
mysql> SET @x = '{ "a": 1, "b": 2 }',
> @y = '{ "a": 3, "c": 4 }',
> @z = '{ "a": 5, "d": 6 }';
mysql> SELECT JSON_MERGE_PATCH(@x, @y, @z) AS Patch,
-> JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
> Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
>Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
> ["a", "d"]
mysql> set @a = Json_object('key','value'), @b = json_object('key','value2');
mysql> select json_merge_preserve(@a,@b);
> {"key": ["value", "value2"]}
通过转化 JSON 值⽣成的字符串，字符集是 utf8mb4，排序规则是 utf8mb4_bin
mysql> SELECT CHARSET(@j), COLLATION(@j);
> utf8mb4 | utf8mb4_bin
由于 utf8mb4_bin 是⼆进制排序规则，因此 JSON 值的⽐较区分⼤⼩写
mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('x'),JSON_ARRAY('x') = JSON_ARRAY('X');
> 1 | 0
区分⼤⼩写也适⽤于 JSON null，true 和 false ⽂字，他们必须始终以⼩写形式写⼊。只有⼩写字母时 JSON 才是 JSON 有效值。
mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
> 1|0|0
mysql> SELECT CAST('null' AS JSON);
> null
mysql> SELECT CAST('Null' AS JSON);
> Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.
JSON 是区分⼤⼩写的，⽽ SQL 是不区分的。下列值都可以成功被识别为 null。
mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
> 1|1|1
当你想要讲引号字符( " 或 " )插⼊到 JSON ⽂档中，你需要使⽤ \ 转义字符。
mysql> CREATE TABLE facts (sentence JSON);
mysql> INSERT INTO facts VALUES
> (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
如果将值作为 JSON 对象⽂字传⼊，则此⽅法不起作⽤。如下⽰例，第⼀个 \" 的位置会被当做 这个属性的结束位置，所以报了缺少 逗号 或 ⼤括号 的错误。
mysql> INSERT INTO facts VALUES
> ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
>> 3140 - Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column
'facts.sentence'.
这⾥需要使⽤双反斜杠来使之⽣效
mysql> INSERT INTO facts VALUES
> ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
mysql操作官方文档查询查看效果，可以看到两种⽅式的结果是⼀样的。
mysql>SELECT * FROM facts;
>{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
要使⽤键查特定的值，可以使⽤ column-path 操作符 ->，可以看到查询结果是完整的 value 值，包括引号和转义符
mysql> select sentence->"$.mascot" from facts;
> "Our mascot is a dolphin named \"Sakila\"."
有时候我们只需要⾥⾯的字符串，可以使⽤ ->> 运算符来查询；这样查询到的结果就只是需要显⽰的值了。
mysql> select sentence->>"$.mascot" from facts;
>Our mascot is a dolphin named "Sakila".
如果启动了 NO_BACKSLASH_ESCAPES 服务器 SQL 模式，则前⾯的插⼊对象的实例将⽆法正常⼯作。如果设置了此模式，则可以使⽤单个反斜杠⽽不是双反斜杠来插⼊ JSON 对象⽂字，并保留反斜杠。如果在执⾏插⼊时使⽤ JSON_OBJECT() 函数并设置了此模式，则必须替换单引号和双引号
mysql> INSERT INTO facts VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
mysql>SELECT * FROM facts;
>{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
解析字符串并发现它是有效的 JSON ⽂档时，它也会进⾏规范化。⽐如 JSON 对象中不能有重复的 key，那么有重复 key 时，后⾯的value 会覆盖前⾯的
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
>{"key1": "def", "key2": "abc"}
将值插⼊ JSON 列时也会执⾏规范化
mysql>CREATE TABLE t1 (c1 JSON);
mysql>INSERT INTO t1 VALUES
('{"x": 17, "x": "red"}'),
('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql>SELECT c1 FROM t1;
>{"x": "red"}
{"x": [3, 5, 7]}
这种 “last duplicate key wins(最后重复秘钥获胜)”的规则由 RFC 7159 建议，并且⼤多数 JavaScript 解析器都⽀持这个规则。(Bug #86866,Bug #26369555)
在 8.0.3 之前的 MySQL 版本中，重复的 key value 会被丢弃。如在 5.7.26 版本的 MySQL 做上⾯同样的测试，结果就不⼀样
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
>{"key1": 1, "key2": "abc"}
mysql>CREATE TABLE t1 (c1 JSON);
mysql>INSERT INTO t1 VALUES
('{"x": 17, "x": "red"}'),
('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql>SELECT c1 FROM t1;
>{"x": 17}
{"x": 17}
MySQL 还会丢弃原始 JSON ⽂档中键、值或元素之间的额外空格。为了使查更有效，它还对 JSON 对象的键进⾏排序。⽬前在这两个版本中做的简单测试，排序结果都是⼀样的。当然官⽅⽂档上也有提⽰：此排序结果可能会发⽣变化，并且不保证在各个版本中保持⼀致。
mysql>select JSON_OBJECT('key1','value1','key3','value3','key2','value2');
>{"key1": "value1", "key2": "value2", "key3": "value3"}
合并 JSON 值
MySQL 中有三个合并⽅法：
JSON_MERGE: 在 MySQL 8.0.3 中已弃⽤，并且在将来的版本中将被删除。
JSON_MERGE_PRESERVE：保留重复键的值。就是重命名后的 JSON_MERGE()
JSON_MERGE_PATCH：丢弃除最后⼀个值之外的所有键
**合并数组：**将多个数组合并成单个数组。
JSON_MERGE_PRESERVE() 通过将后⼀数组追加到前⼀数组末尾实现。
JSON_MERGE_PATCH() 将每个参数视为由单个元素组成的数组(因此它们的下标都是 0)，然后应⽤ “last duplicate key wins” 规则取最后⼀个参数。
mysql> SELECT
-> JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
-> JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
Patch: [true, false]
注意：⽅法中都允许有 null ，但是结果需要注意。JSON_MERGE_PRESERVE() ⽅法中，若作为参数的数组中有个 null 值，那最终结果中也会有个 null，⽅法不会去除 null 值和重复值。
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[true, false]','["a", null, "c"]') AS Preserve;
>[1, 2, "a", "b", "c", true, false, "a", null, "c"]
但如果作为参数的数组本⾝就是 null，那合并结果也是 null
mysql>SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]',null,'[true, false]') AS Preserve;
>Null
JSON_MERGE_PATCH() ⽅法由于只保留最后⼀个数组，所以 null 对它并没有什么影响，只有当最
后⼀个参数为 null 时结果才为 null
mysql>SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]',null,'["a", "b", "c"]',null) AS Patch;
>Null
**合并对象：**将多个对象合并成单个对象
JSON_MERGE_PRESERVE()：将多个对象的键值对组合成⼀个新对象，会将重复键的值组成⼀个数组(不会去除重复值和 null)赋给这个键。
JSON_MERGE_PATCH()：将多个对象的键值对组合成⼀个新对象，重复键的值会⽤ last duplicate key wins(最后重复秘钥获胜)规则取最后⼀个。
mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 1}', '{"c": 5, "d": 3}','{"d":null}') AS Preserve,
JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论