(轉）MySQL建表設定兩個預設CURRENT_TIMESTAMP的技巧

標籤：style   blog   http   color   io   ar   使用   sp   資料   

業務情境:

例如使用者表,我們需要建一個欄位是建立時間, 一個欄位是更新時間.

解決辦法可以是指定插入時間,也可以使用資料庫的預設時間.

在mysql中如果設定兩個預設CURRENT_TIMESTAMP,會出現這樣的錯誤.

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

錯誤的建表語句:

CREATE TABLETBL_FUND_FROZEN_UNFROZEN_RECORD(ID BIGINT NOT NULL AUTO_INCREMENT,TRADE_FLOW_ID VARCHAR(60) NOT NULL,ACCOUNT_NO VARCHAR(32),INITIATOR VARCHAR(16),CREATE_DATE TIMESTAMP default CURRENT_TIMESTAMP,OPERATE_TYPE VARCHAR(32) NOT NULL,FROZEN_AMOUNT DECIMAL(18,2) DEFAULT 0.0 NOT NULL,UNFROZEN_AMOUNT DECIMAL(18,2) DEFAULT 0.0,CREDENTIAL VARCHAR(40) NOT NULL,MODIFY_DATE TIMESTAMP default CURRENT_TIMESTAMP,UNFROZEN_DATE TIMESTAMP,SERIAL_NUM BIGINT NOT NULL,VERSION BIGINT,DECRIPTION VARCHAR(200),CONSTRAINT P_Key_1 PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用函數的方式解決該問題：

通過如下方式指定時間戳記類型：

create table test_table( id integer not null auto_increment primary key, stamp_created timestamp default ‘0000-00-00 00:00:00‘, stamp_updated timestamp default now() on update now() );

進行如下測試，驗證功能是否生效：

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t5;
+----+---------------------+---------------------+
| id | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update test_table set id = 3 where id = 2;
Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

文章出處：

http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0

http://blog.163.com/user_zhaopeng/blog/static/166022708201252323942430/

(轉）MySQL建表設定兩個預設CURRENT_TIMESTAMP的技巧