MySQL和PostgreSQL中的Current timestamp on Update

在MySQL和PostgreSQL中，有一个称为“Current timestamp on Update”的特殊功能。这个功能可以为表中的每个行添加一个自动更新的时间戳，并且当行被更新时，这个时间戳会自动更新。在本文中，我们将探讨这个功能是如何工作的，并提供一些使用它的示例。

阅读更多：MySQL 教程

Current timestamp on Update的工作原理

在MySQL和PostgreSQL中，当前更新时间戳是一个特殊类型的列，可以自动更新。当您将表中的行插入或更新，该列的值将自动设置为当前时间戳。如果该行被更新，该时间戳将自动更新为当前时间戳。

在MySQL中，利用该功能的一种方法是使用TIMESTAMP数据类型和ON UPDATE CURRENT_TIMESTAMP选项。在一个表中，定义一个TIMESTAMP类型的列并在这个列中设置ON UPDATE CURRENT_TIMESTAMP选项，如下面的例子所示：

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  mycolumn VARCHAR(255) NOT NULL,
  timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

上面的代码定义了一个名为mytable的表，其中有一个名为timestamp_column的列，是一个TIMESTAMP类型的列，并在该列上设置了ON UPDATE CURRENT_TIMESTAMP选项。当插入或更新该表中的行时，该列的值将自动设置为当前时间戳，并在更新该行时自动更新。

在PostgreSQL中，您可以使用TIMESTAMP WITH TIME ZONE数据类型并创建一个触发器来实现相同的功能。下面是一个示例代码：

CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  mycolumn VARCHAR(255) NOT NULL,
  timestamp_column TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE OR REPLACE FUNCTION update_timestamp_column()
RETURNS TRIGGER AS BEGIN
  NEW.timestamp_column = now();
  RETURN NEW;
END; LANGUAGE PLPGSQL;

CREATE TRIGGER mytable_update_timestamp_trigger
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_column();

上面的代码创建了一个名为“mytable”的表，在其中“timestamp_column”列定义为“TIMESTAMP WITH TIME ZONE”类型。随后，创建一个触发器（mytable_update_timestamp_trigger），并在该触发器中定义一个函数（update_timestamp_column），该函数将在更新mytable中的行时自动执行，并更新“timestamp_column”列的值。现在，每当更新该表中的任何行时，“timestamp_column”列的值都将自动更新为当前时间戳。

如您所见，MySQL和PostgreSQL的方法略有不同，但在其基础上的一般概念是相同的：创建一个列来存储时间戳，并设置选项或触发器来自动更新该列。

使用示例

下面是一些使用MySQL和PostgreSQL中的当前更新时间戳的示例。

示例1：在MySQL中创建一个表

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  mycolumn VARCHAR(255) NOT NULL,
  timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

上面的代码使用MySQL创建了一个具有三个列的表：id、mycolumn和timestamp_column。id和mycolumn列是常规列，而timestamp_column列是一个TIMESTAMP类型的列，并在该列上设置了ON UPDATE CURRENT_TIMESTAMP选项。这样，当向该表中插入新行或更新现有行时，该列的值将自动设置为当前时间戳，并在更新行时自动更新。

示例2：在PostgreSQL中创建一个表

CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  mycolumn VARCHAR(255) NOT NULL,
  timestamp_column TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE OR REPLACE FUNCTION update_timestamp_column()
RETURNS TRIGGER AS BEGIN
  NEW.timestamp_column = now();
  RETURN NEW;
END; LANGUAGE PLPGSQL;

CREATE TRIGGER mytable_update_timestamp_trigger
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_column();

上面的代码使用PostgreSQL创建了一个具有三个列的表：id、mycolumn和timestamp_column。id和mycolumn列是常规列，而timestamp_column列是一个TIMESTAMP WITH TIME ZONE类型的列。随后，创建了一个名为“mytable_update_timestamp_trigger”的触发器，该触发器在每次更新mytable中的行时自动执行，并调用名为“update_timestamp_column”的函数来更新“timestamp_column”列的值为当前时间戳。

示例3：在MySQL中更新行并自动更新时间戳

UPDATE mytable SET mycolumn = 'new value' WHERE id = 1;

上面的代码使用更新语句将mytable表中id为1的行的mycolumn列更新为“new value”。由于mytable表中的timestamp_column列设置为ON UPDATE CURRENT_TIMESTAMP，因此在该行被更新时，timestamp_column列的值将自动更新为当前时间戳。这样可以确保在每次更新行时，时间戳列都将自动更新。

示例4：在PostgreSQL中更新行并自动更新时间戳

UPDATE mytable SET mycolumn = 'new value' WHERE id = 1;

上面的代码使用更新语句将mytable表中id为1的行的mycolumn列更新为“new value”。由于mytable表中设置了一个名为“mytable_update_timestamp_trigger”的触发器，并在该触发器中定义了一个名为“update_timestamp_column”的函数，该函数将更新timestamp_column列的值，因此在该行被更新时，timestamp_column列的值将自动更新为当前时间戳。这样可以确保在每次更新行时，时间戳列都将自动更新。