MySQL之JSON数据类型CRUD

JSON数据类型：

从mysql5.7.8开始开始支持，json数据类型。可以通过客户端创建json字段表并操作检索json对象。

JSON较字符类型优势：

json字段以二进制为基础存储，读取时不需要重新解析文档并且json对象的键是逻辑排序的可实现快速检索对象
自动验证json文档是否规范
json可存储大字段类似BLOB/TEX并提供原生json函数
innodb行锁模式下，json一行存储多个属性值在一定程度上可以减轻rowlock争用

劣势：

JSON 数据的存储方式类似于 BLOB/TEXT 在 MySQL 中的存储可能会移动到溢出页中
每当修改JSON文档时会重写整个类型出现写入放大
过于free schema的设计可能导致冗余数据占用内存

json使用建议：max_allowed_packet参数了json存储上限，建议不要超过MB大小，除非是冷归档数据

JSON使用：

0、验证函数：

/*
JSON_VALID（值）
返回0: 无效json
返回1: 有效json
*/
mysql[oldlee] > select JSON_VALID('{"age": "18", "job": "DBA", "name": "zhangsan", "score": "A"} ');                                  
+------------------------------------------------------------------------------+
| JSON_VALID('{"age": "18", "job": "DBA", "name": "zhangsan", "score": "A"} ') |
+------------------------------------------------------------------------------+
|                                                                            1 |
+------------------------------------------------------------------------------+

1、新增：

-- 创建添加数据
mysql[oldlee] > create table t1(id int,info json);
mysql[oldlee] > insert into t1 (id, info) values (1, '{"name":"zhangsan", "age":"18", "score":"A"}');
 
-- 使用函数JSON_OBJECT
mysql[oldlee] > insert into t1 (id, info) values (2, JSON_OBJECT(   "name", "wangwu",   "age", "20",   "score", "0" ));
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------+
| id   | info                                            |
+------+-------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "score": "A"} |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}   |
+------+-------------------------------------------------+
 
-- 如果插入的值不符合json规范直接报错：
mysql[oldlee] > insert into t1 values(1,'hahah');        
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value (or column) 'hahah'.
 
-- 插入数组
mysql[oldlee] > insert into t1 (id, info) values (3, '{"name": "maliu","age": "30","score": "100","course" : ["Python","JAVA","MySQL"] }');
-- 使用函数JSON_ARRAY插入数组
mysql[oldlee] > insert into t1 (id, info)
    -> values (4, JSON_OBJECT(
    ->   "name", "lisi",
    ->   "age", "50",
    ->   "score", "60",
    ->   "course", JSON_ARRAY("PHP","Oracle")
    -> ));
 
mysql[oldlee] > select * from t1;
+------+---------------------------------------------------------------------------------------+
| id   | info                                                                                  |
+------+---------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "score": "A"}                                       |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                         |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]} |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}             |
+------+---------------------------------------------------------------------------------------+

2、查询：

/*
使用JSON_EXTRACT(列,路径表达式)函数
路径表达式：以 $ 符号开头，指定一个点，然后是要查找的属性的名称
*/
mysql[oldlee] > select id,JSON_EXTRACT(info, '$.name') AS name from t1;
+------+------------+
| id   | name       |
+------+------------+
|    1 | "zhangsan" |
|    2 | "wangwu"   |
|    3 | "maliu"    |
|    4 | "lisi"     |
+------+------------+
 
-- 数组查询使用 路径表达式+数组索引
mysql[oldlee] > select id,JSON_EXTRACT(info, '$.course') AS name from t1;   
+------+-----------------------------+
| id   | name                        |
+------+-----------------------------+
|    1 | NULL                        |
|    2 | NULL                        |
|    3 | ["Python", "JAVA", "MySQL"] |
|    4 | ["PHP", "Oracle"]           |
+------+-----------------------------+
 
mysql[oldlee] > select id,JSON_EXTRACT(info, '$.course[0]') AS name from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | NULL     |
|    2 | NULL     |
|    3 | "Python" |
|    4 | "PHP"    |
+------+----------+
 
