由一條create語句的問題對比mysql和oracle中的date差別

今天開發的同事提交過來一個sql變更，在部署的時候發現了一個問題。
語句是一個簡單的create語句
CREATE TABLE `test_user` (
  `openid` varchar(64) NOT NULL,
  `amount` varchar(11) DEFAULT 0,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
報錯內容為：
ERROR 1067 (42000) at line 1: Invalid default value for 'create_time'
首先這個看起來不是一個語法問題，部署使用的環境是5.5
這個時候手頭有一套虛擬機器測試環境，立馬實驗了一下，發現在5.6中竟然沒有任何問題。
得到的資訊如下
Query OK, 0 rows affected (0.13 sec)
所以這個問題引起了我的注意。
我做了下面幾個測試，首先current_timestamp肯定是可用的。看看時間的情況。
 select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2015-11-23 18:31:25 | 2015-11-23 18:31:25 |
+---------------------+---------------------+
1 row in set (0.00 sec)
我們建立一個測試表來簡單測試一下看看問題到底在哪裡。
create table test(col1 datetime DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'col1'
看起來似乎是新增default值的時候出了問題。
> create table test(col1 datetime DEFAULT '');
ERROR 1067 (42000): Invalid default value for 'col1'
新增空值，也是不可以。
> create table test(col1 datetime DEFAULT '2015-11-23 18:31:25');
Query OK, 0 rows affected (0.02 sec)
新增一個固定的靜態預設值，這樣就可以了。
同時檢視了一些文章，有的說不能新增預設值，這個說法應該是不成立的，只能說是不能新增動態的預設值。還有一種說法是default的動態預設值是在5.6.5才開始支援。
這部分內容在MySQL官方文件中也有說明。http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
那麼這個問題還是需要解決，怎麼解決呢。首先不可能為了這個操作先把資料庫升級到5.6
那麼解決方法就有兩個。
一個是就是取消預設值，一個就是欄位型別改為timestamp
那麼問題來了，datetime和timestamp有啥區別和聯絡，如果沒有記錯還有一個型別時date,這三種資料型別有啥區別和關係
我們建立一個表，含有三個欄位，datetime,timestamp,date
create table test(date1 datetime,date2 timestamp,date3 date);
然後插入三個值，來看看有什麼差別。
mysql> insert into test values(current_date,current_timestamp,current_date);
Query OK, 1 row affected (0.00 sec)
mysql> select *from test;
+---------------------+---------------------+------------+
| date1               | date2               | date3      |
+---------------------+---------------------+------------+
| 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 |
+---------------------+---------------------+------------+
可以看出datetime和timestamp其實是精確到秒的，date是精確到日。
那麼datetime和timestamp有啥區別。
datetime和timestamp的儲存佔用空間不同，datetime佔用8個位元組，timestamp佔用4個位元組，所以說timestamp支援的時間範圍要窄一些。範圍為：1970-01-01 08:00:01到2038-01-19 11:14:07  而datetime支援的時間範圍則要大很多。1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
簡單來說，解放前的資料用timestamp就不合適了,而datetime則要更寬泛一些。
來簡單驗證一下。如果對timestamp列新增超過時間範圍的值，則會報錯。
mysql> insert into test values('2038-01-19 11:14:08','2038-01-19 11:14:08',current_date);
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'date2' at row 1
再次對timestamp插入最大值。
mysql> insert into test values('2038-01-19 11:14:08','2038-01-19 11:14:07',current_date);
mysql> select *from test;
+---------------------+---------------------+------------+
| date1               | date2               | date3      |
+---------------------+---------------------+------------+
| 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 |
| 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 |
+---------------------+---------------------+------------+
mysql> insert into test values('9999-12-31 23:59:59','2038-01-19 11:14:07',current_date);                   
Query OK, 1 row affected (0.00 sec)
對datetime插入最大值
mysql> insert into test values('9999-12-31 23:59:59','2038-01-19 11:14:07',current_date);                  
mysql> select *from test;
+---------------------+---------------------+------------+
| date1               | date2               | date3      |
+---------------------+---------------------+------------+
| 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 |
| 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 |
| 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 |
+---------------------+---------------------+------------+
除此之外，datetime和timestamp還有一個區別，就是timestamp對於Insert，update操作會預設設定為current_timestamp
比如對datetime插入值，timestamp,date保留為空，結果如下：
mysql> insert into test(date1) values('2038-01-19 11:14:06');
mysql> select *from test;
+---------------------+---------------------+------------+
| date1               | date2               | date3      |
+---------------------+---------------------+------------+
| 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 |
| 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 |
| 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 |
| 2038-01-19 11:14:06 | 2015-11-23 23:30:22 | NULL       |
+---------------------+---------------------+------------+
4 rows in set (0.00 sec)
如果只對timestamp輸入值，datetime和date為空，效果就大大不同
insert into test(date2) values('2038-01-19 11:14:06');
mysql> select *from test;
+---------------------+---------------------+------------+
| date1               | date2               | date3      |
+---------------------+---------------------+------------+
| 2015-11-23 00:00:00 | 2015-11-23 23:21:35 | 2015-11-23 |
| 2038-01-19 11:14:08 | 2038-01-19 11:14:07 | 2015-11-23 |
| 9999-12-31 23:59:59 | 2038-01-19 11:14:07 | 2015-11-23 |
| 2038-01-19 11:14:06 | 2015-11-23 23:30:22 | NULL       |
| NULL                | 2038-01-19 11:14:06 | NULL       |
+---------------------+---------------------+------------+
這種型別的問題在oracle中就會是另外一種情況，oracle中對於timestamp的型別，精度要比date要高。
來簡單做一個測試
SQL> create table test(date1 date,date2 timestamp);
Table created.
SQL> col name format a20
其實這個時候來檢視timestamp，可以看到一個timestamp(6)字樣的型別。
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATE1                                              DATE
 DATE2                                              TIMESTAMP(6)
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> insert into test values(sysdate,sysdate);
SQL> col date2 format a30
DATE1               DATE2
------------------- ------------------------------
2015-11-23 23:03:35 23-NOV-15 11.03.35.000000 PM
如果插入systimestamp，結果會有一些差別。
SQL> insert into test values(systimestamp,systimestamp);
1 row created.
SQL> select *from test;
DATE1               DATE2
------------------- ------------------------------
2015-11-23 23:03:35 23-NOV-15 11.03.35.000000 PM
2015-11-23 23:05:08 23-NOV-15 11.05.08.378586 PM

其實在oracle中時間的型別還有很多，比如timestamp with timezone等等，支援的幅度也更大。
如果上面的問題在oracle中，是否可以支援動態的預設值呢，肯定可以，因為我們似乎已經習慣這麼用了。
可以用下面的方式來指定。
SQL> alter table test modify(date1 default sysdate);
Table altered.
SQL> alter table test modify(date2 default systimestamp);
Table altered.
透過這些小測試也發現了時間的型別在mysql和oracle中還是有很大的差別，可能在資料型別的劃分上，mysql劃分的型別更多，資料型別非常多，而oracle似乎一個Number就可以完全替代，其實內部也是做了很多的改進。透過對比來學習能夠發現不少有意思的地方。