为什么日期不建议使用VARCHAR2或者NUMBER？

bisal的个人杂货铺 2021-05-06

532

通常在数据库表设计时，不建议将日期类型的字段定义为VARCHAR2或者NUMBER类型，语义是其中一方面的原因，从使用层面，还是有其他原因。

若定义为VARCHAR2类型，模拟如下，

SQL> create table test(c1 varchar2(25));
Table created.

插入三条数据，

SQL> insert all into test(c1) values('01JAN21')
  2             into test(c1) values('01APR21')
  3             into test(c1) values('01MAR21')
  4  select 1 from dual;
3 rows created.

如下结果的顺序是随机的，

SQL> select * from test;
C1
-------------------------
01JAN21
01APR21
01MAR21

P.S. 可参考《Oracle数据顺序问题》、《Oracle读取数据的顺序问题》。

如果我需要按照日期递增顺序返回，仅仅通过order by，得到的结果如下，明显是错误的，因为字段c1是字符串类型，order by排序的时候是按照字母的顺序，

SQL> select * from test order by c1;
C1
-------------------------
01APR21
01JAN21
01MAR21

如果按照实际日期的顺序，则可以在order by中进行显式地转换，他是按照日期类型排序的，

SQL> select * from test order by to_date(c1, 'DDMONYY');
C1
-------------------------
01JAN21
01MAR21
01APR21

因此我们说对日期类型，如果按照字符串类型存储，像排序这种操作，需要进行转换，才可以得到正确的结果，在程序中需要注意的。

如果将日期存储为数值NUMBER类型，重复如上操作，是可以得到正确的结果，因为数值类型的排序从语义上和日期排序是相同的，

SQL> create table test(c1 number);
Table created.

SQL> insert all into test(c1) values(20210101)
  2             into test(c1) values(20210401)
  3             into test(c1) values(20210301)
  4  select 1 from dual;
3 rows created.

SQL> select * from test;
        C1
----------
  20210101
  20210401
  20210301

SQL> select * from test order by c1;
        C1
----------
  20210101
  20210301
  20210401

但是一些将日期作为参数的函数，就无法直接使用，

SQL> select c1, add_months(c1,1) from test;
select c1, add_months(c1,1) from test
                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

需要显式转换，

SQL> select c1, add_months(to_date(c1,'yyyy-mm-dd'),1) c from test;      
        C1 C
---------- ------------------
  20210101 01-FEB-21
  20210401 01-MAY-21
  20210301 01-APR-21

虽然这种函数消耗在当前的软硬件环境中，几乎就是忽略不计的，但是从严谨性、精算性、规范性的角度，还是应当避免这种情况，既然数据库给我们提供了DATE、TIMESTAMP等数据类型，为的就是存储日期类型，除非有特殊用途，建议还是按照语义使用正确的数据类型存储，至少不会错，否则就需要充分考虑不对称的数据类型需要在程序中进行什么转换，才可以确保正确。

近期更新的文章：

《》

《写代码如何事半功倍？》

《支持超过4000字节的varchar2类型》

《“自以为对的”MyBatis空闲连接探测的机制》

《小白学习MySQL - 索引键长度限制的问题》

《积累一些SQL》

《创建主键的三种方式对指定索引表空间操作的纠正》

《Oracle优化器的“短路”》

《小白学习MySQL - MySQL会不会受到“高水位”的影响？》

《MySQL行转列的小需求》

《Oracle的greatest和least函数》