Mysql JSON 支持

1. 创建JSON字段
2. 搜索
3. 插入
4. 修改
5. 删除
6. 归一化处理
7. 合并
8. 其他
   1. JSON_TYPE()
   2. 创建JSON数组
   3. 创建JSON对象
   4. 变量赋值
   5. 字面量
   6. 转换
9. 比较和排序

[官方文档](http://dev.mysql.com/doc/refman/5.7/en/json.html)学习

在MySQL5.7版本中增加了对JSON的支持. 在SQL中进行JSON字段操作时都是通过使用函数完成的

创建JSON字段

在Mysql中, JSON是通过字符串进行存储的.

下面的例子演示了创建JSON类型字段的表, 以及插入一个JSON串和插入一个非法的JSON串

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,'.

at position N是从0 开始计算的

搜索

我们可以在JSON文档中通过JSON_EXTRACT()函数指定path来搜索出一个值.

在相关方法中使用表达式可以提取数据,或者修改JSON文档 以及进行其他的操作. 例如下面的操作就是从JSON文档中提取key为name的值.

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

在Mysql中, 可以使用$加后缀的方式表示一个JSON文档. $后可以跟一个选择符来索引到JSON文档中任意的位置元素:

• $"key" 表示在JSON文档中, key所对应的值. 注意key必须使用""括起来.
• [N] 表示JSON数组文档中第N个位置的值(从0开始).
• Paths 可以包含 *或者** 通配符.
• .[*] 找到JSON对象中所有的成员值
• [*] 找到JSON数组中所有的成员值
• prefix**suffix 匹配所有的以prefix开头, 以suffix结尾的path.

下面我们创建出三个元素的数组, 然后假设 $ 指向这个数组:

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

那么:

• $[0] 求值为 3.
• $[1] 求值为 {“a”: [5, 6], “b”: 10}.
• $[2] 求值为[99, 100].
• $[3] 求值为 NULL (指向一个不存在的元素).

因为 $[1] 和 $[2] 是非标量的值, 因此我们可以进一步的使用path表达式求出它内嵌的值. 例如:

• $[1].a 求值为 [5, 6].
• $[1].a[1] 求值为 6.
• $[1].b 求值为 10.
• $[2][0] 求值为 99.

刚才我们也提到了, path表达式的key必须被""包含起来, 未被""包含起来的key会被视为非法的.

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

这俩个key都包含了一个空格, 因此在path表达式中, 必须使用""将key包含:

• $."a fish" 求值为 shark.
• $."a bird" 求值为 to sparrow.

如果在对数组求值时, path中的通配符会求值出多个结果.

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

在下面的例子中, $**.b会在多个path($.a.b 和 $.c.b)中进行求值, 然后将求值结果放到一个数组中:

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).

更多参考See Section 13.16.3, “Functions That Search JSON Values” 以及 Section 14.1.18.6, “Secondary Indexes and Generated Virtual Columns”.

在一些方法中, 会接受一个JSON文档, 然后对该JSON文档进行一些处理. 例如

• JSON_SET()
• JSON_INSERT()
• JSON_REPLACE()

插入

我们生成一个JSON文档, 然后在下面的操作中使用这个文档:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET()会对已经存在的path替换, 不存在的进行添加:

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

在上例中$[1].b[0]选择了一个已经存在的value，然后它被替换成了1. 但是$[2][2] 并不存在, 所以就在$[2][2]插入了值2.

JSON_INSERT()向JSON文档中插入新的值, 但是如果path已经存在, 则会归一化处理, 不会覆盖原有的值:

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()执行替换操作, 但是如果path不存在的话, 不会进行插入操作:

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_SET()也会完成修改值的功能

删除

JSON_REMOVE() 接受一个 JSON 文档以及一个或者多个要删除的path. The return value is the original document minus the values selected by paths that exist within the document:

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

这三个path产生了如下的效果

