概述
背景
MySQL从5.7版本开始就支持JSON格式的数据，json作为一种自带结构的文本使得结构信息与可以与数据库解耦，更加灵活
基本操作
- 建表
mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);
json类型字段可以为NULL
- 插入数据
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');
json类型的字段必须时一个有效的json字符串
可以使用JSON_OBJECT()函数构造json对象：
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));
使用JSON_ARRAY()函数构造json数组：
mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));
查看test_user表中的数据
mysql> select * from test_user;
+----+-----------+--------------------------------------------+
| id | name | info |
+----+-----------+--------------------------------------------+
| 1 | xiaoming | {"age": 18, "sex": 1, "nick_name": "小萌"} |
| 2 | xiaohua | {"age": 17, "sex": 0} |
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]} |
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)
- 查询
表达式： 对象为json列->'$.键', 数组为json列->'$.键[index]'
mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user;
+-----------+---------------------+---------------+------------------+
| name | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' |
+-----------+---------------------+---------------+------------------+
| xiaoming | "小萌" | 1 | NULL |
| xiaohua | NULL | 0 | NULL |
| xiaozhang | NULL | 1 | 3 |
+-----------+---------------------+---------------+------------------+
3 rows in set (0.04 sec)
等价于：对象为JSON_EXTRACT(json列 , ‘ . 键 ′ ) ， 数 组 为 J S O N E X T R A C T ( j s o n 列 , ′ .键')，数组为JSON_EXTRACT(json列 , ' .键′)，数组为JSONEXTRACT(json列,′.键[index]’)
mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]') from test_user;
+-----------+-----------------------------------+-----------------------------+--------------------------------+
| name | JSON_EXTRACT(info, '$.nick_name') | JSON_EXTRACT(info, '$.sex') | JSON_EXTRACT(info, '$.tag[0]')
| +-----------+-----------------------------------+-----------------------------+--------------------------------+
| xiaoming | "小萌" | 1 | NULL |
| xiaohua | NULL | 0 | NULL |
| xiaozhang | NULL | 1 | 3 |
+-----------+-----------------------------------+-----------------------------+--------------------------------+
3 rows in set (0.04 sec)
可以用JSON_UNQUOTE函数将双引号去掉
mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming';
+----------+-----------------------------------+
| name | JSON_UNQUOTE(info->'$.nick_name') |
+----------+-----------------------------------+
| xiaoming | 小萌 |
+----------+-----------------------------------+
1 row in set (0.05 sec)
也可以直接使用操作符->>
mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming';
+----------+----------------------+
| name | info->>'$.nick_name' |
+----------+----------------------+
| xiaoming | 小萌 |
+----------+----------------------+
1 row in set (0.06 sec)
当然属性也可以作为查询条件
mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌';
+----------+----------------------+
| name | info->>'$.nick_name' |
+----------+----------------------+
| xiaoming | 小萌 |
+----------+----------------------+
1 row in set (0.05 sec)
可以通过虚拟列对JSON类型的指定属性进行快速查询，
创建虚拟列:
mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;
- 更新
使用JSON_INSERT()插入新值，但不会覆盖已经存在的值
mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;
看下结果
mysql> select * from test_user where id=2;
+----+---------+--------------------------------------------+-----------+
| id | name | info | nick_name |
+----+---------+--------------------------------------------+-----------+
| 2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花 |
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)
使用JSON_SET()插入新值，并覆盖已经存在的值
mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;
看下结果
mysql> select * from test_user where id=3;
+----+-----------+---------------------------------------------------------------+-----------+
| id | name | info | nick_name |
+----+-----------+---------------------------------------------------------------+-----------+
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | 小张 |
+----+-----------+---------------------------------------------------------------+-----------+
1 row in set (0.06 sec)
使用JSON_REPLACE()只替换存在的值
mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;
看下结果
mysql> select * from test_user where id=2;
+----+---------+--------------------------------------------+-----------+
| id | name | info | nick_name |
+----+---------+--------------------------------------------+-----------+
| 2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花 |
+----+---------+--------------------------------------------+-----------+
1 row in set (0.06 sec)片
可以看到tag没有更新进去
- 删除
使用JSON_REMOVE()删除JSON元素
mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;
看下结果
mysql> select * from test_user where id=1;
+----+----------+----------------------------------+-----------+
| id | name | info | nick_name |
+----+----------+----------------------------------+-----------+
| 1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌 |
+----+----------+----------------------------------+-----------+
1 row in set (0.05 sec)
缺点：
1、mysql json无法有效使用索引，每次查询都是full scan，所以当数据集增大时，性能会严重滑坡；
2、mysql 官方描述对json 多了structure的优化，但是在基于json做查询时这部分代价仍然较高，测试数据集并不十分大，当数据量多时，json操作复杂度的累计会更为严重；
参考链接：https://www.cnblogs.com/lkpnotice/p/6903187.html
https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html
https://www.jianshu.com/p/25161add5e4b
最后
以上就是眯眯眼跳跳糖为你收集整理的mysql5.7支持json类型背景基本操作缺点：的全部内容，希望文章能够帮你解决mysql5.7支持json类型背景基本操作缺点：所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复