简介
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.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二：查询操作
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"')一般对应字符串类型的 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' = '通店';
三：更新 / 删除
#更新数组按以往的更新就行
UPDATE goods SET support_store_ids = '["001","002"]' WHERE goods_id = 6但如果要更新 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
3.2 JSON_SET()
JSON_SET()插入新值，并覆盖已经存在的值
UPDATE goods SET goods_attrs = JSON_SET(goods_attrs,'$.type','门店666') WHERE goods_id = 6
3.3 JSON_REPLACE()
JSON_REPLACE()只替换存在的值
UPDATE goods SET goods_attrs = JSON_REPLACE(goods_attrs,'$.type','门店777') WHERE goods_id = 6
3.4 JSON_REMOVE()
JSON_REMOVE()删除 JSON 元素
UPDATE goods SET goods_attrs = JSON_REMOVE(goods_attrs, '$.type','$.type1') WHERE goods_id = 6;
参考资料 & 致谢
[1] 官方文档
[2] MySQL的json查询
