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