sql中的模糊搜索like和instr

忘是亡心i 2022-09-08 14:55 167阅读 0赞

### 文章目录 ###

*   *  like
     *   *  使用like语法，索引有效吗
     *  instr函数
     *   *  instr和like对比

## like ##

### 使用like语法，索引有效吗 ###

答有效或无效都不能算对。  
有以下几种情况：  
1、`like %keyword` 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。

2、`like keyword%` 索引有效。

3、`like %keyword%` 索引失效，也无法使用反向索引。  
使用下面的函数来进行模糊查询，如果出现的位置〉0，表示包含该字符串。  
查询效率比like要高。  
如果： `table.field like ‘%AAA%’` 可以改为 `locate (‘AAA’ , table.field) > 0`

## instr函数 ##

知道instr是因为有开头匹配的半模糊搜索需求，但是觉得效率有点低，所以才想开辟新大陆。

语法：  
`instr( string1, string2, start_position,nth_appearance )`  
参数  
● string1：源字符串，要在此字符串中查找。  
●string2：要在string1中查找的字符串 。  
●start\_position：代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。  
●nth\_appearance：代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

返回值：  
从 Start 位置开始，在 String1 中寻找 String2 ，如果没有找到，则返回0。如果 String1 或 String2 为Null，则返回Null，其他情况返回 String2 在 String1 中的起始位置。

### instr和like对比 ###

`like '%飞%'` 对应`instr(username,'飞') >0`：

like示例：
    select count(1) from t_user where username like '%飞%';
    
    instr示例：
    select count(1) from t_user where instr(username,'飞') >0;

`like '飞%'` 对应 `instr(username,'飞') =1`

like示例：
    select count(1) from t_user where username like '飞%';
    
    instr示例：
    select count(1) from t_user where instr(username,'飞') =1;

开头匹配的模糊查询实测差别不大，所以并没有优化我的sql。