由一條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就可以完全替代，其實內部也是做了很多的改進。透過對比來學習能夠發現不少有意思的地方。
語句是一個簡單的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就可以完全替代，其實內部也是做了很多的改進。透過對比來學習能夠發現不少有意思的地方。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1844930/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 由一條sql語句導致的系統IO問題SQL
- oracle中的條件語句Oracle
- MySQL 中 一條 order by index limit 語句的分析MySqlIndexMIT
- MySQL和Oracle中的delete,truncate對比MySqlOracledelete
- 一條SQL語句在MySQL中如何執行的MySql
- 一條sql語句在mysql中是如何執行的MySql
- 一條insert語句導致的效能問題分析（一)
- ORACLE DATE和TIMESTAMP資料型別的比較(一) (轉)Oracle資料型別
- Oracle中Date和Timestamp的區別Oracle
- Oracle和MySQL的高可用方案對比（一）OracleMySql
- 一條簡單的sql語句導致的系統問題SQL
- 對sql語句的優化問題SQL優化
- 一條insert語句導致的效能問題分析（二)
- Oracle date 型別比較和String比較Oracle型別
- Oracle、MySQL常見表結構變更語句對比OracleMySql
- MySQL：一條更新語句是如何執行的MySql
- 簡單對比MySQL和Oracle中的一個sql解析細節MySqlOracle
- sqlserver 檢視和sql語句的效率對比SQLServer
- 兩個看似奇怪的MySQL語句問題MySql
- 對sql語句的最佳化問題SQL
- MySQL和Oracle的新增欄位的處理差別MySqlOracle
- 用一條mysql語句插入多條資料MySql
- 一條執行4秒的sql語句導致的系統問題SQL
- Mysql中的DQL語句MySql
- 通過oracle類比MySQL中的位元組字元問題OracleMySql字元
- C語言中迴圈語句while 中判斷條件出現 || 和 && 的區別C語言While
- 如此大的一條sql語句在30個左右的併發訪問系統當中的效能問題？SQL
- ORACLE DATE和TIMESTAMP資料型別的比較(二) (轉)Oracle資料型別
- recover database的四條語句區別 .Database
- 關於Ibatis 轉換 oracle date型別的問題BATOracle型別
- SQL Server SQL語句中的函式呼叫與Oracle SQL語句函式呼叫一個有趣的差別SQLServer函式Oracle
- 一條簡單的更新語句，MySQL是如何加鎖的？MySql
- oracle中sysdate和current_date兩者的區別Oracle
- oracle 對比sql語句執行環境OracleSQL
- Oracle Decode()函式和CASE語句的比較Oracle函式
- delete 語句帶別名問題.delete
- SqlServer中迴圈和條件語句SQLServer
- 執行oracle DDL語句要注意的問題Oracle