极客笔记MySQL和PostgreSQL上的当前时间戳更新
在使用关系型数据库时，有时我们需要在表中插入或更新数据时记录时间戳。时间戳可以用于记录行的最后修改时间，以便跟踪每个行的更改，也可以用于实现业务逻辑，例如在一个特定时间之前过期的行。在本文中，我们将探讨如何在MySQL和PostgreSQL上创建一个可在更新行时向其添加时间戳的表。
阅读更多：MySQL 教程
MySQL上的当前时间戳更新
在MySQL上，您可以使用TIMESTAMP或DATETIME类型来存储日期和/或时间。TIMESTAMP类型以Unix时戳格式存储值，并带有时区信息。DATETIME类型以人类可读格式存储值，而不带有时区信息。在这两种类型中，都可以使用将CURRENT_TIMESTAMP作为默认值，以便在插入或更新每行时自动记录当前时间。
创建表并添加时间戳
以下是创建一个名为“customers”的表并在其中添加时间戳的MySQL代码：
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(60),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
在上面的代码中，我们创建了一个名为“customers”的表，并使用AUTO_INCREMENT设置主键。我们还包括了first_name，last_name和email列来存储客户信息。而对于created_at列，我们使用了DEFAULT CURRENT_TIMESTAMP以指定在每次插入行时自动记录当前时间戳。对于updated_at列，我们使用了DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP来指定在每次更新行时自动记录当前时间戳。
插入数据和更新时间戳
在表创建后，我们可以使用INSERT语句向其添加数据。以下是对“customers”表进行INSERT操作并插入几行数据的MySQL语句：
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'johndoe@example.com'),
('Jane', 'Doe', 'janedoe@example.com'),
('Bob', 'Smith', 'bobsmith@example.com');
此时，MySQL将会自动为created_at和updated_at列设置当前时间戳。
如果我们想更新行并记录更新时间戳，我们可以使用UPDATE语句。以下是一个更新名为“John”的客户电子邮件地址的MySQL语句：
UPDATE customers SET email = 'newemail@example.com' WHERE first_name = 'John';
这将不仅更新John的电子邮件地址，还将更新updated_at列。
PostgreSQL上的当前时间戳更新
在PostgreSQL上，您可以使用TIMESTAMP WITH TIME ZONE或TIMESTAMP WITHOUT TIME ZONE类型来存储日期和/或时间。TIMESTAMP WITH TIME ZONE类型以ISO 8601格式存储值，并带有时区信息。TIMESTAMP WITHOUT TIME ZONE类型以人类可读格式存储值，但不带有时区信息。与MySQL不同的是，您需要使用触发器来在PostgreSQL上自动记录更新时间戳。
创建表并添加时间戳
以下是创建一个名为“customers”的表并在其中添加时间戳的PostgreSQL代码：
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(60),
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITHOUT TIME ZONE
);
在上面的代码中，我们创建了一个名为“customers”的表，并使用SERIAL设置主键。我们还包括了first_name，last_name和email列来存储客户信息。与MySQL不同的是，在created_at列中，我们使用了DEFAULT NOW()以指定在每次插入行时自动记录当前时间戳。而在updated_at列中，我们并没有设置默认值，因为我们将使用触发器来更新它。
创建触发器
在PostgreSQL上，您可以使用触发器来在更新行时自动记录时间戳。以下是为“customers”表创建触发器并将其添加到updated_at列的PostgreSQL代码：
CREATE OR REPLACE FUNCTION update_customer_timestamp()
RETURNS TRIGGER AS BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END; LANGUAGE plpgsql;
CREATE TRIGGER update_customer
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_customer_timestamp();
在上面的代码中，我们首先创建了一个名为update_customer_timestamp的函数，该函数在每次更新行时设置updated_at列为当前时间戳。然后，我们使用这个函数创建了一个名为update_customer的触发器，并向其添加了BEFORE UPDATE触发器，以便在每次更新行之前运行。最后，我们将触发器应用于“customers”表的每一行。
插入数据和更新时间戳
在表创建完毕，并且触发器已启用后，我们可以使用INSERT语句向其添加数据。以下是对“customers”表进行INSERT操作并插入几行数据的PostgreSQL语句：
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'johndoe@example.com'),
('Jane', 'Doe', 'janedoe@example.com'),
('Bob', 'Smith', 'bobsmith@example.com');
此时，PostgreSQL将会自动为created_at列设置当前时间戳，并在更新行时使用触发器为updated_at列设置当前时间戳。
如果我们想更新行并记录更新时间戳，我们可以使用UPDATE语句。以下是一个更新名为“John”的客户电子邮件地址的PostgreSQL语句：
UPDATE customers SET email = 'newemail@example.com' WHERE first_name = 'John';
这将更新John的电子邮件地址，并由触发器为updated_at列设置当前时间戳。
总结
在MySQL和PostgreSQL上，都可以使用相似的方法来记录时间戳，并随着插入和更新而自动更新。MySQL使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP来实现此功能，而PostgreSQL需要使用触发器来更新时间戳。无论您选择哪个方法，都应该在您的应用程序中为每个数据更改设置时间戳，以便在需要时能够确定行的最后更新时间。