MySql教程

面试--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) > 0POSITION('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 |
+------+------+--------+---------+



作者:月圆星繁
链接:https://www.jianshu.com/p/8c087f6069dd
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。   转 https://www.jianshu.com/p/8c087f6069dd
这篇关于面试--mysql的模糊查询优化、like、locate、position、instr、find_in_set的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!