Oracle 在where子句中转换数据类型

ASKTOM 2019-09-24

436

问题描述

嗨，汤姆，

我的问题是关于查询何时在where子句中没有正确的数据类型

示例:

-- 创建表格

CREATE TABLE mytable (
mynumber varchar2(20), primary key(mynumber));复制

-插入一些行

insert into mytable (mynumber)
    select rownum from dual
    connect by rownum <100
;
 
commit;复制

-- 数据类型正确的查询

select * from mytable where mynumber = '10';复制

-- 1.解释计划
-注意使用具有唯一扫描的索引
-- 2.执行查询
- > 10

-- 数据类型错误的查询

select * from mytable where mynumber = 10;复制

-- 1.解释计划
-通知 _number和索引的全面扫描
-- 2.执行查询
- > 10

-插入具有无法转换为数字的值的新行

insert into mytable (mynumber) values ('xyz');
commit;复制

-- 数据类型正确的查询

select * from mytable where mynumber = '10';复制

-- 1.解释计划
-通知索引的使用
-- 2.执行查询
- > 10

-- 数据类型错误的查询

select * from mytable where mynumber = 10;复制

-- 1.解释计划
-通知 _number和索引的全面扫描
-- 2.执行查询
- > ORA-01722: 无效编号

我能够理解发生了什么，但我的问题是为什么Oracle选择将所有行 (索引) 转换为数字，而不是转换where子句中的值。我希望对此有一个很好的解释，但我似乎无法弄清楚。

BR
延斯

专家解答

考虑以下值:

01
1
1.0000

从数字上讲，它们都是相同的值: 1。

但是，如果您将它们作为文本进行比较，它们都是不同的!

因此，当将数值与字符串进行比较时，数据库将对文本进行编号。这样可以确保您获得具有相同数字值的所有行:

create table t (
  c1 varchar2(10)
);

insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );

select * from t
where  to_number ( c1 ) = 1;

C1      
01       
1        
1.000    

select * from t
where  c1 = to_char ( 1 );

C1   
1     复制

转换将发生在哪个表达式返回字符值。翻转数据类型，你会得到所有的行:

drop table t cascade constraints purge;
create table t (
  c1 number
);

insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );

select * from t
where  c1 = '1.0';

C1   
    1 
    1 
    1 复制