解读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的一些总结和见解,仅供大家学习参考,也希望大家多多支持我们。

时间: 2022-11-17

Oracle中Like与Instr模糊查询性能大比拼

instr(title,'手册')>0 相当于 title like '%手册%' instr(title,'手册')=1 相当于 title like '手册%' instr(title,'手册')=0 相当于 title not like '%手册%' t表中将近有1100万数据,很多时候,我们要进行字符串匹配,在SQL语句中,我们通常使用like来达到我们搜索的目标.但经过实际测试发现,like的效率与instr函数差别相当大.下面是一些测试结果: SQL> set timing on

Oracle中的instr()函数应用及使用详解

1.instr()函数的格式 (俗称:字符查找函数) 格式一:instr( string1, string2 ) // instr(源字符串, 目标字符串) 格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号) 解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检

Oracle 模糊查询及like用法

目录 Oracle 模糊查询like用法 一.where子句中使用like关键字 1._ 2.[] 3.[^] 二.在Oracle中提供了instr(strSource,strTarget)函数 Oracle 模糊查询like用法 一.where子句中使用like关键字 我们可以在where子句中使用like关键字来达到Oracle模糊查询的效果:在Where子句中,可以对datetime.char.varchar字段类型的列用Like关键字配合通配符来实现模糊查询, 以下是可使用的通配符: %

详解Spring Data Jpa 模糊查询的正确用法

模糊查询 Spring Data Jpa的使用可以减少开发者对sql语句的编写,甚至完全不需要编写sql语句.但是,开发过程中总会遇到各种复杂的场景以及大大小小的坑. 今天项目中某个功能模块需要用到模糊查询.原生sql中模糊查询关键字'Like',而Spring Data Jpa的Repository接口中恰恰也有实体字段对应的Like.但是,如果直接使用它,那么恭喜你,你幸运地掉坑了. Spring Data Jpa 模糊查询正确用法 首先,我们先创建一个实体用来存储我们的数据 /** * 实

MyBatis中模糊查询使用CONCAT(&apos;%&apos;,#{str},&apos;%&apos;)出错的解决

目录 模糊查询使用CONCAT('%',#{str},'%')出错 原因及解决 MyBatis like模糊查询,CONCAT函数用法 以MySQL为例 模糊查询使用CONCAT('%',#{str},'%')出错 原因及解决 经过我一套乱七八糟毫无思路地查找后,发现不是Mybatis的原因,原来是SQL server不支持CONCAT函数,直接用加号连接就好 MyBatis like模糊查询,CONCAT函数用法 以MySQL为例 <select id="getByPage"

Mybatis 中 Oracle 的拼接模糊查询及用法详解

一.结论 这里先给大家看一下结论 Oracle 中,拼接模糊查询的正确写法 SELECT A.USER_ID, A.USER_NAME FROM USER A AND A.USER_NAME like concat(concat('%','w'),'%') 或者 AND A.USER_NAME like '%' || 'w' || '%' Mybatis 中,拼接模糊查询的正确写法 <select id="selectByName" resultMap="BaseRes

oracle基本查询用法入门示例

本文实例讲述了oracle基本查询用法.分享给大家供大家参考,具体如下: 一.基本select语句 SELECT *|{[DISTINCT] column|expression [alias], ...} FROM table; 例如: --查询所有数据 select * from emp; --查询指定列数据 select empno,ename,sal from emp; --算数运算符(+ - * /) select ename,sal,sal+30 from emp; --使用括号 se

mybatis模糊查询之bind标签和concat函数用法详解

1.二种方式都可以用来模糊查询,都能预防 SQL 注入.但是在更换数据库情况下,bind标签通用. <if test=" userName != null and userName !=""> and userName like concat('%' ,#{userName},'%') </if> 2.使用concat函数连接字符串,在mysql中这个函数支持多个参数,但是在oracle中这个函数只支持2个参数,由于不同数据库之间的语法差异,更换数据库

oracle跨库查询dblink的用法实例详解

本文实例讲述了oracle跨库查询dblink的用法.分享给大家供大家参考,具体如下: 1.创建之前的工作 在创建dblink之前,首先要查看用户是否有相应的权限.针对特定的用户,使用 sqlplus user/pwd登录后,执行如下语句: 复制代码 代码如下: select * from user_sys_privs t where t.privilege like upper('%link%'); 在sys用户下,显示结果为: SYS CREATE DATABASE LINK NO SYS

mysql中模糊查询的四种用法介绍

下面介绍mysql中模糊查询的四种用法: 1,%:表示任意0个或多个字符.可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示. 比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' 将会把u_name为"张三","张猫三"."三脚猫","唐三藏"等等有"三"的记录全找出来. 另外,如果需要找出u_name中既有"三"又有

oracle sql语言模糊查询--通配符like的使用教程详解

oracle在Where子句中,可以对datetime.char.varchar字段类型的列用Like子句配合通配符选取那些"很像..."的数据记录,以下是可使用的通配符: %   零或者多个字符 _    单一任何字符(下划线) \     特殊字符 oracle10g以上支持正则表达式的函数主要有下面四个: 1,REGEXP_LIKE :与LIKE的功能相似 2,REGEXP_INSTR :与INSTR的功能相似 3,REGEXP_SUBSTR :与SUBSTR的功能相似 4,RE