项目场景

Oracle表字段为VARCHAR2时，where条件出现NUMBER的匹配，可能会出现该错误。

问题描述

test表结构如下

CREATE TABLE “lzy”.“TEST”
( “NAME” VARCHAR2(255)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “lzy”

数据

已知字段NAME为VARCHAR2类型

SELECT * FROM TEST WHERE NAME = 1

数据只有1时，不报错；
数据包含非数字，使用上述sql 查询，报错 ORA-01722：invalid number

结论

当表中数据全部为 数字，不会报错；
当表中数据包含 非数字，会报错。

原因分析

带着上面的结论，我们查找原因

查看Oracle的sql执行计划

explain plan for SELECT * FROM TEST WHERE NAME = 1;select * from TABLE(dbms_xplan.display);

如图，发现了端倪

oracle会将字段通过to_number函数强转为NUMBER类型，再进行比对过滤

问题就出现在to_number函数这里，非数字强转NUMBER会报错ORA-01722：invalid number

解决方案

方案一（推荐）

老老实实比对正确的字段类型

SELECT * FROM TEST WHERE NAME = '1';

字段是什么类型就比对什么类型

方案二（不推荐）

当表中该字段的数据全部为数字类型的值，强转不会报错，也就可以继续使用该sql查询
但不推荐使用，原因有两点
1.容错性低。一旦数据被污染，线上一直报错，等着扣工资吧
2.效率低。Oracle会先将该字段下的所有数据转成数字类型，进行比对过滤，会损失一部分性能