日期数据应该以什么数据类型存储

日期类型应该存储成什么数据一直是很多人容易弄错的问题。总的来说，在各种数据库中日期类型都应该存储成date类型。对于mysql和postgresql来说，存储成日期类型是没有问题的；对于ORACLE数据库来说，日期类型有其特殊的问题。因为ORACLE数据库的date数据类型的精度是到秒，因此如果只是需要精确到天的话，处理起来要麻烦一点。
mysql和pgsql可以使用如下语句进行查询：

select * from emp where birth_day=a date;

但是ORACLE里就不能这么写查询，必须写成：

select * from emp where birth_day between to_date(datestr||' 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date(datestr||'23:59:59','yyyy/mm/dd hh24:mi:ss') ;

注意不要用这种写法：

select * from emp where birth_day>=to_date(datestr||' 00:00:00','yyyy/mm/dd hh24:mi:ss') and birth_day<= to_date(datestr||'23:59:59','yyyy/mm/dd hh24:mi:ss') ;

这种写法有其本身的问题。

在这种情况下很多人会直接这样写查询（因为between写起来麻烦一点）：

select * from where datestr=to_char(birth_day,'yyyy/mm/dd');

熟悉数据库的人都知道这么写会给数据库带来多大的影响，即使加fbi。

因此很多人为了省事，就直接在数据库中把日期型数据存储成为字符型或者数字型(高丽棒子就常这么做)，比如：

create table t1 (tid number,birth_day date);
create table t2 (tid number,birth_day char(8));
create table t3 (tid number,birth_day number(8));

insert into t1 select rownum,sysdate-rownum from all_objects where rownum<=365*3;
insert into t2 select rownum,to_char(sysdate-rownum,'yyyymmdd') from all_objects where rownum<=365*3;
insert into t3 select rownum,to_number(to_char(sysdate-rownum,'yyyymmdd')) from all_objects where rownum<=365*3;

commit;

create index t1_bd_idx on t1(birth_day);
create index t2_bd_idx on t2(birth_day);
create index t3_bd_idx on t3(birth_day);

set autot traceonly explain

执行以下查询：

select * from t1 where birth_day between to_date('20070101','yyyymmdd') and to_date('20071231','yyyymmdd');
因为有三分之一的数据会命中，所以ORACLE肯定会使用全表扫描，执行计划中基数是365，成本是3。

select * from t2 where birth_day between '20070101' and '20071231';
这个查询其实应该和上一个查询命中同样多的数据，有同样的执行计划。但是很遗憾，ORACLE执行了索引范围扫描，然后通过ROWID再拿到数据。整个查询中基数是43，成本是3,(这只是优化器估算出来的成本，实际的成本应该是26)。

select * from t3 where birth_day between 20070101 and 20071231;
这个查询跟上一个查询有同样的执行计划。

很显然后两个查询的执行计划是有问题的。这个问题就是由于错误的数据类型导致ORACLE在估算数据的范围的时候出了差错，比如第三个查询数据库的估算方法是：
（20071231-20070101）--条件里范围差
/(20081210-20051210) --实际数据的范围差
*1095（表里的行数）
最后的出来的基数是42。字符类型的计算也与此类似。
当然如果在表上建立了直方图的话，情况会好一些，成本估算所得的基数与实际基数近似。

由此可见将日期类型存储成字符或者数值类型会有很大问题，虽然程序员在处理问题时方便了一点，但是数据库的性能确会遭到影响（程序员都喜欢这么设计数据库表）。
另外由于pgsql和mysql的优化查询算法是不完全与ORACLE类似,因此这种设计表的方法在这两种数据库上运行的如何有待继续研究。

有问题欢迎与我联系。