废话不多说，直接上实例。
一、json结构
创建测试表
|
1
2
3
4
5
6
|
CREATE
TABLE
`article` (
`id`
int
(11) unsigned
NOT
NULL
AUTO_INCREMENT,
`category` json
NOT
NULL
,
`tags` json
NOT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4
DEFAULT
CHARSET=utf8
|
分析：article表中的字段category与tags均为json类型
填写测试数据
|
1
2
3
4
|
INSERT
INTO
`article`
VALUES
(1,
'{\"id\": 1, \"name\": \"php\"}'
,
'[\"php\", \"mysql\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]'
),
(2,
'{\"id\": 2, \"name\": \"java\"}'
,
'[\"java\", \"mysql\", \"oracel\", \"linux\", \"nginx\", \"redis\", \"memcache\", \"mongodb\"]'
),
(3,
'{\"id\": \"3\", \"name\": \"c#\"}'
,
'[\"c\", \"c++\", \"OS\", \"linux\", \"unix\", \"IBM\"]'
);
|
总体预览
二、json查询
select id,json_extract(category,'$.name') as name from test.article;#提取json字段里面的信息
# column->path形式 访问json中的元素 category->'$.name'
select id,category->'$.name' as name from test.article;#提取json字段里面的信息(访问json中的元素 category->'$.name')
select id,json_unquote(json_extract(category,'$.name')) as name from test.article;#提取json字段里面的信息,json_unqoute去双引号
select id,json_unquote(category->'$.name') as name from test.article;#提取json字段里面的信息，json_unqoute去双引号
select id,category->>'$.name' as name from test.article;
select * from test.article where category='{"id": 1, "name": "php"}'; #json不同于字符串，不能当作字符串做比较
select * from test.article where category=cast('{"id": 1, "name": "php"}' as JSON); #通过CAST将字符串转换成JSON形式
select * from test.article where category->'$.name'='java';
select * from test.article where category->>'$.name'='java';
#JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的
select * from test.article where category->'$.id'='2';#字符号串
select * from test.article where category->'$.id'=2;#整形
select * from test.article where category->'$.id'='3';#字符号串
select * from test.article where json_extract(category,'$.id')='3';#字符号串
select * from test.article where json_contains(category,'2','$.id');#整数
select * from test.article where json_contains(category,'"3"','$.id');#字符号串
select * from test.article where json_contains(tags,'"linux"');#字符号串
2、查询json格式的字段
mysql> select jsn_extract(data, '.name′),jsnextract(data,′.address') from user;
+-----------------------------+-------------------------------+
| jsn_extract(data, '.name′)|jsnextract(data,′.address') |
+-----------------------------+-------------------------------+
| "David" | "Shangahai" |
| "Amy" | NULL |
+-----------------------------+-------------------------------+
2 rows in set (0.00 sec)
3、给json格式的某个键字段创建索引。首先创建虚拟列，之后在改虚拟列上创建索引。
mysql> ALTER TABLE user ADD user_name varchar(128)
-> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select user_name from user;
+-----------+
| user_name |
+-----------+
| "Amy" |
| "David" |
+-----------+
2 rows in set (0.00 sec)
mysql> alter table user add index idx_username (user_name);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
4、之后通过虚拟列名对json特定列进行索引查询：
mysql> explain select * from user where user_name='"Amy"'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: idx_username
key: idx_username
key_len: 131
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
三、json更新
更新 JSON
如果是整个 json 更新的话，和插入时类似的。
mysql> UPDATE lnmp 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 lnmp;
+----+------------------------------+-----------+
| id | category | tags |
+----+------------------------------+-----------+
| 1 | {"id": 1, "name": "lnmp.cn"} | [1, 3, 4] |
| 2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)
但如果要更新 JSON 下的元素，MySQL 并不支持 column->path 的形式
mysql> UPDATE lnmp SET category->'$.name' = 'lnmp', 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' = 'lnmp', tags->'$[0]' = 2 WHERE id = 1' at line 1
则可能要用到以下几个函数
JSON_INSERT() 插入新值，但不会覆盖已经存在的值
mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------+-----------+
| id | category | tags |
+----+----------------------------------------------------+-----------+
| 1 | {"id": 1, "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
| 2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
+----+----------------------------------------------------+-----------+
2 rows in set (0.00 sec)
可以看到 name 没有被修改，但新元素 url 已经添加进去
JSON_SET() 插入新值，并覆盖已经存在的值
mysql> UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;
Query OK, 1 row affected (0.00 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": "php.net"} | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)
可以看到 host 已经插入，url 已经被修改
JSON_REPLACE() 只替换存在的值
mysql> UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') 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": "php"} | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+
2 rows in set (0.00 sec)
可以看到 name 已经被替换，url 不存在被忽略。
JSON_REMOVE() 删除 JSON 元素
mysql> UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') 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": "lnmp.cn"} | [1, 3, 4] |
| 2 | {"id": 2, "name": "php"} | [1, 3, 5] |
+----+------------------------------+-----------+
2 rows in set (0.00 sec)
更多函数请参考：http://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html
MySQL JSON 在 PHP 中的表现
虽然在 MySQL 是个JSON 类型，但实际在 PHP 应用中返回的是 JSON 格式的字符串
array(2) {
[0]=>
array(3) {
["id"]=>
string(1) "1"
["category"]=>
string(28) "{"id": 1, "name": "lnmp.cn"}"
["tags"]=>
string(9) "[1, 3, 4]"
}
[1]=>
array(3) {
["id"]=>
string(1) "2"
["category"]=>
string(24) "{"id": 2, "name": "php"}"
["tags"]=>
string(9) "[1, 3, 5]"
}
}
