解读Oracle中代替like进行模糊查询的方法instr(更高效)

目录

• 一、简介
• 二、使用说明
• 对应参数描述
• 我们以一些示例讲解使用方法
• 三、instr()与like比较
• instr函数也有三种情况
• 下面通过一个示例说明like 与 instr()的使用比较
• 四、效率对比
• 五、总结

一、简介

相信大家都使用过like进行模糊匹配查询，在oracle中，instr()方法可以用来代替like进行模糊查询，大数据量的时候效率更高。

本文将对instr()的基本使用方法进行详解以及通过示例讲解与like的效率对比。

二、使用说明

instr(sourceString,destString,start,appearPosition)     

对应参数描述

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'），返回目标字符串在源字符串中的位置。

后面两个参数可要可不要。

我们以一些示例讲解使用方法

【a】从开头开始查找第一个‘h’出现的位置

--从开头开始查找第一个‘h"出现的位置select instr("zhangsan", "h") as idx from dual; --2

查询结果：

【b】从开头开始查找‘an’在字符串中的位置

--从开头开始查找‘an"在字符串中的位置select instr("zhangsan","an") idx from dual; --3

查询结果：

【c】从第一个位置开始查找，返回第二次出现‘a’的位置

--从第一个位置开始查找，返回第二次出现‘a"的位置select instr("zhangsan","a",1,"2") idx from dual; --7

查询结果：

【d】从倒数第一个位置开始，查找第一次出现‘a’的位置

--从倒数第一个位置开始，查找第一次出现‘a"的位置select instr("zhangsan","a",-1,1) idx from dual;  --7

查询结果：

【e】从倒数第一个位置开始，返回第二次出现‘a’的位置

--从倒数第一个位置开始，返回第二次出现‘a"的位置select instr("zhangsan","a",-1,2) idx from dual;   --3

查询结果：

三、instr()与like比较

instr函数也有三种情况

• a. instr(字段,'关键字') > 0 相当于 字段like '%关键字%': 表示在字符串中包含‘关键字’
• b. instr(字段,'关键字') = 1 相当于 字段like '关键字%' 表示以‘关键字’开头的字符串
• c. instr(字段,'关键字') = 0 相当于 字段not like '%关键字%' 表示在字符串中不包含‘关键字’

下面通过一个示例说明like 与 instr()的使用比较

【a】使用like进行模糊查询

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all select * from temp5) res where res.name like "%zhang%"

查询字符串中包含‘zhang’的结果：

【b】使用instr()进行模糊查询

(1) 查询字符串中包含‘zhang’的结果：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all  select * from temp5) res where instr(res.name,"zhang") > 0;

(2) 查询字符串中不包含‘zhang’的结果：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all select * from temp5) res where instr(res.name,"zhang") = 0;

(3) 查询以‘zhang’开头的字符串：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "sizhangsan" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all  select * from temp5) res where instr(res.name,"zhang") = 1;

(4)instr与like特殊用法

select id, name from users where instr("a, b", id) > 0;--等价于select id, name  from users where id = a    or id = b;--等价于select id, name from users where id in (a, b);

四、效率对比

【a】使用plsql创建一张十万条数据测试数据表，同时为需要进行模糊查询的列增加索引

--创建10万条测试数据create table test_instr_like as select rownum as id,"zhangsan" as name from dualconnect by level <= 100000; --name列建立索引create index idx_tb_name on test_instr_like(name);

【b】使用like进行模糊查询

select * from TEST_INSTR_LIKE t where t.name like "%zhang%"

总耗时： 60秒

【c】使用instr进行模糊查询

select * from TEST_INSTR_LIKE t where instr(t.name, "zhang") > 0;

总耗时：50秒

由图可见，instr查询的速度确实比like快一些，但是，看执行计划的话，instr却比like耗时一点。如下图：

五、总结

以上是对instr基本使用方法的讲解以及通过示例对比了like与instr的效率，在进行模糊查询的时候，能用instr的话就尽量用instr,毕竟数据量大的时候还是有一点优势的，本文是笔者对like以及instr的一些总结和见解，仅供大家学习参考，也希望大家多多支持。