[Database] MySQL 5.7+ JSON 字段的使用的处理

简介

MySQL5.7.x增加了对JSON字段的支持,根据官方文档 一下常用的操作摘取

方法 / 步骤

一: 创建测试数据

在 MySQL 8.0.13 之前，不允许对 BLOB，TEXT，GEOMETRY，JSON 字段设置默认值。从 MySQL 8.0.13 开始，取消了这个限制。

# 创建表
CREATE TABLE `goods` (
  `goods_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `goods_name` varchar(55) DEFAULT NULL COMMENT '商品名称',
  `goods_attrs` text COMMENT '商品属性例如：{"color":"red","size":100,"sex":"famale"}',
  `support_store_ids` text COMMENT '支持门店id 格式:["10000","20000","30000"]',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

# 初始化数据
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (1, '5店通用女生VIP卡', '{\"type\":\"通店\",\"sex\":\"famale\"}', '[\"001\",\"002\",\"003\",\"004\",\"005\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (2, '5店通用男生VIP卡', '{\"type\":\"通店\",\"sex\":\"male\"}', '[\"001\",\"002\",\"003\",\"004\",\"005\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (3, '门店1男生卡', '{\"type\":\"门店1\",\"sex\":\"male\"}', '[\"001\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (4, '门店2男生卡', '{\"type\":\"门店2\",\"sex\":\"nomal\"}', '[\"002\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (5, '门店3男生卡', '{\"type\":\"门店3\",\"sex\":\"nomal\"}', '[\"003\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (6, '门店1女生卡', '{\"type\":\"门店1\",\"sex\":\"famale\"}', '[\"001\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (7, '门店2女生卡', '{\"type\":\"门店2\",\"sex\":\"famale\"}', '[\"002\"]');
INSERT INTO `goods`(`goods_id`, `goods_name`, `goods_attrs`, `support_store_ids`) VALUES (8, '门店3女生卡', '{\"type\":\"门店3\",\"sex\":\"famale\"}', '[\"003\"]');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1.1 新增操作

• 手动插入Json类型的记录

INSERT INTO `goods` ( `goods_id`, `goods_name`, `goods_attrs`, `support_store_ids` )
VALUES ( NULL,'一二门店男生卡', JSON_OBJECT( "type", "门店12", "sex", "male" ), JSON_ARRAY( "001", "002" ))

# 数组追加字段
SELECT JSON_ARRAY_APPEND(support_store_ids, '$', "006") FROM goods WHERE goods_id = 1
1
2
3
4
5

二：查询操作

JSON_EXTRACT(json_doc, path[, path] …)

• 普通List查询

 # 查询001店能购买的商品
 # 要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，
 # 比如说整型和字符串是严格区分的,即 "001"和001
SELECT * FROM goods WHERE JSON_CONTAINS(support_store_ids, '"001"')

# 查询属性type是通店的的记录
# //用JSON_CONTAINS 函数，但和 *column->path *的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现错误

SELECT * FROM goods WHERE JSON_CONTAINS(goods_attrs, '"通店"', '$.type')

 # 查询001 和 002 店能购买的商品
 SELECT * FROM goods WHERE JSON_CONTAINS(support_store_ids, '"001"') OR JSON_CONTAINS(support_store_ids, '"002"')
1
2
3
4
5
6
7
8
9
10
11
12

一般对应字符串类型的 category->’$.name’ 中还包含着双引号，这其实并不是想要的结果，可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 * ->> *这个和 JSON_UNQUOTE 是等价的

# 查找type字段中key属性是通店的记录
SELECT * FROM goods WHERE goods_attrs->'$.type'='通店';

# 查询json的值,即键的值
SELECT
	goods_id,
	goods_name,
	goods_attrs -> '$.type' AS type_name,
	JSON_UNQUOTE( goods_attrs -> '$.type' ) 
FROM goods WHERE goods_attrs -> '$.type' = '通店';
1
2
3
4
5
6
7
8
9
10

三：更新 / 删除

#更新数组按以往的更新就行
UPDATE goods SET support_store_ids = '["001","002"]' WHERE goods_id = 6
1
2

但如果要更新 JSON 下的元素，MySQL 并不支持 column->path的形式，则可能要用到以下几个函数

3.1 JSON_INSERT()

• JSON_INSERT() 插入新值，但不会覆盖已经存在的值

-- 追加新的键值
UPDATE goods SET goods_attrs = JSON_INSERT(goods_attrs,'$.type1','通店111') WHERE goods_id = 6
1
2

3.2 JSON_SET()

• JSON_SET() 插入新值，并覆盖已经存在的值

UPDATE goods SET goods_attrs = JSON_SET(goods_attrs,'$.type','门店666') WHERE goods_id = 6
1

3.3 JSON_REPLACE()

• JSON_REPLACE() 只替换存在的值

UPDATE goods SET goods_attrs = JSON_REPLACE(goods_attrs,'$.type','门店777') WHERE goods_id = 6
1

3.4 JSON_REMOVE()

• JSON_REMOVE() 删除 JSON 元素

UPDATE goods SET goods_attrs = JSON_REMOVE(goods_attrs, '$.type','$.type1') WHERE goods_id = 6;
1

参考资料 & 致谢

[1] 官方文档
[2] MySQL的json查询