MySQL JSON 数据操作
本笔记整合搜索信息,参考原文:
https://www.jianshu.com/p/6a9ca839c5b5
https://www.cnblogs.com/river2005/p/15754419.html
https://www.yiibai.com/mysql/generated-columns.html
创建 JSON 字段
使用 JSON 直接标明即可。
CREATE TABLE test_data (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);
插入 JSON 数据
两种方式
- 直接插入 JSON 字符串
- 使用 JSON 的相关函数插入
其他 JSON 函数:https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
-- 直接插入字符串
INSERT INTO test_data (data) VALUES ('{"name": "张飞", "age": 55}');
-- 使用 JSON 函数
INSERT INTO test_data (data) VALUES (JSON_OBJECT("name","张飞","age",55));
更新 JSON 数据
两种方式
- 重新设置字段的值,也就是全部数据都更新
- 使用 JSON 函数更新部分 key
-- 使用 update 重新设置 JSON 字段的值
update test_data set data='{"name": "张飞", "age": 55}' where id=1;
-- JSON 函数:JSON_INSERT,插入新值不会覆盖旧值
update test_data set data=JSON_INSERT(data, '$."name"', "李四", '$."age"', 200) where id=1;
-- JSON 函数:JSON_SET,插入新值并覆盖旧值
update test_data set data=JSON_INSERT(data, '$."name"', "二狗", '$."addr"', "天上人间") where id=1;
-- JSON 函数:JSON_REPLACE,仅替换存在的值
update test_data set data=JSON_INSERT(data, '$."name"', "王五", '$."addr"', "地下狂奔") where id=1;
删除 JSON 数据
使用 JSON 函数操作数据
-- 删除 key
UPDATE test_date SET data = JSON_REMOVE(data,'$.name','$.age') WHERE id = 1;
插入 JSON 数组数据
插入 JSON 中数组的数据,使用 JSON 函数实现
-- 末尾追加数据
UPDATE test_date SET tags = JSON_ARRAY_APPEND(tags,'$."tags"',4) WHERE id = 1;
-- 数组某个位置插入数据
UPDATE test_date SET tags = JSON_ARRAY_INSERT(tags,'$."tags"[0]',5) WHERE id = 1;
删除 JSON 数组数据
删除 JSON 中数组的数据
-- 删除数组中的数据
UPDATE test_date SET data = JSON_REMOVE(data,'$[0]') WHERE id = 1;
创建 JSON 索引
JSON 字段不能直接创建为索引,但是可以使用 JSON 中的某个字段创建,官方的例子:
CREATE TABLE test_data (
json_data JSON,
json_id INT GENERATED ALWAYS AS (json_data->'$."id"'),
INDEX index_json_id(json_id)
);
备注:GENERATED ALWAYS AS ()
生成列创建。当新数据插入表中时,生成列会立即计算。
查询 JSON 中的数据
使用:字段名 -> '$.key1.key2.key3[0]'
来获取数据,如果 key
是字符串,则需要加上双引号。
select id, data -> '$."name"' as name from test_data;
查询结果如果是字符串会包含双引号,使用 JSON_UNQUOTE
函数可以去除结果的双引号
select id, JSON_UNQUOTE(data -> '$."name"') as name from test_data;
使用 ->>
符号替换 ->
也可以去除双引号。
JSON 中的值也可以写在 where 条件之后。
判断 JSON 中的数组是否包
-- 单个值判断
SELECT * FROM test_data WHERE JSON_CONTAINS(tags,'1');
SELECT * FROM test_data WHERE JSON_CONTAINS(tags,'[1]');
-- 多个值判断
SELECT * FROM test_data WHERE JSON_CONTAINS(tags, '[1,2]');
判断 JSON 中的 NULL 值
判断一个字段存不存在 JSON 中
- 使用
is null
返回结果为 1
判断一个存在的字段但是值是 NULL
- 使用
is null
返回结果为 0 - 使用
JSON_TYPE()
判断值是不是等于NULL
(一定要全大写)
-- 使用 is null 无法判断是否为 null
mysql> select answer_json->'$."132"', answer_json->'$."132"' is null from question_answer;
+------------------------+--------------------------------+
| answer_json->'$."132"' | answer_json->'$."132"' is null |
+------------------------+--------------------------------+
| "没有" | 0 |
| "没有" | 0 |
| null | 0 |
| null | 0 |
| null | 0 |
| "没有" | 0 |
| "没有" | 0 |
+------------------------+--------------------------------+
7 rows in set (0.00 sec)
mysql>
-- 使用 JSON_TYPE 方式判断
mysql> select answer_json->'$."132"', JSON_TYPE(answer_json->'$."132"')='NULL' from question_answer;
+------------------------+------------------------------------------+
| answer_json->'$."132"' | JSON_TYPE(answer_json->'$."132"')='NULL' |
+------------------------+------------------------------------------+
| "没有" | 0 |
| "没有" | 0 |
| null | 1 |
| null | 1 |
| null | 1 |
| "没有" | 0 |
| "没有" | 0 |
| null | 1 |
+------------------------+------------------------------------------+
8 rows in set (0.00 sec)