MySQL 5.7 Use The JSON Data Type（MySQL 使用 JSON 数据类型）

文章摘译自 MySQL 5.7 Reference Manual / Data Types / The JSON Data Type

自 MySQL 5.7.8 起，MySQL 支持 JSON 数据类型，优势如下
* 自动验证数据合法
* 优化存储格式

存储在 JSON columns 中的 JSON documents 大小受限于 max_allowed_packet
JSON columns 不允许有默认值

Creating JSON Values

JSON array 包含一列值，逗号分割，[ and ] 包裹
["abc", 10, null, true, false]

JSON object 包含 key/value 对，逗号分割，{ and } 包裹
{"k1": "value", "k2": 10}

JSON arrays and objects 可以包含 strings, numbers, JSON null literal, JSON boolean true or false literals, date, time, or datetime
JSON objects 里面 Keys 必须是 strings
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

支持嵌套

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

JSON column 尝试插入一个值，成功当它合法，失败当它非法

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text: "Invalid value." at position 6 in value (or column) '[1, 2,'.

JSON_TYPE() 需要一个 JSON 参数，尝试解析成 JSON 值，返回该值的 JSON 类型，失败返回错误

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING |
+----------------------+

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.

MySQL 在 JSON context 中处理字符串使用 utf8mb4 character set and utf8mb4_bin collation

JSON_ARRAY() 需要一列值（可能空），返回 JSON array 包含这些值

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() 需要一列 key/value 对（可能空），返回 JSON object 包含这些对

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+

JSON_MERGE() 需要两个或更多 JSON documents，返回联合结果

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

JSON values 可以给用户自定义变量赋值

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

然而 自定义变量 不是 JSON 数据类型，JSON_OBJECT() 当给自定义变量赋值的时候转成 string

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4 | utf8mb4_bin |
+-------------+---------------+

因为 utf8mb4_bin 是 binary collation，JSON values 之间的比较是区分大小写的

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
| 0 |
+-----------------------------------+

JSON null, true, and false literals，必须写成小写

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
| 1 | 0 | 0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

与 SQL 区别的是：NULL, TRUE, and FALSE literals 可以写成任何大小写

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
| 1 | 1 | 1 |
+--------------+--------------+--------------+

Normalization, Merging, and Autowrapping of JSON Values

相同的 Key 会被丢弃，即便值不同

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"} |
+------------------------------------------------------+

JSON_MERGE() 合并 JSON array

mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false]                       |
+-----------------------------------------------------+

多个合并的时候，含有相同值，合并成 JSON array

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

非数组值合并的时候会先自动转成数组，然后再合并

mysql> SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2]               |
+----------------------+

mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}]                 |
+------------------------------------------------+

Searching and Modifying JSON Values

通过 JSON path expression 取 JSON document 中值
Path expressions 非常有用通过函数提取和修改 JSON document 中值

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+

Path syntax 通过开头一个 $ 来代表 JSON document，可选的跟着选择符来标识后继 JSON document 部分
每个 key name 后面跟一个点号，当 key name 在某种情况（含有空格）下非法时可以用双引号括起来
[N] 追加到 Path 来选择数组中的一个位置为 N 的元素，数组从 0 起始
可以包含通配符
.[*] JSON object 所有成员
[*] JSON array 所有元素
prefix**suffix 匹配所有 prefix 开始，suffix 结束
Path 在文档中不存在取值为 NULL

[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[0] evaluates to 3.
$[1] evaluates to {"a": [5, 6], "b": 10}.
$[2] evaluates to [99, 100].
$[3] evaluates to NULL (it refers to the fourth array element, which does not exist).

$[1].a evaluates to [5, 6].
$[1].a[1] evaluates to 6.
$[1].b evaluates to 10.
$[2][0] evaluates to 99.

{"a fish": "shark", "a bird": "sparrow"}

$."a fish" evaluates to shark.
$."a bird" evaluates to sparrow.

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

MySQL 5.7.9 以后，你可以通过 column->path 代替 JSON_EXTRACT(column, path)

JSON_SET() 不存在则增加，若存在则替换
JSON_INSERT() 不存在则增加，若存在不替换
JSON_REPLACE() 不存在则忽略，若存在则替换
JSON_REMOVE() 移除一个或多个，返回剩余内容

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]]      |
+--------------------------------------------+

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]]      |
+-----------------------------------------------+

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]]             |
+------------------------------------------------+

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

Comparison and Ordering of JSON Values

略

Aggregation of JSON Values

略

====本文内容已完，宣传我的项目====
欢迎关注《休闲益智游戏》微信服务号
谢谢