程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> MySQL中同時存在創建和上次更新時間戳字段解決方法淺析

MySQL中同時存在創建和上次更新時間戳字段解決方法淺析

編輯：MYSQL入門知識

問題重現

在寫這篇文章之前，明確我的MySQL版本。

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)

現在有這樣的需求，一張表中有一個字段created_at記錄創建該條記錄的時間戳，另一個字段updated_at記錄更新該條記錄的時間戳。
我們嘗試以下幾個語句。

第一個，測試通過。

CREATE TABLE temp
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    updated_at timestamp NULL \
    DEFAULT CURRENT_TIMESTAMP \
    ON UPDATE CURRENT_TIMESTAMP
);

第二個，測試不通過。報ERROR 1293 (HY000)錯誤。（完整錯誤信息：ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause）

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
);

MySQL 5.5.29中有這樣的奇葩限制，不明白為什麼。既然有這樣的限制，那麼只有繞道而行，現在嘗試給出如下幾種解決辦法。

解決方案一

第一種，created_at使用DEFAULT CURRENT_TIMESTAMP或者DEFAULT now()，updated_at使用觸發器。

具體解決方法如下：
1.temp表結構如下：

CREATE TABLE temp
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    created_at timestamp NULL \
    DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NULL
);

2.插入測試數據：

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)

3.在temp上創建觸發器，實現更新時記錄更新時間；

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 ;

4.測試。

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()；

具體解決方法如下：
1.temp表結構如下：

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
);

2.在temp上創建觸發器，實現插入數據記錄創建時間；

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 ;

3.插入測試數據：

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)

4.測試。

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()；

具體解決方法如下：
1.temp表結構如下：

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
);

2.插入測試數據：

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)

3.測試。

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)

我們把文首測試不通過的SQL語句在MySQL 5.6下執行，可以看到沒有任何錯誤。

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)

總結

本文介紹的方法歸根結底，就兩條，一是建表語句指定默認值和更新動作，二是使用觸發器插入默認值和更新時間。面對當前無法更改的事實，只能采取折中的辦法或者犧牲更多來彌補。還有一條值得注意的是，遇到問題多想想不同的解決辦法，盡可能地列出所有可能或者可行的方案，這樣一來讓自己學到更多，二來可以鍛煉思維的廣度，三來多種方案可以彌補某種方案在特定環境下不可行的不足。