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> INSERTINTOtemp(name,created_at,updated_at) VALUES('robin',now(),now()); 

Query OK, 1 row affected (0.03 sec) 

 

 

mysql> INSERTINTOtemp(name,created_at,updated_at) VALUES('wentasy',now(),now()); 

Query OK, 1 row affected (0.01 sec) 

 

 

mysql> SELECT * FROMtemp; 

+----+---------+---------------------+---------------------+ 

| 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 rowsinset (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> UPDATEtempSETname='robinwen'WHERE id=1; 

Query OK, 1 row affected (0.01 sec) 

Rows matched: 1 Changed: 1 Warnings: 0

 

 

#可以看到已经记录了第一条数据的更新时间 

mysql> SELECT * FROMtemp; 

+----+----------+---------------------+---------------------+ 

| 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 rowsinset (0.00 sec)