MySQL 5.7 JSON 数据类型使用总结

从MySQL5.7.8开始，MySQL支持原生的JSON数据类型。MySQL 支持RFC 7159定义的全部json 数据类型，具体的包含四种基本类型（ strings , numbers , booleans , null ）和两种结构化类型（ objects and arrays ）。

将 JSON 格式的字符串存储在字符串列中相比，该数据类型具有以下优势：

JSON类型的存储结构

MySQL为了提供对 json 对象的支持，提供了一套将 json 字符串转为结构化二进制对象的存储方式。json会被转为二进制的doc对象存储于磁盘中（在处理JSON时MySQL使用的 utf8mb4 字符集， utf8mb4 是 utf8 和 ascii 的超集）。

doc对象包含两个部分，type和value部分。其中type占1字节，可以表示16种类型：大的和小的json object类型、大的和小的 json array类型、literal类型（true、false、null三个值）、number类型（int6、uint16、int32、uint32、int64、uint64、double类型、utf8mb4 string类型和custom data（mysql自定义类型）

JSON数据类型意义

其实，没有JSON数据类型的支持，我们一样可以通过 varchar 类型或者 text 等类型来保存这一格式的数据，但是，为什么还要专门增加这一数据格式的支持呢？其中肯定有较varchar或者text来存储此类型更优越的地方。

JSON 数据类型

JSON 对象

JSON 数组

创建 JSON

类似varchar，设置主要将字段的type是json，不能设置长度，可以是NULL但不能有默认值。

CREATETABLE`tinywan_json` (
idINT ( 11 ) NOTNULL auto_increment,
 tag jsonNOTNULLCOMMENT'标签列表',
 catagory jsonNOTNULLCOMMENT'分类列表',
 create_time INT ( 11 ) DEFAULTNULLCOMMENT'创建时间',
PRIMARY KEY ( id ) 
) ENGINE = INNODBDEFAULTcharset = utf8mb4;


使用 describe tinywan_json 查看创建的表结构：

mysql> describe tinywan_json;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tag | json | NO | | NULL | |
| catagory | json | NO | | NULL | |
| create_time | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.05 sec)


写入JSON数据

INSERTINTO tinywan_json ( catagory, tag )
VALUES
 ( '{"0":"厨卫","1":"童装","2":"休闲"}', '["Good","Fine","Bad"]' );


MYSQL也有专门的函数 JSON_OBJECT , JSON_ARRAY 函数生成json格式的数据。

INSERTINTO tinywan_json ( catagory, tag )
VALUES
 ( JSON_OBJECT ( "name", "John", "age", 23 ), JSON_ARRAY ( 'Low', 'Middle', 'High' ) )


查看插入的两条数据

mysql> select * from tinywan_json;
+----+---------------------------+-----------------------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------------------+-------------+
| 1 | ["Good", "Fine", "Bad"] | {"0": "厨卫", "1": "童装", "2": "休闲"} | NULL |
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------------------+-------------+
2 rows inset (0.10 sec)


查询 JSON

查询json中的数据使用 column->path 的形式，其中对象类型 path 这样表示 $.path ，而数组类型则是 $[index] 。

查看每一行数据的JSON类型，筛选如下：

mysql> select tag,json_type(tag),catagory,json_type(catagory) from tinywan_json;
+---------------------------+----------------+-----------------------------------------+---------------------+
| tag | json_type(tag) | catagory | json_type(catagory) |
+---------------------------+----------------+-----------------------------------------+---------------------+
| ["Good", "Fine", "Bad"] | ARRAY | {"0": "厨卫", "1": "童装", "2": "休闲"} | OBJECT |
| ["Low", "Middle", "High"] | ARRAY | {"age": 23, "name": "John"} | OBJECT |
+---------------------------+----------------+-----------------------------------------+---------------------+
2 rows inset (0.07 sec)


筛选出 catagory 中 name = John 的那条记录的所有 tag:

mysql> select tag from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+
| tag |
+---------------------------+
| ["Low", "Middle", "High"] |
+---------------------------+
1 row inset (0.06 sec)


通过 json_type 确定了 tag 的数据类型是 ARRAY ，那么就可以使用数组索引的方式查询：

mysql> select tag->'$[0]',tag->'$[2024]',tag->'$.notexist'from tinywan_json where catagory->'$.name' = 'John';
+-------------+----------------+-------------------+
| tag->'$[0]' | tag->'$[2024]' | tag->'$.notexist' |
+-------------+----------------+-------------------+
| "Low" | NULL | NULL |
+-------------+----------------+-------------------+
1 row inset (0.05 sec)


上面的例子中，可以看到，使用 tag->'$[0]' 的方式如期获取了 tag 数组内的第一个数据。另外两个，一个 tag->'$[2024]' 是不存在的数组元素， 一个tag->'$.notexist' 是不存在的对象键，都返回了 null ，这与预期的一致。

还要一个小小的问题，返回的 tag->'$[0]' = "Low" ，并且左右有一对双引号，可否去掉呢？使用 JSON_UNQUOTE 函数

mysql> select JSON_UNQUOTE(tag->'$[0]'),tag->'$[2024]',tag->'$.notexist' from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+----------------+-------------------+
| JSON_UNQUOTE(tag->'$[0]') | tag->'$[2024]' | tag->'$.notexist' |
+---------------------------+----------------+-------------------+
| Low | NULL | NULL |
+---------------------------+----------------+-------------------+
1 row inset (0.06 sec)


JSON 条件查询

因为JSON不同于字符串，所以如果用字符串和JSON字段进行比较，是不会相等的。这时可以使用 CAST 函数，将字符串转成 JSON 的形式。

mysql> select * from tinywan_json where catagory = '{"age": 23, "name": "John"}';
Empty set
mysql> select * from tinywan_json where catagory = CAST('{"age": 23, "name": "John"}'asJSON);
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.07 sec)

对应地， select 字段和 where 筛选条件中，均可以使用 column->path 的方式操作。

mysql> select tag,catagory->'$.age'from tinywan_json where tag->'$[1]' = 'Middle';
+---------------------------+-------------------+
| tag | catagory->'$.age' |
+---------------------------+-------------------+
| ["Low", "Middle", "High"] | 23 |
+---------------------------+-------------------+
1 row inset (0.07 sec)


特别注意的是 。JSON中的元素是严格区分变量类型的，比如说整型和字符串是严格区分的。

mysql> select * from tinywan_json where catagory->'$.age' = 23;
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.06 sec)
mysql> select * from tinywan_json where catagory->'$.age' = '23';
Empty set

除了用 column->path 的形式搜索，还可以用 JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是， JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现错误 nvalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

mysql> select * from tinywan_json where JSON_CONTAINS(catagory,'23','$.age');
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.08 sec)
mysql> select * from tinywan_json where JSON_CONTAINS(catagory,23,'$.age');
Invalid data typeforJSON data in argument 2 to functionjson_contains; a JSONstring or JSONtype is required.

上面打印的第一行，才是正确的写法。整数应该写成 'int' ，字符串则要看值内的情况，很多情况下需要带上双引号， '"string"' ，这样写。如下：

mysql> select * from tinywan_json where JSON_CONTAINS(tag,'"Fine"');
+----+-------------------------+-----------------------------------------+-------------+
| id | tag | catagory | create_time |
+----+-------------------------+-----------------------------------------+-------------+
| 1 | ["Good", "Fine", "Bad"] | {"0": "厨卫", "1": "童装", "2": "休闲"} | NULL |
+----+-------------------------+-----------------------------------------+-------------+
1 row inset (0.07 sec)


小结