oracle sql char varchar,老生常谈的Oracle的CHAR与VARCHAR数据类型

开门见山直接说问题，前一段时候生产上进行数据库的变更，导致了在短时间内业务的异常，最后通过将CHAR类型修改为VARCHAR2类型后解决了该问题。

一、问题背景

举一个类似的例子，我们有一个学生表STUDNET，表结构与数据如下，系统升级前只支持本科生的管理，系统升级后要添加研究生的管理功能，但是学号的位数不一致，所以由原来的CHAR(5)修改为CHAR(7)，后面发现本科生管理功能不能正常时候，后将CHAR(5)修改为VARCHAR2(7)后恢复使用。

表结构：

SQL> desc student;

Name Null? Type

----------------------------------------- -------- ----------------------------

STU_ID NOT NULL CHAR(7)

STU_NAME VARCHAR2(10)

STU_GENDER VARCHAR2(5)

数据：

SQL> select * from student;

STU_ID STU_NAME STU_G

------- ---------- -----

1001 小明 男

1002 小刚 男

2001001 小米 女

2001002 小敏 女

二、问题干扰

对于CHAR和VARCHAR的数据类型的问题有一点数据库基础的同学应该都知道，CHAR是定长，VARCHAR是变长，但是在问题的分析上我们受到了一些表面现象的干扰，通过PLSQL或者SQLPLUS对如下语句进行查询，发现无论条件是1001还是1001_都可以查询到数据，这也会给经验不足的开发人员一定的干扰，理所当然的认为程序去跑该SQL也没有问题，但是后面我们发现业务通过Mybatis执行该SQL没有查询到对应的数据。

SQL> select * from student where stu_id='1001';

STU_ID STU_NAME STU_G

------- ---------- -----

1001 小明 男

SQL> select * from student where stu_id='1001 ';

STU_ID STU_NAME STU_G

------- ---------- -----

1001 小明 男

三、问题解决

1.我们知道CHAR类型是定长，如果长度不足该字段定义的长度，那会自动在后面补足空格，在PLSQL或者SQLPLUS中对CHAR类型列进行查询时，会自动补齐查询条件中的空格(换一种说法也可以理解为查询的值和被查询的值都进行了末端去空格处理)。

2.但是在程序去操作数据库时，如使用Mybatis或Hibernate去查询数据时就不会做一些额外的操作，所以执行下列语句时会查不到对应的数据，原因是1000与1001___不相等。

select * from student where stu_id='1001';

3.那解决这个问题的思路有两个，一个是按照如下方式改写SQL，将CHAR类型的列中后面的空格去掉，另一种方案是将CHAR类型改为VARCHAR2类型，我个人推荐第二种。

SQL> select * from student where trim(stu_id)='1001';

STU_ID STU_NAME STU_G

------- ---------- -----

1001 小明 男

四、学习知识

1.CHAR与VARCHAR的比较

首先，CHAR为定长，VARCHAR为变长，在空间上VARCHAR会比较节省空间。其次，在查询效率上，CHAR会比VARVHAR快一些，因为处理VARVHAR类型还会有一些额外的操作。

我看还有一种说法是说如果VARCHAR类型的列存储的内容长度经常改变会发生“行迁移”的现象，所以会导致VARCHAR效率比较慢，这种说法我不是很同意，因为即使存在CHAR类型的列的内容长度不变，如果该行其他的列的长度经常更改也会造成“行迁移”。

2.VARCHAR与VARCHAR2的比较

目前在ORACLE中VARCHAR是VARCHAR2的同义词。标准的VARCHAR类型可以存储空字符串，ORACLE自己设计了一个VARCHAR2的数据类型，它将存储空字符串的特性改为存储NULL值。