MySQL JSON字段的多属性条件查询|极客教程

MySQL JSON字段的多属性条件查询

在MySQL 5.7版本以后，引入了对JSON类型的支持，可以将JSON数据存储在列中，并进行相应的查询操作。JSON字段是一种非常灵活的数据存储方式，能够存储多种类型的数据，并且支持索引。在实际应用中，有时候需要对JSON字段中的多个属性进行条件查询，本文将详细介绍在MySQL中如何进行JSON字段的多属性条件查询。

创建表并插入数据

首先，我们创建一个示例表user，包含一个名为info的JSON字段，用于存储用户的信息。

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

然后插入一些示例数据：

INSERT INTO `user` (`info`) VALUES
('{"name": "Alice", "age": 25, "gender": "female"}'),
('{"name": "Bob", "age": 30, "gender": "male"}'),
('{"name": "Charlie", "age": 28, "gender": "male"}'),
('{"name": "David", "age": 35, "gender": "male"}');

单属性条件查询

首先，我们先介绍如何进行JSON字段的单属性条件查询。假设我们需要查询年龄为25岁的用户，可以使用MySQL的->>操作符：

SELECT * FROM `user`
WHERE `info` ->> '$.age' = '25';

上述查询会返回姓名为Alice，年龄为25岁的用户信息。

多属性条件查询

接下来，我们介绍如何进行JSON字段的多属性条件查询。假设我们需要查询年龄在25岁到30岁之间，并且性别为女性的用户，可以通过MySQL的->>和AND操作符实现：

SELECT * FROM `user`
WHERE `info` ->> ' $.age' >= '25' AND `info` ->> '$ .age' <= '30'
AND `info` ->> '$.gender' = 'female';

上述查询会返回姓名为Alice，年龄为25岁，性别为女性的用户信息。

嵌套属性条件查询

有时候，JSON字段中的属性可能是一个嵌套的JSON对象，我们也可以针对嵌套属性进行条件查询。假设我们在info字段中添加address属性，用于存储用户的地址信息，我们可以查询住在北京的女性用户：

UPDATE `user`
SET `info` = JSON_SET(`info`, ' $.address', 'Beijing') WHERE `info` ->> '$ .name' = 'Alice';

SELECT * FROM `user`
WHERE `info` ->> ' $.address' = 'Beijing' AND `info` ->> '$ .gender' = 'female';

上述查询会返回姓名为Alice，性别为女性，地址为北京的用户信息。

使用`JSON_EXTRACT`函数

除了使用->>操作符进行条件查询，还可以使用JSON_EXTRACT函数进行条件查询。JSON_EXTRACT函数能够提取JSON字段中的指定属性值，更加灵活。

SELECT * FROM `user`
WHERE JSON_EXTRACT(`info`, ' $.age') >= '25' AND JSON_EXTRACT(`info`, '$ .age') <= '30'
AND JSON_EXTRACT(`info`, '$.gender') = 'male';

上述查询与之前的多属性条件查询效果相同，都是查询年龄在25岁到30岁之间，并且性别为男性的用户。