mysql locate不走索引_面试--mysql的模糊查询优化、like、locate、position、instr、find_in_set...
困扰很久的一个事情，一次面试的时候，在mysql的一个表中有个字段存的值是 “#床垫#白色#2*3” 类似的字段。
面试官让我匹配下 #白色 的所有数据，我当时第一反应就是通过 like 来处理，然后他说还有没有别的方法。
我想了下就说 find_in_set但是它的配置值必须要是用逗号隔开，所以应该不满足。
后面这个问题确实困扰了我，我就各种查找有效的方法，今天就总结下，MySQL的模糊查询，以后的多扩展下自己的思路，多尝试其他的方式，避免尴尬。
1) locate用法
locate(‘substr',str,pos)
SELECT LOCATE('xbar',`foobar`);
返回0
SELECT LOCATE('bar',`foobarbar`);
返回4
SELECT LOCATE('bar',`foobarbar`,5);
返回7
返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 ；
如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置；
如果 substr 在 str 中不存在，返回值为0。
SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`) > 0
keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据
2) 其他的方式
POSITION('substr' IN field)方法
INSTR(str,'substr')方法
3)Like查询优化方案
like模糊查询形如'%AAA%'和'%AAA'将不会使用索引，但是业务上不可避免可能又需 要使用到这种形式查询方式：
优化方案一：使用覆盖索引，即查询出的列只是用索引就可以获取，而无须查询表记录，这样也走了索引；
优化方案二：使用locate函数或者position函数代替like查询：
如table.field like '%AAA%'可以改为locate('AAA', table.field) > 0或POSITION('AAA' IN table.field)>0
4)使用正则表达式查询
使用 REGEXP 关键字指定正则表达式的字符匹配模式
正则表达式常用的字符匹配列表
选项
说明
例子
匹配值示例
^
匹配文本的开始字符
^b
book,big,banana
$
匹配文本结束字符
st$
test,resist
.
匹配任何单个字符
b.t
bit,bat,but
*
匹配零个或多个在它前面的字符
f*n:匹配字符n前面的0个或多个f字符的字符串
fn,fan,faan
匹配前面的字符1次或多次 ba+:匹配以 b 开头后面紧跟1个或多个a的字符串 ba,bay,bare
匹配包含指定的字符串的文本 fa:匹配包含“fa”的字符串 fan,afa.faad
[字符集合] 匹配字符集合中的任何一个字符 '[xz]':匹配 x 或者 z dizzy，zebra
[^] 匹配不在括号中的任何字符
'[^abc]':匹配任何不包含a、b、c的字符串
desk,fox
字符串{n,} 匹配前面的字符串至少n次 b{2}:匹配有2个或更多的b字符的字符串 bbb，bbbb
字符串{n,m} 匹配前面的字符串至少n次，至多m次。如果n为0，次参数为可选参数 b{2,4}:匹配至少有2个，最多有4个b字符的字符串
bb，bbb,bbbb
查询以特定字符或字符串开头的记录
字符^可以匹配以特定字符或者字符串开头的文本。
【例】在 fruits 表中，查询 f_name 字段以字母 b 开头的记录。SQL 语句如下：
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | blanana | 10.30 |
+------+------+------------+---------+
查询以特定字符或字符串结尾的记录
字符 $ 可以匹配以特定字符或者字符串结尾的文本。
【例】在 fruits 表中，查询 f_name 字段以字母 y 结尾的记录。SQL 语句如下：
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
+------+------+------------+---------+
** 代替字符串中的任意一个字符**
字符‘.’可以匹配任意一个字符。
【例】在 fruits 表中，查询 f_name 字段以包含字母 a 与 g 且两个字母之间只有一个字母的记录。SQL 语句如下
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.60 |
+------+------+--------+---------+
匹配多个字符
星号(*) 可以任意次匹配前面的字符，包括 0 次。加号(+)至少匹配前面的字符一次。
【例】在 fruits 表中，查询 f_name 字段以包含字母 b 开头，且 b 后面出现字母 a 的记录。SQL 语句如下：
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1 | 102 | banana | 10.30 |
+------+------+--------+---------+