Oracle VARCHAR 列上的数字比较如何工作？

How does numeric comparison on Oracle VARCHAR column work?

提问by James Jithin

I have a table where two columns are of type VARCHAR2(3BYTE) and VARCHAR2(32BYTE). When I do a select query (where col1=10and where col1='10') or (where col2=70001or col2='70001') the number of records fetched are the same in each set of where clauses. How does this happen? How does Oracle treat string literals and numeric constants and compare to the data despite column data-type?

我有一个表，其中两列的类型为 VARCHAR2(3BYTE) 和 VARCHAR2(32BYTE)。当我执行选择查询（where col1=10和where col1='10'）或（where col2=70001或col2='70001'）时，在每组 where 子句中获取的记录数相同。这是怎么发生的？尽管列数据类型不同，Oracle 如何处理字符串文字和数字常量并与数据进行比较？

But this does not work for a column of type VARCHAR2(128BYTE). The query needed to be where col3='55555555001'to work and where col3=55555555001throws ORA-01722 error.

但这对 VARCHAR2(128BYTE) 类型的列不起作用。查询需要where col3='55555555001'工作并where col3=55555555001抛出 ORA-01722 错误。

回答by Alex Poole

As noted in the SQL Language Reference:

如SQL 语言参考中所述：

• During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
• ...
• When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

• 在 SELECT FROM 操作期间，Oracle 将列中的数据转换为目标变量的类型。
• ...
• 将字符值与数值进行比较时，Oracle 会将字符数据转换为数值。

Implicit conversion is performed on the table column when the types don't match. This can be seen by tracing in SQL*Plus, with some dummy data.

当类型不匹配时，对表列执行隐式转换。这可以通过在 SQL*Plus 中使用一些虚拟数据进行跟踪来看到。

create table t42 (foo varchar2(3 byte));
insert into t42 (foo) values ('10');
insert into t42 (foo) values ('2A');
set autotrace on explain

This works:

这有效：

select * from t42 where foo = '10';

FOO
---
10

Execution Plan
----------------------------------------------------------
Plan hash value: 3843907281

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

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

   1 - filter("FOO"='10')

Note
-----
   - dynamic sampling used for this statement (level=2)

But this errors:

但是这个错误：

select * from t42 where foo = 10;

ERROR:
ORA-01722: invalid number



Execution Plan
----------------------------------------------------------
Plan hash value: 3843907281

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

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

   1 - filter(TO_NUMBER("FOO")=10)

Note the difference in the filter; filter("FOO"='10')versus filter(TO_NUMBER("FOO")=10). In the latter case, comparing against a number, a to_number()is being performed against every row in the table the the result of that conversion is compared against the fixed value. So if any of the character values cannot be converted, you'll get an ORA-01722. The function being applied will also stop an index being used, if one is present on that column.

注意过滤器的不同；filter("FOO"='10')与filter(TO_NUMBER("FOO")=10). 在后一种情况下，与数字进行比较，对to_number()表中的每一行执行a ，将该转换的结果与固定值进行比较。因此，如果任何字符值无法转换，您将收到 ORA-01722。如果该列上存在索引，则正在应用的函数还将停止正在使用的索引。

Where it gets interesting is if you have more than one filter. Oracle may evaluate them in different orders at different times, so you might not always see the ORA-01722, and it'll pop up sometimes. Say you had where foo = 10 and bar = 'X'. If Oracle thought it could filter out the non-Xvalues first, it would only apply the to_number()to what's left, and that smaller sample might not have non-numeric values in foo. But if you has and bar = 'Y', the non-Yvalues might include non-numerics, orOracle might filter on foofirst, depending on how selective it thinks the values are.

有趣的是，如果您有多个过滤器。Oracle 可能会在不同的时间以不同的顺序评估它们，因此您可能不会总是看到 ORA-01722，它有时会弹出。说你有where foo = 10 and bar = 'X'。如果 Oracle 认为它可以先过滤掉非X数值，它只会将 应用于to_number()剩下的，并且较小的样本在 中可能没有非数字值foo。但是，如果您有and bar = 'Y'，则非Y值可能包括非数字，或者Oracle 可能会foo首先过滤，具体取决于它认为这些值的选择性。

The moral is to never store numeric information as a character type.

道德是永远不要将数字信息存储为字符类型。

---

---

I was looking for an AskTom reference to back up the moral, and the first one I looked atconveniently refers to the effect of "a change in the order of a predicate" as well as saying "don't store numbers in varchar2's".

我正在寻找一个 AskTom 引用来支持道德，我看到的第一个引用方便地指的是“谓词顺序的更改”以及“不要在 varchar2 中存储数字”的效果。

回答by Codo

If a numeric column or value and a character column are involved, Oracle converts the character column values to numbers and then converts numbers with numbers. It's as if you had written:

如果涉及数字列或值和字符列，则Oracle先将字符列值转换为数字，然后再将数字转换为数字。就好像你写过：

where to_number(col3) = 55555555001

That's why you get an ORA-01722: invalid numbererror if a single row contains a string (n col3) that cannot be converted to a numeric value.

这就是ORA-01722: invalid number如果单行包含无法转换为数字值的字符串 (n col3) 时会出现错误的原因。

For that reason we have the IS_NUMBERfunction in our Oracle database that doesn't cause an error but returns NULL for values that cannot be converted to numbers. Then you can safely write:

出于这个原因IS_NUMBER，我们的 Oracle 数据库中有一个函数，它不会导致错误，但会为无法转换为数字的值返回 NULL。然后你可以安全地写：

where is_number(col3) = 55555555001

The function is defined as:

该函数定义为：

CREATE OR REPLACE FUNCTION is_number (p_str IN VARCHAR2)
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number(p_str);
  RETURN l_num;

EXCEPTION
  WHEN others THEN
    RETURN NULL;
END is_number;