从 MySQL 5.7.8 开始，MySQL 支持原生的 JSON 数据类型。
创建 JSON
类似 varchar，设置 JSON 主要将字段的 type 是 json, 不能设置长度，可以是 NULL 但不能有默认值。
mysql> CREATE TABLE tb_json_test(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category` JSON, `tags` JSON, PRIMARY KEY (`id`) );
mysql> DESC tb_json_test;+----------+------------------+------+-----+---------+----------------+| 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)
这样 JSON 的字段就创建好了。
插入 JSON
就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式
mysql> INSERT INTO `tb_json_test` (category, tags) VALUES ('{"id": 1, "name": "apple"}', '[1, 2, 3]');
Query OK, 1 row affected (0.01 sec)
MySQL 也有专门的函数 JSON_OBJECT，JSON_ARRAY 生成 json 格式的数据
mysql> INSERT INTO `tb_json_test` (category, tags) VALUES (JSON_OBJECT("id", 2, "name", "banana"), JSON_ARRAY(1, 3, 5));
Query OK, 1 row affected (0.00 sec)
查看插入的数据
mysql> SELECT * FROM tb_json_test;+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] || 2 | {"id": 2, "name": "banana"} | [1, 3, 5] |+----+------------------------------+-----------+2 rows in set (0.00 sec) 更多生成 JSON 值的函数请参考：
查询 JSON
查询 json 中的数据用 column->path 的形式，其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]
mysql> SELECT id, category->'$.id', category->'$.name', tags->'$[0]', tags->'$[2]' FROM tb_json_test;+----+------------------+--------------------+--------------+--------------+| id | category->'$.id' | category->'$.name' | tags->'$[0]' | tags->'$[2]' |+----+------------------+--------------------+--------------+--------------+| 1 | 1 | "apple" | 1 | 3 || 2 | 2 | "banana" | 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 tb_json_test;+----+--------------------+----------------------------------+---------------------+| id | category->'$.name' | JSON_UNQUOTE(category->'$.name') | category->>'$.name' |+----+--------------------+----------------------------------+---------------------+| 1 | "apple" | apple | apple || 2 | "banana" | banana | banana |+----+--------------------+----------------------------------+---------------------+2 rows in set (0.00 sec)
下面说下 JSON 作为条件进行搜索。因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的
mysql> SELECT * FROM tb_json_test WHERE category = '{"id": 1, "name": "apple"}';
Empty set (0.00 sec)
这时可以通过 CAST 将字符串转成 JSON 的形式
mysql> SELECT * FROM tb_json_test WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' as JSON);+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.00 sec) 通过 JSON 中的元素进行查询, 对象型的查询同样可以通过 column->path
mysql> SELECT * FROM tb_json_test WHERE category->'$.name' = 'apple';+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.00 sec) 上面有提到 column->path 形式从 select 中查询出来的字符串是包含双引号的，但作为条件这里其实没什么影响，-> 和 ->> 结果是一样的
mysql> SELECT * FROM tb_json_test WHERE category->>'$.name' = 'apple';+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.00 sec) 要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的
mysql> SELECT * FROM tb_json_test WHERE category->'$.id' = '1';
Empty set (0.00 sec)
mysql> SELECT * FROM tb_json_test WHERE category->'$.id' = 1;+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.00 sec) 可以看到搜索字符串 1 和整型 1 的结果是不一样的。
除了用 column->path 的形式搜索，还可以用JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现这个错误
mysql> SELECT * FROM tb_json_test WHERE JSON_CONTAINS(category, 1, '$.id');
ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.
这里必须是要字符串 1
mysql> SELECT * FROM tb_json_test WHERE JSON_CONTAINS(category, '1', '$.id');+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.01 sec) 对于数组类型的 JSON 的查询，比如说 tags 中包含有 2 的数据，同样要用 JSON_CONTAINS 函数，同样第二个参数也需要是字符串
mysql> SELECT * FROM tb_json_test WHERE JSON_CONTAINS(tags, '2');+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 2, 3] |+----+------------------------------+-----------+1 row in set (0.00 sec) 更多搜索 JSON 值的函数请参考：
更新 JSON
如果是整个 json 更新的话，和插入时类似的。
mysql> UPDATE tb_json_test SET tags = '[1, 3, 4]' WHERE id = 1;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tb_json_test ;+----+------------------------------+-----------+ | id | category | tags | +----+------------------------------+-----------+ | 1 | {"id": 1, "name": "apple"} | [1, 3, 4] | | 2 | {"id": 2, "name": "banana"} | [1, 3, 5] |+----+------------------------------+-----------+2 rows in set (0.00 sec) 但如果要更新 JSON 下的元素，MySQL 并不支持 column->path 的形式
mysql> UPDATE lnmp SET category->'$.name' = 'pear', tags->'$[0]' = 2 WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->'$.name' = 'pear', tags->'$[0]' = 2 WHERE id = 1' at line 1
则可能要用到以下几个函数
JSON_INSERT() 插入新值，但不会覆盖已经存在的值
mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'pear', '$.weight', '100') WHERE id = 1;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tb_json_test;+----+----------------------------------------------------+-----------+| id | category | tags |+----+----------------------------------------------------+-----------+| 1 | {"id": 1, "weight": "100", "name": "apple"} | [1, 3, 4] || 2 | {"id": 2, "name": "banana"} | [1, 3, 5] |+----+----------------------------------------------------+-----------+2 rows in set (0.00 sec) 可以看到 name 没有被修改，但新元素 weight 已经添加进去
JSON_SET() 插入新值，并覆盖已经存在的值
mysql> UPDATE tb_json_test SET category = JSON_SET(category, '$.color', 'yellow', '$.weight', '200') WHERE id = 1;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tb_json_test;+----+----------------------------------------------------------------------------------+-----------+| id | category | tags |+----+----------------------------------------------------------------------------------+-----------+| 1 | {"id": 1, "weight": "200", "color": "yellow", "name": "apple"} | [1, 3, 4] || 2 | {"id": 2, "name": "banana"} | [1, 3, 5] |+----+----------------------------------------------------------------------------------+-----------+2 rows in set (0.00 sec) 可以看到 color已经插入，weight已经被修改
JSON_REPLACE() 只替换存在的值
mysql> UPDATE tb_json_test SET category = JSON_REPLACE(category, '$.name', 'orange', '$.weight', '150') WHERE id = 2;
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM lnmp;+----+----------------------------------------------------------------------------------+-----------+| id | category | tags |+----+----------------------------------------------------------------------------------+-----------+| 1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] || 2 | {"id": 2, "name": "orange"} | [1, 3, 5] |+----+----------------------------------------------------------------------------------+-----------+2 rows in set (0.00 sec) 可以看到 name 已经被替换，weight 不存在被忽略。
JSON_REMOVE() 删除 JSON 元素
mysql> UPDATE tb_json_test SET category = JSON_REMOVE(category, '$.weight', '$.color') WHERE id = 1;
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM lnmp;+----+------------------------------+-----------+| id | category | tags |+----+------------------------------+-----------+| 1 | {"id": 1, "name": "apple"} | [1, 3, 4] || 2 | {"id": 2, "name": "banana"} | [1, 3, 5] |+----+------------------------------+-----------+2 rows in set (0.00 sec) 更多函数请参考：