mysql - 如何在表更新时自动更新时间戳？

Question

我有一张带有新闻帖子的表格，我想在添加日期添加时间戳，并且我想在编辑帖子时更新另一个列。我希望它在 MySql 中自动发生。无需使用任何 PHP 代码。

   CREATE TABLE IF NOT EXISTS news (
      id int(11) NOT NULL AUTO_INCREMENT,
      data text,
      date_published timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      date_edited timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id)
    );

在这种情况下，最佳做法是什么？

Answer 1

考虑使用触发器。来自MySQL docs :

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

触发器示例:

CREATE TRIGGER trigger_example AFTER UPDATE ON news
FOR EACH ROW UPDATE some_table SET another_column = NEW.data;