mysql模糊查询的几种方式

like

-- 没有用到索引
select * from t_user where user_name like '%test%';

-- 用到索引但是不通用
select * from t_user where user_name like 'test%';

locate

-- LOCATE('substr',str,pos)
-- pos不填:返回substr在str中第一次出现的位置，如果不存在，返回值为0
-- pos存在:返回pos之后第一次出现的位置
select * from t_user where locate('test',user_name)>0;

position

-- 同locate
select * from t_user where position('test' in user_name);

instr

-- 查找一个字符串在另一个字符串中首次出现的位置
select * from t_user where instr(user_name,'test')>0;

find_in_set

FIND_IN_SET(str,strList)

• str 要查询的字符串
• strList 字段名，参数以 , 分隔，如 (1,2,6,8)

-- 测试及结果
mysql> select find_in_set('1','1,2,3,4,5,6');
+--------------------------------+
| find_in_set('1','1,2,3,4,5,6') |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set

mysql> select find_in_set('2','1,2,3,4,5,6');
+--------------------------------+
| find_in_set('2','1,2,3,4,5,6') |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set

mysql> select find_in_set('','1,2,3,4,5,6');
+-------------------------------+
| find_in_set('','1,2,3,4,5,6') |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set

mysql> select find_in_set('2','');
+---------------------+
| find_in_set('2','') |
+---------------------+
|                   0 |
+---------------------+
1 row in set

mysql> select find_in_set('7','1,2,3,4,5,6');
+--------------------------------+
| find_in_set('7','1,2,3,4,5,6') |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set

mysql> select find_in_set(null,'1,2,3,4,5,6');
+---------------------------------+
| find_in_set(null,'1,2,3,4,5,6') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set

mysql> select find_in_set('7',null);
+-----------------------+
| find_in_set('7',null) |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set