一种疑问：在进行Oracle数据库设计时，字符类型的字段我们应该使用什么类型，是CHAR？还是VARCHAR2？

一个声音：定长的字符串使用CHAR类型，变长字符串使用VARCHAR2类型。
这种声音的通常解释：当字符串的长度固定，我们使用CHAR类型可以提高效率；当字符串的长度变化的情况下应该使用VARCHAR2类型，这样可以节省存储空间。

您同意上面的这种声音么？

我们从原理和实验上分析一下。

从原理上讲，Oracle的所有字符串的存储格式都是相同的，因此效率上不会有太大的区别。

具体实验及相关结论。
1.创建一个测试表t，包含两个字段，第一个字段x定义为CHAR类型，第二个字段y定义为VARCHAR2类型，长度均定义为10。
sec@ora10g> create table t (x char(10), y varchar2(10));

Table created.

2.在表t中初始化一条记录。
sec@ora10g> insert into t values ('secooler','secooler');

1 row created.

3.初级查看，貌似两个字段存储的内容是一样的。
sec@ora10g> select * from t;

X          Y
---------- ----------
secooler   secooler

4.进一步查看，初见端倪。
sec@ora10g> select '*'||x||'*', length(x), '*'||y||'*', length(y) from t;

'*'||X||'*'   LENGTH(X) '*'||Y||'*'   LENGTH(Y)
------------ ---------- ------------ ----------
*secooler *         10 *secooler*            8

sec@ora10g> select x,dump(x),y,dump(y) from t;

X
----------
DUMP(X)
----------------------------------------------------
Y
----------
DUMP(Y)
----------------------------------------------------
secooler
Typ=96 Len=10: 115,101,99,111,111,108,101,114,32,32
secooler
Typ=1 Len=8: 115,101,99,111,111,108,101,114

结论一：
CHAR类型的长度是固定长度10，而VARCHAR2类型的长度随字符串的长短自动分配大小；
CHAR类型的字段会自动的在字符串的尾部添加空格，以便补全字段到定义的长度10（参见上面dump出来的两个“32”内容），因此，如果经常使用CHAR类型存储较小的字符串将会浪费大量的存储空间。

5.进一步探索，了解细节。
sec@ora10g> select * from t where x=y;

no rows selected

sec@ora10g> select * from t where x='secooler';

X          Y
---------- ----------
secooler   secooler

sec@ora10g> select * from t where y='secooler';

X          Y
---------- ----------
secooler   secooler

既然x字段的类型是CHAR，而且其中确确实实的存放了一个尾部存有空格的字符串，那为什么当where子句中的x='secooler'条件还可以查看到具体的数据呢？
其实这里发生了隐式转换，'secooler'字符串在与CHAR类型进行比较的时候自动的将字符串'secooler'尾部填充了两个空格，然后才与真正的数据内容进行比较，所以可以检索到数据。
使用trim函数或rpad函数可以显示转换，看一下匹配后效果。
sec@ora10g> select * from t where trim(x)=y;

X          Y
---------- ----------
secooler   secooler

sec@ora10g> select * from t where x=rpad(y,10);

X          Y
---------- ----------
secooler   secooler

结论二：使用CHAR类型时可能会发生隐式转换，会有一定的开销，不推荐使用。

6.再次深入探索，验证在使用绑定变量时可能存在的问题。
1）定义一个varchar2(10)类型的变量v_varchar2，然后使用这个变量进行查询。
sec@ora10g> variable v_varchar2 varchar2(10)
sec@ora10g> exec :v_varchar2 := 'secooler'

PL/SQL procedure successfully completed.

sec@ora10g> select * from t where x=:v_varchar2;

no rows selected

sec@ora10g> select * from t where y=:v_varchar2;

X          Y
---------- ----------
secooler   secooler

使用绑定变量进行查询时是不会有隐式转换情况发生的，此时使用CHAR类型定义的x字段进行检索数据后无法得到相应信息（这个结果更加真实）。

2）再定义一个char(10)类型的变量v_char，同样的查询。
sec@ora10g> variable v_char char(10)
sec@ora10g> exec :v_char := 'secooler'

PL/SQL procedure successfully completed.

sec@ora10g> select * from t where x=:v_char;

X          Y
---------- ----------
secooler   secooler

sec@ora10g> select * from t where y=:v_char;

no rows selected

结果一样，仍然不会发生隐式转换，此时使用VARCHAR2定义的y字段无法得到检索的结果。

结论三：如果在应用中混合使用CHAR和VARCHAR2，在定义变量的时候需要细致的选定类型，以免发生检索不到数据的问题。这样无形之中带来了很多额外的风险点，加大了程序的排错难度。

7.如果我们只存储一位字符是不是就优先选择CHAR类型？
当然不是，从上面CHAR和VARCHAR2类型混用上得到的结论可见，最好不使用CHAR类型。
从原理上讲，VARCHAR2(1)和CHAR(1)在存储结构上是一样的，不存在孰优孰劣之分。

8.总结
通过上面的实验和分析，我们可以得到以下几个结论。
1）CHAR类型的长度是固定的，而VARCHAR2类型的长度随字符串的长短自动分配大小；
2）CHAR类型定义可能会浪费大量的存储空间；
3）使用CHAR类型时可能会发生隐式转换，会有一定的开销；
4）如果在应用中混合使用CHAR和VARCHAR2，需要特别关注绑定变量使用上的细节，增加了系统复杂度；
5）从效率上讲VARCHAR2和CHAR的使用没有明显的区别。

因此，一般规律是尽量不使用CHAR类型保存字符串，统一使用VARCHAR2类型，既方便管理又方便使用。

Good luck.

-- The End --

ITPub博客

【比较】CHAR？or VARCHAR2？

最新文章