SQL中的JSON数据类型
概述
MySQL支持原生JSON类型，使用JSON数据类型相较于将JSON格式的字符串存储在String型中的优势有：
存储时会自动验证JSON文本；
可以优化存储格式。存储在
JSON型中的JSON文本会被转换成一个支持快速读取的文档元素，这样在使用时不需要再解析文本，并且可以直接通过键和索引访问其中的子对象而无需读取全部文本。
JSON型中可存储的JSON文本的大小不会超过mysql.ini配置文件中设置的max_allowed_packet的值。
JSON值的局部更新
MySQL从8.0开始支持对JSON型中存储的数据进行局部更新，而不需要清除原有数据并写入新值。
局部更新按照下述规则执行：
数据列被声明为
JSON型-
UPDATE语句使用JSON_SET()、JSON_REPLACE()、JSON_REMOVE()三个函数实现JSON的局部更新但对该列直接赋值不属于局部更新，例如：
UPDATE myTable SET json1 = '{"a": 10, "b": 25}'局部更新操作可以实现在单个
UPDATE语句中更新多个JSON列 -
局部更新是针对同一列数据的操作，对不同列的操作不属于局部更新，例如：
UPDATE myTable SET json1 = JSON_SET(json2, '$.a', 100)局部更新中可以使用上述三个函数的嵌套调用形式
局部更新仅将现有JSON对象中的数组或子对象替换成新值，但不能给父对象或数组添加新元素
所替换的新值不能比原值占据的存储空间更大，除非上一次更新留下了足够的存储空间
创建JSON值
-
JSON数组：
["abc", 10, null, true, false]JSON数组中可以存储数字量、字符串、null、布尔量、时间量
-
JSON对象：
{"key1": "value", "key2": 10}JSON对象中的键必须为字符串
JSON数组和JSON对象中可以嵌套子JSON数组和对象。
MySQL中的JSON编码格式为CHARSET=utf8mb4 COLLATE=utf8mb4_bin。
使用字符串字面量创建JSON
在MySQL中JSON值按照字符串的形式写入，在要求为JSON值的上下文中（例如将值插入JSON列，或调用输入参数为JSON的函数）MySQL会解析该字符串，若不符合JSON格式则报错。
例如：
mysql> SELECT JSON_TYPE('["abc", 1]');
+-------------------------+
| JSON_TYPE('["abc", 1]') |
+-------------------------+
| ARRAY |
+-------------------------+
mysql> SELECT JSON_TYPE('{"a": 1, "b": 2}');
+-------------------------------+
| JSON_TYPE('{"a": 1, "b": 2}') |
+-------------------------------+
| OBJECT |
+-------------------------------+
mysql> SELECT JSON_TYPE('"abc"');
+--------------------+
| JSON_TYPE('"abc"') |
+--------------------+
| STRING |
+--------------------+
mysql> SELECT JSON_TYPE('abc');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.
使用函数创建JSON
介绍三个常用的创建JSON的函数：
-
JSON_ARRAY()函数会将传入其中的参数组成JSON数组，例如：mysql> SELECT JSON_ARRAY('a', 1, NOW()); +----------------------------------------+ | JSON_ARRAY('a', 1, NOW()) | +----------------------------------------+ | ["a", 1, "2019-03-22 10:01:53.000000"] | +----------------------------------------+ -
JSON_OBJECT()会将传入的键值对转换为JSON对象，例如：mysql> SELECT JSON_OBJECT('a', 1, 'b', 2); +-----------------------------+ | JSON_OBJECT('a', 1, 'b', 2) | +-----------------------------+ | {"a": 1, "b": 2} | +-----------------------------+ -
JSON_MERGE_PRESERVE()将多个JSON文本组合成一个JSON，例如：mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}'); +-----------------------------------------------------+ | JSON_MERGE_PRESERVE('["a", 1]', '{"a": 1, "b": 2}') | +-----------------------------------------------------+ | ["a", 1, {"a": 1, "b": 2}] | +-----------------------------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}'); +-------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "d": 4}') | +-------------------------------------------------------------+ | {"a": 1, "b": 2, "c": 3, "d": 4} | +-------------------------------------------------------------+
使用JSON
-
将创建出的JSON值赋值给一个自定义的变量时，该JSON会被转换成字符串，因此自定义的变量属于字符串类型、而非JSON类型，例如：
SET @j = JSON_OBJECT('key', 'value');创建的
@j为字符串类型。 -
JSON值在进行比较时会区分大小写，例如：
mysql> SELECT JSON_ARRAY('X') = JSON_ARRAY('x'); +-----------------------------------+ | JSON_ARRAY('X') = JSON_ARRAY('x') | +-----------------------------------+ | 0 | +-----------------------------------+'x'和`'X'不相等，返回false（即0）。 -
JSON中的
null、true、false字面量必须为小写形式，例如：mysql> SELECT JSON_VALID('null'), JSON_VALID('NULL'), JSON_VALID('Null'); +--------------------+--------------------+--------------------+ | JSON_VALID('null') | JSON_VALID('NULL') | JSON_VALID('Null') | +--------------------+--------------------+--------------------+ | 1 | 0 | 0 | +--------------------+--------------------+--------------------+可以看到
Null和NULL的形式是对JSON型来说非法的。但应注意，SQL中的
null、true、false字面量不区分大小写。 -
单引号
''和双引号""的使用：-
使用
JSON_OBJECT()等函数创建JSON时，字符串中出现的引号需要使用转义字符\来和标记字符串开始结束的引号作区分，否则会报错，例如：正确写法： mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\".")); Query OK, 1 row affected (0.22 sec) mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named 'Sakila'.")); Query OK, 1 row affected (0.22 sec) 错误写法： mysql> INSERT INTO facts VALUES (JSON_OBJECT("mascot2", "Our mascot is a dolphin named "Sakila".")); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"."))' at line 1 -
使用JSON对象字面量创建JSON时，需要使用两个转义字符
\\来指明MySQL不需要对引号进行转义，而保留其字面值，例如：两种正确写法： mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}'); Query OK, 1 row affected (0.11 sec) mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \'Sakila\'."}'); Query OK, 1 row affected (0.07 sec) 三种错误写法： mysql> INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}'); ERROR 3140 (22032): 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"."}'); ERROR 3140 (22032): 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'."}'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Sakila'."}')' at line 1
-
-
查找JSON中某个键对应的值：
-
使用
->操作符，返回值会显示为带有包裹引号和转义字符的形式，例如：mysql> SELECT sentence->"$.mascot" FROM facts; +---------------------------------------------+ | sentence->"$.mascot" | +---------------------------------------------+ | "Our mascot is a dolphin named \"Sakila\"." | +---------------------------------------------+ -
使用
->>操作符，返回值会显示为无包裹引号和转义字符的形式，例如：mysql> SELECT sentence->>"$.mascot" FROM facts; +-----------------------------------------+ | sentence->>"$.mascot" | +-----------------------------------------+ | Our mascot is a dolphin named "Sakila". | +-----------------------------------------+
-
JSON值的标准化、合并、和自动包装
JSON值的标准化
当使用JSON_OBJECT()函数创建JSON对象时，传入参数中的重复键会被忽略，即当出现重复键值对时，会对已存在键值对的值进行更新，例如：
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 2, 'key1', 10);
+-----------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 2, 'key1', 10) |
+-----------------------------------------------+
| {"key1": 10, "key2": 2} |
+-----------------------------------------------+
在使用INSERT()函数插入JSON对象时，也会忽略重复键，并更新该键对应的值，例如：
mysql> CREATE TABLE t1 (c1 JSON);
mysql> INSERT INTO t1 VALUES
-> ('{"x": 1, "x": "a"}'),
-> ('{"x": 1, "x": "a", "x": [1, 2, 3]}');
mysql> SELECT c1 FROM t1;
+------------------+
| c1 |
+------------------+
| {"x": "a"} |
| {"x": [1, 2, 3]} |
+------------------+
（注意：在8.0.3之前版本的MySQL中，遇到重复出现的键时，不会用新值更新旧值，而仅忽略该键值对）
JSON值的合并和自动包装
- 使用
JSON_MERGE_PRESERVE()函数合并多个JSON值时会保留重复的键 - 使用
JSON_MERGE_PATCH()函数时仅保留重复键的最后一个，即会发生JSON的标准化过程
合并JSON数组
-
JSON_MERGE_PRESERVE()函数会将多个数组串联mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]'); +------------------------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '["a", "b"]', '["true", "false"]') | +------------------------------------------------------------------+ | [1, 2, "a", "b", "true", "false"] | +------------------------------------------------------------------+ -
JSON_MERGE_PATCH()函数仅保留传入的最后一个数组mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]'); +---------------------------------------------------------------+ | JSON_MERGE_PATCH('[1, 2]', '["a", "b"]', '["true", "false"]') | +---------------------------------------------------------------+ | ["true", "false"] | +---------------------------------------------------------------+
合并JSON对象
-
JSON_MERGE_PRESERVE()函数会将重复键对应的所有值组合成一个数组mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}'); +--------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') | +--------------------------------------------------------------+ | {"a": [1, 10], "b": 2, "c": 3} | +--------------------------------------------------------------+ -
JSON_MERGE_PATCH()函数仅保留最后一个重复键对应的值mysql> SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}'); +-----------------------------------------------------------+ | JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 10, "c": 3}') | +-----------------------------------------------------------+ | {"a": 10, "b": 2, "c": 3} | +-----------------------------------------------------------+
合并非JSON数组或对象的元素
当待合并的元素既非JSON数组也非JSON对象时，会将传入的元素自动包装为长度为1的JSON数组，并按照合并数组的规则合并
mysql> SELECT JSON_MERGE_PRESERVE('1', '2');
+-------------------------------+
| JSON_MERGE_PRESERVE('1', '2') |
+-------------------------------+
| [1, 2] |
+-------------------------------+
mysql> SELECT JSON_MERGE_PATCH('1', '2');
+----------------------------+
| JSON_MERGE_PATCH('1', '2') |
+----------------------------+
| 2 |
+----------------------------+
将JSON数组和JSON对象合并到一起
当待合并的元素既有JSON数组也有JSON对象时，会将JSON对象自动包装成数组，并按照合并数组的规则合并
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}');
+---------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '{"a": 1, "b": 2}') |
+---------------------------------------------------+
| [1, 2, {"a": 1, "b": 2}] |
+---------------------------------------------------+
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"a": 1, "b": 2}') |
+------------------------------------------------+
| {"a": 1, "b": 2} |
+------------------------------------------------+
在JSON中查找和修改元素
在JSON中查找和修改元素的语法为：$，该符号后跟随需要查找的键名或索引。
查找元素
JSON_EXTRACT()函数用于从JSON中提取元素，例如：
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.a');
+-----------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2}', '$.a') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[2]');
+-----------------------------------+
| JSON_EXTRACT('[1, 2, 3]', '$[2]') |
+-----------------------------------+
| 3 |
+-----------------------------------+
修改元素
-
JSON_SET()函数用于修改JSON中对应的元素，例如：mysql> SELECT JSON_SET('{"a": 1, "b": 2}', '$.a', 3); +----------------------------------------+ | JSON_SET('{"a": 1, "b": 2}', '$.a', 3) | +----------------------------------------+ | {"a": 3, "b": 2} | +----------------------------------------+ mysql> SELECT JSON_SET('[1, 2, 3]', '$[2]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[2]', 4) | +----------------------------------+ | [1, 2, 4] | +----------------------------------+当路径对应的元素不存在时，会添加新的元素
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2); +--------------------------------------------+ | JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +--------------------------------------------+ | ["a", {"b": [1, false]}, [10, 20, 2]] | +--------------------------------------------+ -
JSON_INSERT()函数会给JSON添加新的元素，但是不会更改原有元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2); +-----------------------------------------------+ | JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +-----------------------------------------------+ | ["a", {"b": [true, false]}, [10, 20, 2]] | +-----------------------------------------------+ -
JSON_REPLACE()函数会修改现有元素，但不会添加新元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2); +------------------------------------------------+ | JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) | +------------------------------------------------+ | ["a", {"b": [1, false]}, [10, 20]] | +------------------------------------------------+ -
JSON_REMOVE()函数会删除路径对应的元素mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]'; mysql> SELECT JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]'); +-----------------------------------------+ | JSON_REMOVE(@j, '$[1].b[0]', '$[2][2]') | +-----------------------------------------+ | ["a", {"b": [false]}, [10, 20]] | +-----------------------------------------+
路径语法
-
当键名中包含空格时需要用双引号包含键名，即
mysql> SELECT JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"'); +------------------------------------------------------------------------+ | JSON_EXTRACT('{"a fish": "shark", "a bird": "sparrow"}', '$."a fish"') | +------------------------------------------------------------------------+ | "shark" | +------------------------------------------------------------------------+ -
当查找不到路径中的元素时，返回null
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[3]'); +-----------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[3]') | +-----------------------------------+ | NULL | +-----------------------------------+ -
可以使用
'$[M to N]'的形式提取出某一范围内的元素，last关键字指代数组中最后一个元素（但不能用于修改元素）
mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[1 to last]'); +-------------------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[1 to last]') | +-------------------------------------------+ | [2, 3] | +-------------------------------------------+ -
可以使用
*和**通配符来提取元素（但不能用于修改元素）-
.*表示提取JSON对象中所有键的值，并返回一个数组mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.*'); +-----------------------------------------+ | JSON_EXTRACT('{"a": 1, "b": 2}', '$.*') | +-----------------------------------------+ | [1, 2] | +-----------------------------------------+ -
[*]表示提取JSON数组中所有元素mysql> SELECT JSON_EXTRACT('[1, 2, 3]', '$[*]'); +-----------------------------------+ | JSON_EXTRACT('[1, 2, 3]', '$[*]') | +-----------------------------------+ | [1, 2, 3] | +-----------------------------------+ -
prefix**suffix表示提取路径以prefix开始、以suffix结束的元素，其中prefix是可选的，但suffix是必须的mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b'); +-------------------------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$**.b') | +-------------------------------------------------------------------------+ | [1, 3] | +-------------------------------------------------------------------------+ mysql> SELECT JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b'); +---------------------------------------------------------------------------+ | JSON_EXTRACT('{"a": {"b": 1, "d": 2}, "c": {"b": 3, "d": 4}}', '$.a**.b') | +---------------------------------------------------------------------------+ | [1] | +---------------------------------------------------------------------------+
-
JSON值的比较和排序
JSON值的比较
JSON值使用<、>、=、<=、>=、<>、<=>、!=操作符进行比较。比较时会将JSON转换为MySQL的原生数值类型或字符串来比较。
JSON在比较时分为两步：
-
比较JSON类型（即
JSON_TYPE()的返回值），若类型不同则按照类型的优先级顺序得出比较结果，优先级越高则越大，若类型相同则进行第二步；JSON类型的优先级如下：
BLOB<BIT<OPAQUE<DATETIME<TIME<DATE<BOOLEAN<ARRAY<OBJECT<STRING<INTEGER=DOUBLE<NULL -
根据各类型具体的比较规则比较
-
BLOB、BIT、OPAQUE、STRING：先比较两个值长度相同的部分，如果都相同，则长度较短的值排在长度较长的值之前。对STRING的比较是基于utf8mb4编码格式的。"a" < "ab" < "b" < "bc" "A" < "a" DATATIME、TIME、DATE：表示较早时间点的值排在表示较晚时间点的值之前。表示相同时间点的DATATIME值和TIMESTAMP值相等-
ARRAY：两JSON数组长度相同且对应元素相等时两数组相等。长度不同时，对应位置的元素的值较小的数组排在前面；对应元素都相同时，较短的数组排在前面[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"] BOOLEAN：false值排在true值之前-
OBJECT：当两个JSON对象具有相同的键，且各键对应的值也相等时，两个JSON对象相等。不相等的JSON对象的的排序不定{"a": 1, "b": 2} = {"b": 2, "a": 1} INTEGER、DOUBLE：当两个比较对象一个是INT型、一个是DOUBLE型时，INT型会被转换为DOUBLE型；但当两个比较对象无法预先判断是INT型还是DOUBLE型时，会转换为INT型比较JSON值和SQL
NULL比较时，比较结果未知JSON值和非JSON值比较时，非JSON值会被转换为JSON值
-
JSON值的排序
使用ORDER BY和GROUP BY对JSON值排序时遵循以下规则：
- 按照前述比较规则排序
- 升序时，SQL
NULL排在所有JSON值之前（包括JSONnull）；降序时，SQLNULL排在所有JSON值之后
推荐将JSON值转换为MySQL基本类型再进行排序。
