By

文章目录

最近遇到一个技术问题，需要根据分类过滤数据，这个分类是有层级关系的，也就是当输入一个分类ID时，需要返回分类本身的数据，还有子分类，孙分类的数据。我就是想到了用JSON数组来实现。因为数据库里有记录某篇文章的所有分类,包含所有父级分类。

在网上查一下怎么在MYSQL里查找JSON数据组是否包含某个值，查了半天，也没找到有用的信息。后来还是到MySQL官网找到的答案。就是 MEMBER OF(json_array).

1
2
3
4
5
6
mysql> select 1 member of ('[1]');
+---------------------+
 1 member of ('[1]') 
+---------------------+
 1                   
+---------------------+

member of 匹配则返回1，不匹配则返回0。接下来，我们创建一个测试表，及一些测试数据来试一下这个功能。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `article` (
  `id` int NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `catalogs` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `test`.`article`(`id`, `title`, `catalogs`) VALUES (1, 't1', '[1]');
INSERT INTO `test`.`article`(`id`, `title`, `catalogs`) VALUES (2, 't2', '[1,2]');
INSERT INTO `test`.`article`(`id`, `title`, `catalogs`) VALUES (3, 't3', '[11]');
INSERT INTO `test`.`article`(`id`, `title`, `catalogs`) VALUES (4, 't4', NULL);

有的分类，是有层级的。比如一个地址：广东省，深圳市，宝安区。有时候我们希望查询的是广东省的所有数据。所以会希望能过一个ID能查询出直接属于这个分类的，以及它的子分类的内容。像上面的第2条记录[1,2]表示的就是这一类的情况。

查询分类1对应的记录：

1
2
3
4
5
6
7
8
mysql> select * from article where 1 member of(catalogs);
+----+-------+----------+
 id  title  catalogs 
+----+-------+----------+
  1  t1     [1]      
  2  t2     [1,2]    
+----+-------+----------+
2 rows in set (0.00 sec)

我们看到 [1] 和 [1,2] 都是能正确返回的。[11]则能正确的被过滤掉。

值的注意的是，如果你的内容是字符串类的表达的，要加上’’去匹配。注意看下面的区别：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select 1 member of ('["1"]');
+-----------------------+
 1 member of ('["1"]') 
+-----------------------+
                     0 
+-----------------------+
1 row in set (0.00 sec)

mysql> select "1" member of ('["1"]');
+-------------------------+
 "1" member of ('["1"]') 
+-------------------------+
                       1 
+-------------------------+
1 row in set (0.00 sec)

所以不能用数字1去匹配字符串”1”。

注：MEMBER OF()是MySQL 8.0.17.版本才加入的功能，所以如果上面的SQL报错，注意查看mysql的版本。

如果是在mysql 5.7 版本执行上面的SQL，就会报错

1
2
3
4
5
6
7
8
mysql> select 1 member of ('[1]');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of ('[1]')' at line 1
mysql> select version();
+-----------+
 version() 
+-----------+
 5.7.30    
+-----------+

参考： https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of

文章目录