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

bisal

发布于 2021-09-06 15:48:17

5620

发布于 2021-09-06 15:48:17

通常在数据库表设计时，不建议将日期类型的字段定义为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等数据类型，为的就是存储日期类型，除非有特殊用途，建议还是按照语义使用正确的数据类型存储，至少不会错，否则就需要充分考虑不对称的数据类型需要在程序中进行什么转换，才可以确保正确。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/05/06 ，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle