mysql json查询 简书_MySQL5.7.8 JSON

Mysql从5.7.8版本开始原生支持JSON(JavaScript Object Notation)类型。确切的是MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。

优点如下：

1、自动验证存储在JSON列中的JSON文档。无效的文档产生错误。

2、优化存储格式。JSON文档存储在JSON列，被转换为内部格式，允许快速读取为文档元素。当以后服务器读取这种二进制格式存储的JSON值时，不需要从文本中分析该值。二进制格式的结构允许服务器直接通过键或数组索引查找子对象或嵌套值，而不必在文档中读取子对象或嵌套值之前或之后的所有值。

JSON函数完整列表#

MySQL官方列出json相关的函数，完整列表如下[doc]：

分类

函数

描述

-

将json转成json字符串类型

-

json_extract的简洁写法，MySQL 5.7.9开始支持

-

json_unquote(column -> path)的简洁写法

-

提取json中的键值为json数组

-

按给定字符串关键字搜索json，返回匹配的路径

废弃，MySQL 5.7.9开始改名为json_array_append

-

末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素

-

插入值(插入新值，但不替换已经存在的旧值)

-

合并json数组或对象

-

替换值(只替换已经存在的旧值)

-

设置值(替换旧值，并插入不存在的新值)

-

去除json字符串的引号，将值转成string类型

-

返回json文档的长度

-

返回json值得类型

-

判断是否为合法json文档

官方文档对全部函数都作了充分解释并提供一定的示例代码。下文挑选了部分函数，演示它们的使用方法。

插入和查询数据#

mysql> CREATE TABLE employees (data JSON);

Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');

Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employees WHERE data->'$.id'= 2; -- json路径表达式

+--------------------------+

| data |

+--------------------------+

| {"id": 2, "name": "Joe"} |

+--------------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM employees WHERE json_extract(data,'$.id') = 2;

+--------------------------+

| data |

+--------------------------+

| {"id": 2, "name": "Joe"} |

+--------------------------+

1 row in set (0.00 sec)

mysql> SET @j = '["a", "b"]';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT json_extract(@j, '$[0]');

+--------------------------+

| json_extract(@j, '$[0]') |

+--------------------------+

| "a" |

+--------------------------+

1 row in set (0.00 sec)

json路径表达式是json_extract的简洁写法，但存在以下限制[ref]

即，1. 数据源必须是表字段，2. 路径表达式必须为字符串，3. SQL语句中最多只支持一个。

json_contains示例：

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT json_contains(@j, '{"a": 1}');

+-------------------------------+

| json_contains(@j, '{"a": 1}') |

+-------------------------------+

| 1 |

+-------------------------------+

1 row in set (0.00 sec)

更新数据

json_array_append和json_array_insert示例：

mysql> SET @j = '["a", "b"]';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT json_array_append(@j, '$', 'c');

+---------------------------------+

| json_array_append(@j, '$', 'c') |

+---------------------------------+

| ["a", "b", "c"] |

+---------------------------------+

1 row in set (0.00 sec)

mysql> SET @scalar = '1';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT json_array_append(@scalar, '$', 'c');

+---------------------------------+

| json_array_append(@scalar, '$', 'c') |

+---------------------------------+

| [1, "c"] |

+---------------------------------+

1 row in set (0.00 sec)

mysql> SELECT json_array_insert(@j, '$[1]', 'c');

+------------------------------------+

| json_array_insert(@j, '$[1]', 'c') |

+------------------------------------+

| ["a", "c", "b"] |

+------------------------------------+

1 row in set (0.00 sec)

json_replace、json_set和json_insert示例

json_replace：只替换已经存在的旧值

json_set：替换旧值，并插入不存在的新值

json_insert：插入新值，但不替换已经存在的旧值

替换值，json_replace示例

mysql> UPDATE employees

-> SET data = json_replace(data, '$.name', 'Cathy')

-> WHERE data->'$.id' = 2;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM employees WHERE data->'$.id'= 2;

+---------------------------+

| data |

+---------------------------+

| {"id": 2, "name": "Cathy"} |

+---------------------------+

1 row in set (0.00 sec)

设置值，json_set示例

mysql> UPDATE employees

-> SET data = json_set(data, '$.name', 'Bill', '$.city', '北京')

-> WHERE data->'$.id'= 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM employees WHERE data->'$.id'= 2;

+---------------------------------------------+

| data |

+---------------------------------------------+

| {"id": 2, "city": "北京", "name": "David"} |

+---------------------------------------------+

1 row in set (0.00 sec)

插入值，json_insert示例

mysql> UPDATE employees

-> SET data = json_insert(data, '$.name', 'Will', '$.address', '通州副中心')

-> WHERE data->'$.id'= 2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM employees WHERE data->'$.id'= 2;

+---------------------------------------------------------------------+

| data |

+---------------------------------------------------------------------+

| {"id": 2, "city": "北京", "name": "David", "address": "通州副中心"} |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)