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