从 MySQL 5.7.8 开始，MySQL 支持原生的 JSON 数据类型。

 

一. 创建json(不可以设置长度,可以是null,不能用有默认值)

mysql> CREATE TABLE lnmp (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `category` JSON,
    `tags` JSON,
    PRIMARY KEY (`id`)
);

mysql> DESC lnmp;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| category | json             | YES  |     | NULL    |                |
| tags     | json             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

 

二. 插入数据

可以支持字典,列表,以及Null,可以直接插入,也可以通过专门的函数JSON_OBJECT, JSON_ARRAY生成json格式的数据.

INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]');
INSERT INTO `lnmp` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "php.net"),JSON_ARRAY(1, 3, 5));

查看插入的数据

 

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
|  2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)

 

三.查询数据

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

 

mysql> SELECT id, category->'$.id', category->'$.name', tags->'$[0]', tags->'$[2]' FROM lnmp;
+----+------------------+--------------------+--------------+--------------+
| id | category->'$.id' | category->'$.name' | tags->'$[0]' | tags->'$[2]' |
+----+------------------+--------------------+--------------+--------------+
|  1 | 1                | "lnmp.cn"          | 1            | 3            |
|  2 | 2                | "php.net"          | 1            | 5            |
+----+------------------+--------------------+--------------+--------------+
2 rows in set (0.00 sec)

可以看到对应字符串类型的 category->'$.name' 中还包含着双引号，这其实并不是想要的结果，可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的

 

mysql> SELECT id, category->'$.name', JSON_UNQUOTE(category->'$.name'), category->>'$.name' FROM lnmp;
+----+--------------------+----------------------------------+---------------------+
| id | category->'$.name' | JSON_UNQUOTE(category->'$.name') | category->>'$.name' |
+----+--------------------+----------------------------------+---------------------+
|  1 | "lnmp.cn"          | lnmp.cn                          | lnmp.cn             |
|  2 | "php.net"          | php.net                          | php.net             |
+----+--------------------+----------------------------------+---------------------+
2 rows in set (0.00 sec)

 

下面说下 JSON 作为条件进行搜索。

虽然插入的时候可以用字符串的形式插入,但是实际上json字段的存储方式并不等同于字符串,查询的时候直接用字符串相等是不可以的.

可以通过cast方法将字符串转为json

mysql> SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' as JSON);
+----+------------------------------+-----------+
| id | category                     | tags      |
+-