【MySQL】find_in_set 函数
xixuefeng
MySQL, MySQL-DEV
2018-03-03 23:49:46
1,245 次浏览
find_in_set
【MySQL】find_in_set 函数已关闭评论
在数据库设计中，经常会遇到这种情况，某一个字段的值是用逗号分隔的。常见的比如用户权限，在赋权时把权限、角色用逗号隔开。
首先是绝对不赞成这么搞的，起码连第一范式都没有满足，且后续在针对这个字段进行统计时性能会相当的差。并且在检索时还有可能有问题。
MySQL 5.7 做个测试
1：准备表及数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> create table t(id int(5) auto_increment,list varchar(50),primary key(id)); Query OK, 0 rows affected (0.05 sec) mysql> ## 插入测试数据 mysql> insert into t(list) values('1,2,3,4,5,'),('9,12,13,28,32,'),('1,25,33,49,56,'),('11,12,13,14,15,'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t; +----+-----------------+ | id | list | +----+-----------------+ | 1 | 1,2,3,4,5, | | 2 | 9,12,13,28,32, | | 3 | 1,25,33,49,56, | | 4 | 11,12,13,14,15, | +----+-----------------+ 4 rows in set (0.00 sec) mysql> |
2：检索t表中list字段包含2的记录
2.1：用like实现
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
## 直接like 2 我们可以看到，结果并不是我们想要的，只要包含2的都返回了 mysql> select * from t where list like '%2%'; +----+-----------------+ | id | list | +----+-----------------+ | 1 | 1,2,3,4,5, | | 2 | 9,12,13,28,32, | | 3 | 1,25,33,49,56, | | 4 | 11,12,13,14,15, | +----+-----------------+ 4 rows in set (0.00 sec) mysql> ## 2,也不是我们想要的 mysql> select * from t where list like '%2,%'; +----+-----------------+ | id | list | +----+-----------------+ | 1 | 1,2,3,4,5, | | 2 | 9,12,13,28,32, | | 4 | 11,12,13,14,15, | +----+-----------------+ 3 rows in set (0.00 sec) mysql> ## 当然，如果初始数据是前后都带逗号的，那么就没有问题了。 ## 更新一下t表，在字符串前面加上逗号 mysql> update t set list=CONCAT(',',list); Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from t; +----+------------------+ | id | list | +----+------------------+ | 1 | ,1,2,3,4,5, | | 3 | ,1,25,33,49,56, | | 4 | ,11,12,13,14,15, | | 2 | ,9,12,13,28,32, | +----+------------------+ 4 rows in set (0.00 sec) mysql> ## 再次用like检索，此时的结果就是我们想要的了 mysql> select * from t where list like '%,2,%'; +----+-------------+ | id | list | +----+-------------+ | 1 | ,1,2,3,4,5, | +----+-------------+ 1 row in set (0.00 sec) mysql> |
2.2：用find_in_set实现
|
1 2 3 4 5 6 7 8 9 10 |
## find_in_set函数就没有那么麻烦了，如下 mysql> select * from t where find_in_set(2,list); +----+------------+ | id | list | +----+------------+ | 1 | 1,2,3,4,5, | +----+------------+ 1 row in set (0.00 sec) mysql> |
3：小结
1）显然，find_in_set无论是用法还是结果都是非常贴心的；
2）find_in_set函数的使用，前提是，字符串必须是以’,’号分隔，它是通过’,’还做为分隔的位置，同时也是按照位置进行匹配；
3）在性能上，应该是差不多的，作者本人也没仔细测试过性能。但是，在准确度上，find_in_set一定是最优的；
4）最关键的，不想被骂，就别这么设计。