解读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 all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union 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 all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union 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 all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union 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 all
select * from temp2
union all
select * from temp3
union all
select * from temp4
union 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 dual
connect 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的一些总结和见解，仅供大家学习参考，也希望大家多多支持。