Mysql支持JSON类型 - 纯净天空

Mysql从5.7.8版本之后，就开始原生支持JSON(JavaScript Object Notation)类型了。这跟以往直接把JSON字符串存到Mysql的文本类型不一样，因为使用MySQL原生的JSON类型，数据校验之后是以二进制形式存储，避免了每次读数据时都要做字符串解析的额外负担，大大提升了读取性能。这里介绍MySQL中JSON类型的常用用法。

1. 创建带JSON类型的表格

下面的语句创建一个拥有主键和json类型字段的mysql表格：

CREATE TABLE table_name (
    id INT NOT NULL AUTO_INCREMENT, 
    json_col JSON,
    PRIMARY KEY(id)
);

2. 插入JSON格式的数据到表格中

简单的KEY/VALUE词典形式的JSON数据掺入到M有SQL

INSERT INTO
    table_name (json_col) 
VALUES
    ('{"City": "Galle", "Description": "Best damn city in the world"}');

注意：考虑到JSON字符串中使用双引号来标记字符串，所以整个JSON串使用单引号标示。存入的数据在校验通过之后，将以二进制(binary format）的形式存储。

3. 插入嵌套类型的JSON数据到MySQL表格

更复杂的JSON数据，其中一个Value是数组

INSERT INTO myjson(dict) 
VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

4. 查询JSON类型内部的值

通常可以使用”->”来取JSON内容

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+
To look up this particular sentence employing mascot as the key, you can use the column-path operator ->, as shown here:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

也可以使用JSON_EXTRACT函数提取JSON类型内部的值, MySQL 5.7.9版本之后支持了JSON_EXTRACT(table_column, path)

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

如果有下面这个JSON结构

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

5. 更新JSON类型的字段

前面介绍的是如何插入JSON数据到MySQL表格，这里介绍如何更新JSON类型的数据。下面的代码是新增数据到上面的实例数据中。

UPDATE 
    myjson 
SET 
    dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen') 
WHERE 
    id = 2;

注意: 1) 美元符号”$”表示当前的JSON数据，$.variations取该JSON数据中的对应字段的值(一个数组)
2)更新数据之后，使用select * from myjson查询出的结果如下：

+----+-----------------------------------------------------------------------------------------+
| id | dict                                                                                    |
+---+-----------------------------------------------------------------------------------------+
| 2  | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |
+----+-----------------------------------------------------------------------------------------+

6. 将数据CAST为MySQL JSON类型

SELECT CAST('[1,2,3]' as JSON) ;
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

7. 创建JSON对象和JSON数组

JSON_OBJECT可以创建JSON对象:

SELECT JSON_OBJECT('key1',col1 , 'key2',col2 , 'key3','col3') as myobj;

JSON_ARRAY创建JSON数组:

SELECT JSON_ARRAY(col1,col2,'col3') as myarray;

注意: myobj.key3 和 myarray[2] 是 “col3” 这个固定字符串。

更复杂的JSON数据:

SELECT JSON_OBJECT("opening","Sicilian", "variations",JSON_ARRAY("pelikan","dragon","najdorf") ) as mymixed ;

json

参考

MYSQL JSON官方文档：https://dev.mysql.com/doc/refman/5.7/en/json.html