MySQL之JSON数据类型CRUD

作者: 社会我大爷 | 来源:发表于2022-03-03 23:20 被阅读0次

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

MySQL之JSON数据类型CRUD

JSON数据类型：

JSON较字符类型优势：

劣势：

JSON使用：

0、验证函数：

1、新增：

2、查询：

3、更新

4、删除

JSON性能优化：

JSON函数参考：

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读