一张表中的两个 MySQL 时间戳列

Question

我想创建一个表，其中有一列用于“创建”，另一列用于“更新”。 “创建”列将在插入时设置并且永远不会更改。每次更新行时，“已更新”列都会更改。我不想在后续的 INSERT 或 UPDATE 语句中弄乱这些列中的任何一个。那么，如果我从这样的内容开始，我的 CREATE TABLE 语句应该是什么样子呢？

CREATE TABLE IF NOT EXISTS `mydb`.`mytable` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `updated` TIMESTAMP,
  `created` TIMESTAMP,
  `deleted` TINYINT DEFAULT 0,
  `notes` TEXT DEFAULT '',
  `description` VARCHAR(100)
) TYPE=innodb;

我似乎在创建具有两个 TIMESTAMP 列的表时遇到问题。我不关心这些列是 TIMESTAMP 还是 DATETIME 或其他什么，我只是希望它们由 MySQL 填充，而无需插入或更新语句的显式指令。

我希望能够执行这样的插入：

INSERT INTO `mydb`.`mytable` (notes,description) VALUES ('some note','some description');

和这样的更新：

UPDATE `mydb`.`mytable` SET notes=CONCAT(notes,'some more notes') WHERE id=1;

都不需要在插入或更新语句中显式设置“创建”列或设置（或重置）“更新”列。

Answer 1

尝试这个来创建您的表：

CREATE TABLE IF NOT EXISTS db.test_table
(
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME DEFAULT NULL,
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted TINYINT DEFAULT 0,
notes TEXT DEFAULT NULL,
description VARCHAR(100)
)

请注意，这

updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

将允许自动更新此字段。

并在插入记录之前将其设置为触发器：

DELIMITER $

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `db`.`on_before_insert` BEFORE INSERT
    ON `db`.`test_table`
    FOR EACH ROW BEGIN
    SET new.created = NOW();    
    END$

DELIMITER ;

然后您可以使用它来插入：

INSERT INTO db.test_table(description) VALUES ("Description")

并更新您的记录

UPDATE db.test_table SET description = "Description 2" where Id=1

并且您创建和更新的字段将被适当设置。

Answer 2

新闻快报：在 mysql 中，每次更新行中的任何其他列时，TIMESTAMP 列总是使用 now() 进行更新 - 这是一个这种数据类型的特意特征。

另一方面，DATETIME 没有这种奇怪的行为 - 这是完全正常的。

答案：创建必须是DATETIME，但由于这个bug，你还需要一个触发器，像这样：

CREATE TABLE IF NOT EXISTS mytable (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `updated` TIMESTAMP, -- This will be updated to now(), if you don't set it or set it to null
  `created` DATETIME NOT NULL, -- This will never be magically updated once written
  `deleted` TINYINT DEFAULT 0,
  `notes` TEXT DEFAULT '',
  `description` VARCHAR(100)
) TYPE=innodb;

DELIMITER ~
CREATE TRIGGER mytable_insert_trigger
BEFORE INSERT ON mytable
FOR EACH ROW BEGIN
    SET NEW.created = CURRENT_TIMESTAMP;
END;~
DELIMITER ;

insert into mytable (notes) values ('test');
select * from mytable;
+----+---------------------+---------------------+---------+-------+-------------+
| id | updated             | created             | deleted | notes | description |
+----+---------------------+---------------------+---------+-------+-------------+
|  1 | 2011-07-05 11:48:02 | 2011-07-05 11:48:02 |       0 | test  | NULL        |
+----+---------------------+---------------------+---------+-------+-------------+

Answer 3

试试这个：

CREATE TABLE IF NOT EXISTS mydb.mytable
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    updated DATETIME,
    created TIMESTAMP,
    deleted TINYINT DEFAULT 0,
    notes TEXT DEFAULT '',
    description VARCHAR(100)
) TYPE=innodb;

编辑：使用触发器。

CREATE TRIGGER mytable_update
BEFORE UPDATE ON mydb.mytable
    FOR EACH ROW SET NEW.updated = NOW();

Answer 4

另一种方法是更改​​时间戳列的顺序

或

设置第一列默认值，如下所示

ALTER TABLE `tblname` CHANGE `first_timestamp_column` 
     `first_timestamp_column` TIMESTAMP NOT NULL DEFAULT 0;

参考

Answer 5

不幸的是，MySQL 不允许在一张表中拥有两个 TIMESTAMP 列。我将使用 ON UPDATE CURRENT_TIMESTAMP 来更新列，并使用 NOW() 函数手动创建集。