MySQL 中 JSON 字段增删改查相关函数及使用演示，简单易学，5分钟上手

目录

1. 简介

2. 查询JSON字段值

3. 添加JSON字段值

4. 删除JSON字段值

5. 修改JSON字段值

6.  特殊操作函数

1. 简介

JSON数据是我们在开发过程中几位常用的一种数据存储方式，主要以KV键值对的形式存储数据，在 MySQL5.7 之后，提供了JSON字段，在 MySQL8之后得到了很好的优化，下面就是JSON格式数据与的例子，以KV键值对的形式存储数据，每个键值对之间使用 "，" 隔开即可。

{
	"CPU": "Kirin 980",
	"system": "android",
	"storage": "512G"
}

我们先准备好一张 product 商品表，字段类型如下图所示，方便一会操作展示

然后在表中存放一些数据，如下图，数据均为编造，仅为演示 

建表SQL和数据SQL如下，想动手尝试小伙伴们可自行CV

DROP 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;

2. 查询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'   （这种方法查询出来的结果不带双引号）

使用方式如下，使用两个大于号，查询出来的结果中没有双引号

3. 添加JSON字段值

JSON_INSERT(JSON字段，'$.要插入的Key'，"要插入的值"... 可以插入多个) 插入一条JSON数据

# 向product表中details字段中添加color1和color2两个键值对
SELECT JSON_INSERT(details, '$.color1',"black",'$.color2',"red")
FROM product;

 运行SQL，就可以发现 details 字段中已经有了我们刚刚插入的color1和color2

4. 删除JSON字段值

JSON_REMOVE(JSON字段值，'$.要删除的Key')

UPDATE product
SET details = JSON_REMOVE(details, '$.color1','$.color2');

再次查询，就会发现color1和color2字段已经被删除 

5. 修改JSON字段值

JSON_REPLACE(JSON字段值，)

# 更新id为1的数据中JSON字段CPU的值为Kirin9000
UPDATE product
SET details = JSON_REPLACE(details, '$.CPU', "Kirin9000")
WHERE id = 1;

执行SQL更新操作 

查询我们更新的数据，可以看到CPU的值已经被更新 

6.  特殊操作函数

（1）JSON_KEYS(要查询的JSON字段值) 返回数据表中所有的JSON字段的 key

# 查询 product 表中所有的JSON字段的 key 
SELECT JSON_KEYS(details) FROM product;

（2）JSON_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;

查询数据验证是否执行成功，如下所示