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

由一條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就可以完全替代，其實內部也是做了很多的改進。通過對比來學習能夠發現不少有意思的地方。