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

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

自动验证存储在 JSON列中的 JSON 文档。无效的文档会产生错误。
优化的存储格式。存储在列中的 JSON 文档被转换为允许快速读取文档元素的内部格式。
当读取 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格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。
MySQL同时提供了一组操作JSON类型数据的内置函数。
更优化的存储格式，存储在JSON列中的JSON数据会被转成内部特定的存储格式，允许快速读取。
可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）

JSON 数据类型

JSON 对象

使用对象操作的方法进行查询：字段->'$.json属性'
使用函数进行查询：json_extract(字段, '$.json属性')
获取JSON数组/对象长度：JSON_LENGTH()

JSON 数组

使用对象操作的方法进行查询：字段->'$[0].属性'
使用函数进行查询：JSON_CONTAINS(字段,JSON_OBJECT('json属性', '内容'))
获取JSON数组/对象长度：JSON_LENGTH()

创建 JSON

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

CREATE TABLE `tinywan_json` (
 id INT ( 11 ) NOT NULL auto_increment,
 tag json NOT NULL COMMENT '标签列表',
 catagory json NOT NULL COMMENT '分类列表',
 create_time INT ( 11 ) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT charset = 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数据

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

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

INSERT INTO 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 in set (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 in set (0.07 sec)

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

mysql> select tag from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+
| tag                       |
+---------------------------+
| ["Low", "Middle", "High"] |
+---------------------------+
1 row in set (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 in set (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 in set (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"}' as JSON);
+----+---------------------------+-----------------------------+-------------+
| id | tag                       | catagory                    | create_time |
+----+---------------------------+-----------------------------+-------------+
|  2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL        |
+----+---------------------------+-----------------------------+-------------+
1 row in set (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 in set (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 in set (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 in set (0.08 sec)

mysql> select * from tinywan_json where JSON_CONTAINS(catagory,23,'$.age');
Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type 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 in set (0.07 sec)

小结

JSON类型无须预定义字段，适合拓展信息的存储
单个JSON文档的大小不能超过4G；单个KEY的大小不能超过两个字节，即64K
JSON类型适合应用于不常更新的静态数据
对搜索较频繁的数据建议增加虚拟列并建立索引

MySQL 5.7 JSON 数据类型使用总结

JSON类型的存储结构

JSON数据类型意义

JSON 数据类型

JSON 对象

JSON 数组

创建 JSON

写入JSON数据

查询 JSON

JSON 条件查询

小结

相关推荐

C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)

程序员的开源月刊《HelloGitHub》第 71 期

详细介绍一下Redis的Watch机制，可以利用Watch机制来做什么?

假如有100W个用户抢一张票，除了负载均衡办法，怎么支持高并发?

Java面试必考问题:什么是乐观锁与悲观锁

如何将AI助手接入微信（打开ai手机助手）

redission YYDS spring boot redission 使用

SparkSQL——DataFrame的创建与使用

一文带你了解Redis与Memcached? redis与memcached的区别

如何利用Redis进行事务处理呢? 如何利用redis进行事务处理呢英文