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 直接标明即可。

1
2
3
4
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 

1
2
3
4
5
-- 直接插入字符串
INSERT INTO test_data (data) VALUES ('{"name": "张飞", "age": 55}');

-- 使用 JSON 函数
INSERT INTO test_data (data) VALUES (JSON_OBJECT("name","张飞","age",55));

更新 JSON 数据

两种方式

  • 重新设置字段的值，也就是全部数据都更新
  • 使用 JSON 函数更新部分 key
1
2
3
4
5
6
7
8
9
10
11
-- 使用 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 函数操作数据

1
2
-- 删除 key
UPDATE test_date SET data = JSON_REMOVE(data,'$.name','$.age') WHERE id = 1;

插入 JSON 数组数据

插入 JSON 中数组的数据，使用 JSON 函数实现

1
2
3
4
5
-- 末尾追加数据
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 中数组的数据

1
2
-- 删除数组中的数据
UPDATE test_date SET data = JSON_REMOVE(data,'$[0]') WHERE id = 1;

创建 JSON 索引

JSON 字段不能直接创建为索引，但是可以使用 JSON 中的某个字段创建，官方的例子：

1
2
3
4
5
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 是字符串，则需要加上双引号。

1
select id, data -> '$."name"' as name from test_data;

查询结果如果是字符串会包含双引号，使用 JSON_UNQUOTE 函数可以去除结果的双引号

1
select id, JSON_UNQUOTE(data -> '$."name"') as name from test_data;

使用 ->> 符号替换 -> 也可以去除双引号。

JSON 中的值也可以写在 where 条件之后。

判断 JSON 中的数组是否包

1
2
3
4
5
6
-- 单个值判断
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 （一定要全大写）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 使用 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)