• $[2]找到匹配[10, 20]值, 然后将其删除掉.
• 第一个$[1].b[1]匹配到了false值, 然后将其删除掉.
• 第二个$[1].b[1]没有匹配到任何值, 因此该操作不会有任何结果.

归一化处理

当一个字符串可以解析成一个有效的JSON文档, 它同时也会进行归一化处理. 当JSON中出现重复的Key时, 只会保留最开始的那个Key/Value, 接下来重复出现的都会抛弃掉.

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

Mysql的归一化处理还会对JSON对象的key进行排序处理(以便查找时提供更好的性能). The result of this ordering is subject to change and not guaranteed to be consistent across releases. 另外, key或者value之间的空格会自动的被忽略掉.

同样的, Mysql中创建JSON的方法同样也都做了归一化处理.

当多个数组合并成一个数组时, 数组元素会依次存储进新的数组中, 如下面的JSON_MERGE():

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

合并

多个对象合并到一个对象中的时候, 如果多个对象中都出现了相同的key, 那么相同的key对应的value值会被放到该key对应的数组中.

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

当非数组类型的数据出现在要求数组为参数的上下文中时, 非数组类型的数据会自动被包装成数组类型(会自动在数据俩侧添加[]将其括起来). 在下面的例子中, 每一个参数都会被自动包装成([1], [2]), 然后产生一个新的数组.

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

其他

在下面我们看一下除了增删改查之外的其他常用函数

JSON_TYPE()

JSON_TYPE()方法接受一个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 使用utf8mb4编码和utf8mb4_bin集合处理JSON 字符串内容. 其他的编码会被转换成utf8mb4编码. (ascii 和 utf8 编码并不会进行转换, 因为这俩个字符集是utf8mb4的子集.)

创建JSON数组

除了使用字面量JSON串之外, Mysql还提供了很多创建JSON串的方法. 例如JSON_ARRAY()`函数接受一个参数列表(个数大于等于0), 然后返回一个JSON字符串数组.

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

创建JSON对象

JSON_OBJECT()接受一个key/value形式的参数列表, 返回一个包含那些元素的JSON对象:

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

变量赋值

也可以将JSON赋给一个用户自定义的变量

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

在上例中, 尽管JSON_OBJECT()方法会返回一个JSON类型对象, 但是当将其赋给一个变量(@j)时, 它就被自动转换成了一个字符串类型.

JSON转换成的字符串, 它的编码是utf8mb4, 字符序为utf8mb4_bin:

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

因为utf8mb4_bin是一种二进制的字符序, 因此在对比俩个JSON值是区分大小写的.

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 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 |
+--------------------+--------------------+--------------------+

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'.

JSON字面量区分大小写与SQL中的不同. 在SQL中NULL, TRUE, FALSE等字面量可以写成由任意大小写组成:

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

比较和排序

JSON文档里面的value可以通过如下操作符进行比较操作

• =
• <
• <=

• =

• <>
• !=
• <=>

下面的比较操作符和方法并不支持JSON值

• BETWEEN
• IN()
• GREATEST()
• LEAST()

刚才列出的比较操作符和方法会将JSON值转换成MySQL原生的numeric或者string类型, 因此他们有一个consistent non-JSON扩展类型.

JSON值进行比较时会先根据JSON类型进行比较, 如果类型不同的话, 比较结果就决定于更高优先级的类型. 如果类型一样的话, 则会根据指定类型原则进行比较.

下面列出了JSON类型的优先级, 从高到低进行排序. 我们可以通过JSON_TYPE()来获取某个值得类型.

• BLOB
• BIT
• OPAQUE
• DATETIME
• TIME
• DATE
• BOOLEAN
• ARRAY
• OBJECT
• STRING
• INTEGER, DOUBLE
• NULL
  如果JSON值拥有相同的优先级的话, 那么不同的类型或根据下面介绍的规则进行比较: