SQL - 字符与数值自动类型转换带来的问题

在 SQL 中发出一个带条件的查询，会自动进行字符与数值的自动类型转换。这是为了方便我们的编码，但有时候也会带来意想不到的问题。看一个刚刚碰到的问题：

 

1. 创建测试环境

 

SQL> create table yct1(id varchar2(3), name varchar2(20))
  2  /

 

表已创建。

 

SQL> insert into yct1 select 2, 'yct'||rownum from dual connect by rownum < 6;

 

已创建5行。

 

SQL> insert into yct1 select '02', 'yct'||rownum from dual connect by rownum < 6;

 

已创建5行。

 

SQL> commit;

 

提交完成。

 

SQL> select  * from yct1;

 

ID  NAME
--- --------------------
2   yct1
2   yct2
2   yct3
2   yct4
2   yct5
02  yct1
02  yct2
02  yct3
02  yct4
02  yct5

 

已选择10行。

 

2. 字符与数值的自动类型转换

 

对于字符类型的字段 yct1.id，使用数值数据和使用字符数据作为查询条件，得到的结果是不一样的：

 

-- 条件中使用数值

SQL> select * from yct1 where id = 02;

 

ID  NAME
--- --------------------
2   yct1
2   yct2
2   yct3
2   yct4
2   yct5
02  yct1
02  yct2
02  yct3
02  yct4
02  yct5

 

已选择10行。

 

-- 条件中使用字符

SQL> select * from yct1 where id = '02';

 

ID  NAME
--- --------------------
02  yct1
02  yct2
02  yct3
02  yct4
02  yct5

 

我们看一下使用数值类型查询时，这个 SQL 的执行计划：

 

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

SQL> explain plan for
  2  select * from yct1 where id = 02;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1878970141

 

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   150 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| YCT1 |    10 |   150 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 

   1 - filter(TO_NUMBER("ID")=02)

 

Note
-----
   - dynamic sampling used for this statement

 

已选择17行。

 

在过滤条件中，Oracle 发现两者类型不一致，则使用函数 to_number 自动将表中字符类型的数据转换为数值类型。所以这个 SQL 等价于：

 

select * from yct1 where to_number(id) = 2;

 

这可能跟我们预期的查询结果相差很大。

 

3. 继续测试

 

我在字段 yct1.id 中插入一个不能转换为数值的字符，再次测试一下：

 

SQL> insert into yct1 values('x02', 'yctx');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from yct1;

 

ID  NAME
--- --------------------
2   yct1
2   yct2
2   yct3
2   yct4
2   yct5
02  yct1
02  yct2
02  yct3
02  yct4
02  yct5
x02 yctx

 

已选择11行。

 

SQL> select * from yct1 where id = 02;
ERROR:
ORA-01722: 无效数字

 

 

 

未选定行

 

跟这个 SQL 的执行结果是一样的：

 

SQL> select * from yct1 where to_number(id) = 2;
ERROR:
ORA-01722: 无效数字

 

 

 

未选定行

 

4. 结论

 

虽然 Oracle 可以自动转换一些类型，但我们编码时，不要寄希望于 Oracle，而应该使用显式的类型转换，将数据类型一一对应。否则带来的问题可能很难发现，比如若字段 yct1.id 上存在索引，Oracle 强制类型转换后也会无法使用。