mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.5.29-log | +------------+ 1 row in set (0.00 sec)
第一個，測試通過。
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );第一種，created_at使用DEFAULT CURRENT_TIMESTAMP或者DEFAULT now()，updated_at使用觸發器。
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL );mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:00:39 | 2014-09-01 14:00:39 | | 2 | wentasy | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
delimiter | DROP TRIGGER IF EXISTS tri_temp_updated_at; CREATE TRIGGER tri_temp_updated_at BEFORE UPDATE ON temp FOR EACH ROW BEGIN SET NEW.updated_at = now(); END; | delimiter ;
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已經記錄了第一條數據的更新時間 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:00:39 | 2014-09-01 14:03:05 | | 2 | wentasy | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第二種，created_at使用觸發器，updated_at使用DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP或者DEFAULT now() ON UPDATE now()；
具體解決方法如下：
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );delimiter | DROP TRIGGER IF EXISTS tri_temp_created_at; CREATE TRIGGER tri_temp_created_at BEFORE INSERT ON temp FOR EACH ROW BEGIN IF new.created_at IS NULL THEN SET new.created_at=now(); END IF; END; | delimiter ;
mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:08:36 | 2014-09-01 14:08:36 | | 2 | wentasy | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已經記錄了第一條數據的更新時間 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:08:36 | 2014-09-01 14:09:09 | | 2 | wentasy | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第三種，created_at指定timestamp DEFAULT '0000-00-00 00:00:00'，updated_at指定DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP或者timestamp DEFAULT now() ON UPDATE now()； CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:10:43 | 2014-09-01 14:10:43 | | 2 | wentasy | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已經記錄了第一條數據的更新時間 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:10:43 | 2014-09-01 14:11:24 | | 2 | wentasy | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第四種，更換MySQL版本，MySQL 5.6已經去除了此限制。
我們可以看下MySQL 5.5和5.6幫助文檔對於這個問題的解釋。
From the MySQL 5.5 documentation:
One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
Changes in MySQL 5.6.5:
Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.
我們確定下MySQL的版本。
mysql> SELECT VERSION(); +---------------------------------------+ | VERSION() | +---------------------------------------+ | 5.6.20-enterprise-commercial-advanced | +---------------------------------------+ 1 row in set (0.00 sec) CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO temp(name) VALUES('robin'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO temp(name) VALUES('wentasy'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 15:05:57 | 2014-09-01 15:05:57 | | 2 | wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 | +----+---------+---------------------+---------------------+ 2 rows in set (0.01 sec) mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已經記錄了第一條數據的更新時間 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 15:05:57 | 2014-09-01 15:06:45 | | 2 | wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
總結
Good Luck!
Robin
2014年9月1日
