面试--mysql的模糊查询优化、like、locate、position、instr、find_in_set

本文主要是介绍面试--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 关键字指定正则表达式的字符匹配模式

正则表达式常用的字符匹配列表

• 匹配前面的字符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 |
+------+------+--------+---------+

这篇关于面试--mysql的模糊查询优化、like、locate、position、instr、find_in_set的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！