MySQL5.7 JSON类型使用详解

MySQL5.7引入了JSON类型，可以用于存储、查询和处理JSON格式的数据。下面是MySQL5.7 JSON类型使用的详解：

创建表和JSON列

在创建表时，可以为表中的一列指定JSON类型：

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入JSON数据

插入JSON数据可以直接使用JSON格式的值：

INSERT INTO `person`(`info`) VALUES
('{"name": "张三", "age": 18, "gender": "男"}');

查询JSON数据

查询JSON数据可以使用MySQL5.7提供的一些内置函数：

SELECT `info`->'$.name' AS `name`, `info`->'$.age' AS `age`, `info`->'$.gender' AS `gender`
FROM `person`
WHERE `id` = 1;

该语句将查询id为1的person记录中的name、age和gender字段，并使用->操作符提取JSON值。

更新JSON数据

更新JSON数据可以使用MySQL5.7提供的JSON_SET和JSON_REPLACE函数：

UPDATE `person`
SET `info` = JSON_SET(`info`, '$.age', 20)
WHERE `id` = 1;

该语句将更新id为1的person记录中的age字段为20。

删除JSON数据

删除JSON数据可以使用MySQL5.7提供的JSON_REMOVE函数：

UPDATE `person`
SET `info` = JSON_REMOVE(`info`, '$.gender')
WHERE `id` = 1;

该语句将删除id为1的person记录中的gender字段。

示例说明

假设我们有一张存储公司员工信息的表employee，其中有一列info存储员工的个人信息，包括姓名、年龄、籍贯等。我们可以使用JSON类型来存储这些信息，例如：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `employee`(`info`) VALUES
('{"name": "张三", "age": 28, "hometown": "北京"}'),
('{"name": "李四", "age": 35, "hometown": "上海"}'),
('{"name": "王五", "age": 24, "hometown": "广州"}');

假设我们现在需要查询年龄在30岁以下的员工姓名和籍贯，可以使用以下语句：

SELECT `info`->'$.name' AS `name`, `info`->'$.hometown' AS `hometown`
FROM `employee`
WHERE `info`->'$.age' < 30;

该语句将查询出名字为张三和王五的员工，并显示其姓名和籍贯。

假设我们现在需要更新李四的年龄为38岁，可以使用以下语句：

UPDATE `employee`
SET `info` = JSON_SET(`info`, '$.age', 38)
WHERE `info`->'$.name' = '李四';

假设我们现在需要删除王五的籍贯，可以使用以下语句：

UPDATE `employee`
SET `info` = JSON_REMOVE(`info`, '$.hometown')
WHERE `info`->'$.name' = '王五';

以上示例说明了如何使用MySQL5.7的JSON类型来存储、查询、更新和删除JSON格式的数据。