/*
使用JSON_EXTRACT(列,路径表达式) 配合where过滤json数据
*/
mysql[oldlee] > select id,info from t1 where JSON_EXTRACT(info, '$.score') = '100';
+------+---------------------------------------------------------------------------------------+
| id   | info                                                                                  |
+------+---------------------------------------------------------------------------------------+
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]} |
+------+---------------------------------------------------------------------------------------+
 
-- JSON_EXTRACT快捷键：
mysql[oldlee] > select id,info from t1 where info -> '$.score' = '100';                                   
+------+---------------------------------------------------------------------------------------+
| id   | info                                                                                  |
+------+---------------------------------------------------------------------------------------+
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]} |
+------+---------------------------------------------------------------------------------------+
 
/*
使用 JSON_CONTAINS 搜索数据
JSON_CONTAINS(json字段，要搜索的文档[在目标中搜索的可选路径值])
如果存在则返回 1，否则返回 0
*/
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "score": "A"}                                                                                           |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
 
mysql[oldlee] > SELECT id, JSON_CONTAINS(info, '{"course":"MySQL"}') AS '是否包含' FROM t1;        
+------+--------------+
| id   | 是否包含     |
+------+--------------+
|    1 |            0 |
|    2 |            0 |
|    3 |            1 |
|    4 |            0 |
+------+--------------+
 
-- 使用JSON_OBJECT构造一个json对象并检索
mysql[oldlee] > SELECT id, JSON_CONTAINS(info, JSON_OBJECT("course", "MySQL")) AS '是否包含' FROM t1;
+------+--------------+
| id   | 是否包含     |
+------+--------------+
|    1 |            0 |
|    2 |            0 |
|    3 |            1 |
|    4 |            0 |
+------+--------------+
 
-- 指定检索路径，查找extra是否包含 {"company": "xxx"}
 
mysql[oldlee] > SELECT id, JSON_CONTAINS(info, '{"company": "xxx"}','$.extra') AS info FROM t1;
+------+------+
| id   | info |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    4 | NULL |
|    5 |    1 |
+------+------+
 
 
/*
JSON_SEARCH 返回搜索值的 属性路径
语法：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char [, path] ])
 
json_doc：这是要搜索的 JSON 字段或文档
one_or_all：指定“one”在第一次匹配后终止搜索，或指定“all”以返回所有匹配结果
search_str：要在 json_doc 中搜索的字符串
escape_char：在搜索字符串中指定通配符
path：在指定路径中搜索字符串
*/
-- 例如查询成绩为 A的路径：
mysql[oldlee] > SELECT id, JSON_SEARCH(info, 'one', 'A') AS search_result FROM t1; 
+------+---------------+
| id   | search_result |
+------+---------------+
|    1 | "$.score"     |
|    2 | NULL          |
|    3 | NULL          |
|    4 | NULL          |
|    5 | NULL          |
+------+---------------+
 
-- 查询所有包含 MySQL的路径：
mysql[oldlee] > SELECT id, JSON_SEARCH(info, 'all', '%MySQL%') AS search_result FROM t1;
+------+---------------+
| id   | search_result |
+------+---------------+
|    1 | NULL          |
|    2 | NULL          |
|    3 | "$.course[2]" |
|    4 | NULL          |
|    5 | "$.course[2]" |
+------+---------------+
 
/*
JSON_TYPE 查找值的类型
*/
mysql[oldlee] > SELECT '["a", "b", "c"]' AS json_data, JSON_TYPE('["a", "b", "c"]') AS jtype;
+-----------------+-------+
| json_data       | jtype |
+-----------------+-------+
| ["a", "b", "c"] | ARRAY |
+-----------------+-------+

3、更新

