like模糊查询，及其优化(查找数组)_mysql模糊查询like优化

目前sql有四种匹配模糊查询模式 % _ [] [^]

% 任意长度

表示任意0-n个字符，可匹配任意类型和长度

// sql
select * from table where name like '%三%';
// mybatis
.... where name like concat('%',#{name},'%');
1
2
3
4

_ 单个

表示任意单个字符----用来限长的

// sql
select * from table where name like '_三_';
// mybatis
.... where name like concat('_',#{name},'_');
1
2
3
4

[] 单个

表示括号内所有列之内的一个

// sql
select * from table where name like '[张李王]三';
// mybatis
.... where name like '[张李王]'#{name};
1
2
3
4

##[^] 单个
表示不在括号内的字符

// sql
select * from table where name like '[^张李王]三';
// mybatis
.... where name like '[^张李王]'#{name};
1
2
3
4

模糊查询优化

对于用like查询会有性能问题，且做模糊会让索引失效，从而全表扫描
优化方法有四种

INSTR(‘查询字段’,‘查询条件’)

INSTR(str,substr)
instr(title,‘手册’)>0 相当于 title like ‘%手册%’
instr(title,‘手册’)=1 相当于 title like ‘手册%’
instr(title,‘手册’)=0 相当于 title not like ‘%手册%’

select * from table where INSTR(name, #{keyWords} )
1

LOCATE(‘查询条件’, ‘查询字段’)

select * from table where LOCATE(#{keyWords} , name )
1

可以用or来多次查询不同的数据条件；
可以查询这样([4,3])的数据

position( ‘查询条件’ in ‘查询字段’)

POSITION(substr IN str)

select * from table where position(#{keyWords}  in name )
1

FIND_IN_SET(‘查询字段’,‘查询条件’)

FIND_IN_SET(str,strlist)

适用于查询带逗号的字符串

select * from table where FIND_IN_SET(#{keyWords}, name )
1

多个字段进行模糊搜索

INSTR(CONCAT(mui.nickname,yui.nickname),#{query}) > 0

注意

• mybatis中大于号和小于号都用 > < 代替
• 使用了模糊查询，就得用id动态sql来排除null的情况
• sql中用is表示字段未null的值