MYSQL中多个TIMESTAMP的问题

问题描述

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

1
2
3
4
5
6
7
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_timeupdate_time该怎么办呢?

法一:设置第一个TIMASTAMP为0

1
2
3
4
5
6
7
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,即

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

法二:使用触发器

1
2
3
4
5
6
7
8
9
10
11
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了。

法三:手动写入

1
2
3
4
5
6
7
8
9
10
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;

参考资料:

Just a beginner.<br /><a href='https://github.com/yaoshanliang/about' target='_blank'>profile</a>