/*
JSON_INSERT(json_doc, path, val [, path, val…] ) 将新数据添加到现有 JSON 值并返回更新的 JSON 值,如果更新的key存在则忽略
json_doc：要更新的 JSON 字段
path: 添加新值的路径
val： 为路径添加的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_INSERT(info, '$.extra','[{"company": "xxx"}]') WHERE id = 1; 
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "extra": "[{"company": "xxx"}]", "score": "A"}                                                        |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
 
/*
JSON_REPLACE 更新现有值
JSON_REPLACE (json_doc, path, val [, path, val…] )
json_doc：要更新的 JSON 字段
path：更新值的路径
val：要更新的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_REPLACE(info, '$.extra','[{"company": "xxx"}]') WHERE id = 1;     
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "extra": "[{"company": "xxx"}]", "score": "A"}                                                         |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
 
/*
JSON_SET 插入和更新，相当于JSON_REPLACE和JSON_INSERT合并功能
JSON_SET (json_doc, path, val [, path, val…] )
json_doc：要更新的 JSON 字段
path：更新值或插入新属性的路径
val：要为属性更新或插入的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_SET(info, '$.extra','[{"company": "兑吧"}]','$.job','DBA') WHERE id = 1;
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "job": "DBA", "name": "zhangsan", "extra": "[{"company": "兑吧"}]", "score": "A"}                                       |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+

4、删除

4.1、删除一行：和常规sql一样操作

4.2、从json数据中删除属性 update + JSON_REMOVE：

/*
JSON_INSERT(json_doc, path, val [, path, val…] ) 将新数据添加到现有 JSON 值并返回更新的 JSON 值,如果更新的key存在则忽略
json_doc：要更新的 JSON 字段
path: 添加新值的路径
val： 为路径添加的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_INSERT(info, '$.extra','[{"company": "xxx"}]') WHERE id = 1; 
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "extra": "[{"company": "xxx"}]", "score": "A"}                                                        |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
 
/*
JSON_REPLACE 更新现有值
JSON_REPLACE (json_doc, path, val [, path, val…] )
json_doc：要更新的 JSON 字段
path：更新值的路径
val：要更新的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_REPLACE(info, '$.extra','[{"company": "xxx"}]') WHERE id = 1;     
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "name": "zhangsan", "extra": "[{"company": "xxx"}]", "score": "A"}                                                         |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
 
/*
JSON_SET 插入和更新，相当于JSON_REPLACE和JSON_INSERT合并功能
JSON_SET (json_doc, path, val [, path, val…] )
json_doc：要更新的 JSON 字段
path：更新值或插入新属性的路径
val：要为属性更新或插入的值
*/
 
mysql[oldlee] > UPDATE t1 SET info = JSON_SET(info, '$.extra','[{"company": "xxx"}]','$.job','DBA') WHERE id = 1;
mysql[oldlee] > select * from t1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id   | info                                                                                                                                      |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | {"age": "18", "job": "DBA", "name": "zhangsan", "extra": "[{"company": "xxx"}]", "score": "A"}                                       |
|    2 | {"age": "20", "name": "wangwu", "score": "0"}                                                                                             |
|    3 | {"age": "30", "name": "maliu", "score": "100", "course": ["Python", "JAVA", "MySQL"]}                                                     |
|    4 | {"age": "50", "name": "lisi", "score": "60", "course": ["PHP", "Oracle"]}                                                                 |
|    5 | {"age": "29", "name": "zhaoqi", "extra": [{"company": "xxx"}, {"salary": "20k"}], "score": "80", "course": ["Python", "JAVA", "MySQL"]} |
+------+-------------------------------------------------------------------------------------------------------------------------------------------+

JSON性能优化：

使用虚拟列创建函数索引，虚拟列参考：MySQL之虚拟列

mysql[oldlee] > explain select id,info from t1 where JSON_EXTRACT(info, '$.score') = '100';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
mysql[oldlee] > ALTER TABLE t1 ADD COLUMN js_score VARCHAR(100) GENERATED ALWAYS AS (JSON_EXTRACT(info, '$.score') );
mysql[oldlee] > CREATE INDEX idx_js_score ON t1(js_score);
 
mysql[oldlee] > explain select id,info from t1 where js_score = '100';                               
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_js_score  | idx_js_score | 103     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

JSON函数参考：

https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,117评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,328评论 1赞 293
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,839评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,007评论 0赞 206
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,384评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,629评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,880评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,593评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,313评论 1赞 243
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,575评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,066评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,392评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,052评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,082评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,844评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,662评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,575评论 2赞 270