MYSQL中多个TIMESTAMP的问题

问题描述

如下需求，创建一个简单的文章表，需要在创建文章和更新文章的时候自动完成create_time和update_time字段。MYSQL中insert时TIMESTAMP default CURRENT_STAMP，update时TIMESTAMP on update CURRENT_TIMESTAMP。

create table article(
    `id` int auto_increment primary key,
    `title` varchar(100),
    `content` text,
    `create_time` TIMESTAMP default CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP on update CURRENT_TIMESTAMP
);

但是这样的SQL是会报错的，ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

官方文档解释如下，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.

那如果需要insert和update更新create_time和update_time该怎么办呢？

法一：设置第一个TIMASTAMP为0

create table article(
    `id` int auto_increment primary key,
    `title` varchar(100),
    `content` text,
    `create_time` TIMESTAMP default "0000-00-00 00:00:00",
    `update_time` TIMESTAMP on update CURRENT_TIMESTAMP
);

不过这是后在create的时候就需要将create_time字段设置为null，即

insert into article(`title`, `create_time`) values ("test", null);
update article set title = "test1" where id = 1;

法二：使用触发器

create table article(
    `id` int auto_increment primary key,
    `title` varchar(100),
    `content` text,
    `create_time` TIMESTAMP default CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP
);
create trigger `update_article_trigger` before update on `article` for each row set new.`update_time` = now();

insert into article(`title`) values ("test");
update article set title = "test2" where id = 1;

这时候在update的时候就会自动触发更新update_time了。

法三：手动写入

create table article(
    `id` int auto_increment primary key,
    `title` varchar(100),
    `content` text,
    `create_time` TIMESTAMP default CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP
);

insert into article(`title`) values ("test");
update article set `title` = "test3", `update_time` = now() where id = 1;

---

参考资料：

• having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0