mysql 查找json数组是否包含某个字符串或数字

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

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

mysql> select 1 member of ('[1]');
+---------------------+
| 1 member of ('[1]') |
+---------------------+
| 1                   |
+---------------------+

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

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对应的记录：

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]则能正确的被过滤掉。

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

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，就会报错

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    |
+-----------+

select<include refid="Join_Standard_Column_List"/>,
        t1.nav_code as classification, t1.isCheck as classificationCheck, t1.summaryCheck,t1.nlp_summary summary,t1.entityCheck
        from mix_data t1
        where 1=1
        and t1.del=0
        <if test="title!=null and title!=''">
            and t1.`title` like "%"#{title}"%"
        </if>
        <if test="navCodes!=null and navCodes.size()>0">
            and
            <foreach collection="navCodes" item="navCode" open="(" separator=" or " close=")">
                #{navCode}  member of(nav_code)
            </foreach>
        </if>

注意：本文归作者所有，未经作者允许，不得转载