5 分钟学会 MySQL JSON 字段：查询、插入、删除、更新一篇讲清

目录一. 简介二. 查询JSON字段值三. 添加JSON字段值四. 删除JSON字段值五. 修改JSON字段值六. 特殊操作函数一. 简介JSON数据是我们在开发过程中几位常用的一种数据存储方式主要以KV键值对的形式存储数据在 MySQL5.7 之后提供了JSON字段在 MySQL8之后得到了很好的优化下面就是JSON格式数据与的例子以KV键值对的形式存储数据每个键值对之间使用 隔开即可。{ CPU: Kirin 980, system: android, storage: 512G }我们先准备好一张 product 商品表字段类型如下图所示方便一会操作展示然后在表中存放一些数据如下图数据均为编造仅为演示建表SQL和数据SQL如下想动手尝试小伙伴们可自行CVDROP TABLE IF EXISTS product; CREATE TABLE product ( id int NOT NULL, name varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NULL DEFAULT NULL, price decimal(10, 2) NULL DEFAULT NULL, colors json NULL, details json NULL, PRIMARY KEY (id) USING BTREE ) ENGINE InnoDB CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci ROW_FORMAT Dynamic; -- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO product VALUES (1, HuaWei P30, 6800.00, [\white\, \black\], {\CPU\: \Kirin 980\, \system\: \android\, \storage\: \512G\}); INSERT INTO product VALUES (2, IPhone 10, 8800.00, [\white\, \black\, \grey\], {\CPU\: \A8\, \system\: \iOS\, \storage\: \256G\}); INSERT INTO product VALUES (3, IPad Pro, 5800.00, [\white\, \grey\], {\CPU\: \A9\, \system\: \iOS\, \storage\: \512G\}); INSERT INTO product VALUES (4, XiaoMi 10, 3800.00, [\red\, \black\, \grey\], {\CPU\: \晓龙 865\, \system\: \android\, \storage\: \512G\}); INSERT INTO product VALUES (5, Vivo 20, 5800.00, [\white\, \grey\], {\CPU\: \晓龙 865\, \system\: \android\, \storage\: \256G\}); SET FOREIGN_KEY_CHECKS 1;二. 查询JSON字段值方式一JSON_EXTRACT(JSON字段值$.key) 查询JSON中想要的字段。括号内第一个字段为要查询的JSON字段的字段名第二处固定格式为 $.要查询的Key$ 美元符就代表JSON字段当然了我们也可以添加其他的WHERE条件# 查询JSON字段中Key为CPU的所有值并取别名CPU展示出来 SELECT *,JSON_EXTRACT(details, $.CPU) AS CPU FROM product; # 查询JSON字段details中CPU为A8的数据 SELECT *,JSON_EXTRACT(details, $.CPU) AS CPU FROM product WHERE JSON_EXTRACT(details, $.CPU) A8;运行上述SQL就可以查询到所有JSON字段中Key为CPU的对应的值如下所示SELECT details-$.CPU AS CPU FROM product方式二JSON字段名-$.要获取的值对应的Key 这种方法查询出来的结果带双引号使用方式如下使用一个大于号查询出来的结果中带有双引号SELECT details-$.CPU AS CPU FROM product方式三JSON字段名-$.要获取的值对应的Key 这种方法查询出来的结果不带双引号使用方式如下使用两个大于号查询出来的结果中没有双引号三. 添加JSON字段值JSON_INSERT(JSON字段$.要插入的Key要插入的值... 可以插入多个) 插入一条JSON数据# 向product表中details字段中添加color1和color2两个键值对 SELECT JSON_INSERT(details, $.color1,black,$.color2,red) FROM product;运行SQL就可以发现 details 字段中已经有了我们刚刚插入的color1和color2四. 删除JSON字段值JSON_REMOVE(JSON字段值$.要删除的Key)UPDATE product SET details JSON_REMOVE(details, $.color1,$.color2);再次查询就会发现color1和color2字段已经被删除五. 修改JSON字段值JSON_REPLACE(JSON字段值)# 更新id为1的数据中JSON字段CPU的值为Kirin9000 UPDATE product SET details JSON_REPLACE(details, $.CPU, Kirin9000) WHERE id 1;执行SQL更新操作查询我们更新的数据可以看到CPU的值已经被更新六. 特殊操作函数1JSON_KEYS(要查询的JSON字段值) 返回数据表中所有的JSON字段的 key# 查询 product 表中所有的JSON字段的 key SELECT JSON_KEYS(details) FROM product;2JSON_SET(JSON字段值$.key要插入的数据) 将数据插入JSON格式中有key则替换无key则新增# 更新product表中id为4的CPU的值为晓龙8,没有此字段则添加, UPDATE product SET details JSON_SET(details, $.CPU, 晓龙8) WHERE id 4; # 更新product表中id为1的color值为red,没有此字段则添加 UPDATE product SET details JSON_SET(details, $.color, red) WHERE id 1;SELECT * FROM product;查询数据验证是否执行成功如下所示