腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

解讀Oracle中代替like進(jìn)行模糊查詢的方法instr(更高效)

更新時(shí)間：2022年11月18日 16:25:29 作者：每天都要進(jìn)步一點(diǎn)點(diǎn)

這篇文章主要介紹了解讀Oracle中代替like進(jìn)行模糊查詢的方法instr(更高效)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

一、簡(jiǎn)介

相信大家都使用過(guò)like進(jìn)行模糊匹配查詢，在oracle中，instr()方法可以用來(lái)代替like進(jìn)行模糊查詢，大數(shù)據(jù)量的時(shí)候效率更高。

本文將對(duì)instr()的基本使用方法進(jìn)行詳解以及通過(guò)示例講解與like的效率對(duì)比。

二、使用說(shuō)明

instr(sourceString,destString,start,appearPosition) ? ??

對(duì)應(yīng)參數(shù)描述

instr（'源字符串' , '目標(biāo)字符串' ,'開始位置','第幾次出現(xiàn)'），返回目標(biāo)字符串在源字符串中的位置。

后面兩個(gè)參數(shù)可要可不要。

我們以一些示例講解使用方法

【a】從開頭開始查找第一個(gè)‘h’出現(xiàn)的位置

--從開頭開始查找第一個(gè)‘h'出現(xiàn)的位置
select instr('zhangsan', 'h') as idx from dual; --2

查詢結(jié)果：

【b】從開頭開始查找‘an’在字符串中的位置

--從開頭開始查找‘a(chǎn)n'在字符串中的位置
select instr('zhangsan','an') idx from dual; --3

查詢結(jié)果：

【c】從第一個(gè)位置開始查找，返回第二次出現(xiàn)‘a’的位置

--從第一個(gè)位置開始查找，返回第二次出現(xiàn)‘a(chǎn)'的位置
select instr('zhangsan','a',1,'2') idx from dual; --7

查詢結(jié)果：

【d】從倒數(shù)第一個(gè)位置開始，查找第一次出現(xiàn)‘a’的位置

--從倒數(shù)第一個(gè)位置開始，查找第一次出現(xiàn)‘a(chǎn)'的位置
select instr('zhangsan','a',-1,1) idx from dual;  --7

查詢結(jié)果：

【e】從倒數(shù)第一個(gè)位置開始，返回第二次出現(xiàn)‘a’的位置

--從倒數(shù)第一個(gè)位置開始，返回第二次出現(xiàn)‘a(chǎn)'的位置
select instr('zhangsan','a',-1,2) idx from dual;   --3

查詢結(jié)果：

三、instr()與like比較

instr函數(shù)也有三種情況

a. instr(字段,'關(guān)鍵字') > 0 相當(dāng)于字段like '%關(guān)鍵字%': 表示在字符串中包含‘關(guān)鍵字’
b. instr(字段,'關(guān)鍵字') = 1 相當(dāng)于字段like '關(guān)鍵字%' 表示以‘關(guān)鍵字’開頭的字符串
c. instr(字段,'關(guān)鍵字') = 0 相當(dāng)于字段not like '%關(guān)鍵字%' 表示在字符串中不包含‘關(guān)鍵字’

下面通過(guò)一個(gè)示例說(shuō)明like 與 instr()的使用比較

【a】使用like進(jìn)行模糊查詢

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’的結(jié)果：

【b】使用instr()進(jìn)行模糊查詢

(1) 查詢字符串中包含‘zhang’的結(jié)果：

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’的結(jié)果：

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;
--等價(jià)于
select id, name
  from users
 where id = a
    or id = b;
--等價(jià)于
select id, name from users where id in (a, b);

四、效率對(duì)比

【a】使用plsql創(chuàng)建一張十萬(wàn)條數(shù)據(jù)測(cè)試數(shù)據(jù)表，同時(shí)為需要進(jìn)行模糊查詢的列增加索引

--創(chuàng)建10萬(wàn)條測(cè)試數(shù)據(jù)
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進(jìn)行模糊查詢

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

總耗時(shí)： 60秒

【c】使用instr進(jìn)行模糊查詢

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

總耗時(shí)：50秒

由圖可見(jiàn)，instr查詢的速度確實(shí)比like快一些，但是，看執(zhí)行計(jì)劃的話，instr卻比like耗時(shí)一點(diǎn)。如下圖：

五、總結(jié)

以上是對(duì)instr基本使用方法的講解以及通過(guò)示例對(duì)比了like與instr的效率，在進(jìn)行模糊查詢的時(shí)候，能用instr的話就盡量用instr,畢竟數(shù)據(jù)量大的時(shí)候還是有一點(diǎn)優(yōu)勢(shì)的，本文是筆者對(duì)like以及instr的一些總結(jié)和見(jiàn)解，僅供大家學(xué)習(xí)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: