MYSQL中json数据类型介绍和用法

MySQL支持RFC 7159JSON定义的本机数据类型，可以高效访问JSON文档中的数据。相比于在字符串中存储的json格式字符串，JSON文档数据类型有以下好处：

• 存储在JSON类中的JSON文档能够自动验证，无效的文档会报错
• 存储在JSON列中的JSON文档会转化为内部格式，这样可以对文档进行快速访问。不需要重新从文本中解析json值，能够直接通过键或数组索引查找子对象或者嵌套值。

JSON操作

• 创建JSON：

JSON数组：

['a','sd',20,true,null]

JSON对象

{"key1": "value1","key2": 30}

JSON对象和数组可以包含字符串，数字类型，空，布尔值。JSON对象中的键必须是字符串。
在JSON数组中可以允许有JSON数组或对象的嵌套，例如：

[10,{''id": 1,"name": "Micheal"},[null,false,"type"]]
{"id": 1,"value": [2,24]}

MySQL如何处理作为输入的JSON值：

• 输入的值为有效的JSON值时，值能成功插入，无效则报错：

CREATE TABLE jsonDemo (jdoc JSON);
INSERT INTO jsonDemo VALUES ('{"key1": "Micheal"}');
INSERT INTO jsonDemo VALUES ('{1: "Micheal"}');

上面例子中前两条成功执行，最后一条结果：

INSERT INTO jsonDemo VALUES ('{1: "Micheal"}')  Error Code: 3140. Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'jsonDemo.jdoc'.

• JSON_TYPE()函数：将一个JSON参数解析为JSON值，如果值有效，返回JSON类型，否则产生错误

SELECT JSON_TYPE('["a",1,true]');
SELECT JSON_TYPE('"HELLO"');
SELECT JSON_TYPE('hello');

第一条语句返回ARRAY
第二条语句返回STRING
第三条语句报错：Error Code: 3141. Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

MySQL还提供了一系列创建JSON值的替代方法比如：
JSON_ARRAY()
JSON_OBJECT()
JSON_MERGE_PRESERVE()
关于这三个方法的用法可以自行搜索获得。

搜索和修改JSON值

• 通过JSON路径表达式选择JSON文档中的值
  例如：从下面json文档中查询键为name的值：

SELECT JSON_EXTRACT('{"id": 1,"name": "Micheal"}','$.name');

查询结果：
"Micheal"
JSON路径表达式语法：
使用$ 字符表示当前JSON文档，$后可跟选择器
选择器：

• .加key的名称，比如'$.name'
• [N] N: 数组的下标
• [M to N] 数组下标范围
  路径还可包含通配符 或*：
• .[*]查询json对象中所有成员的值
• [*] 查询json数组中所有成员的值
• prefix*suffix 查询所有以命名前缀开头并以命名后缀结尾的路径

其中文档中不存在的路径的结果为NULL。
举例：

SELECT JSON_EXTRACT('{"id": 1,"name": "Micheal"}','$.id');
SELECT JSON_EXTRACT('[1,22,4,12]','$[2]');
SELECT JSON_EXTRACT('[1,22,4,12]','$[0 to 2]');
SELECT JSON_EXTRACT('[1,22,4,12]','$[*]');
SELECT JSON_EXTRACT('{"id": 1,"name": "Micheal"}','$.*');

结果分别为：
1，4，[1, 22, 4],[1, 22, 4, 12],[1, "Micheal"]