mysql - 创建触发器和存储过程，在用户通过身份验证时更新日期时间

Question

我用 PHP 和 MySQL 创建了一个登录系统，并为用户提供了下表:

CREATE TABLE `users` (
    `ID` int(11) NOT NULL,
    `Authenticated` tinyint(1) NOT NULL DEFAULT 0,
    `Name` varchar(20) NOT NULL,
    `Surname` varchar(20) NOT NULL,
    `Username` varchar(20) NOT NULL,
    `Email` varchar(255) NOT NULL,
    `Password` varchar(70) NOT NULL,
    `Created_At` datetime DEFAULT current_timestamp(),
    `Authenticated_At` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当用户注册时，系统会在用户表中插入一个带有 Authenticated = 0 的用户。作为管理员，我必须使用以下方法对用户进行身份验证:

UPDATE users SET Authenticated = 1 WHERE ID = {$id};

我想知道的是如何创建一个触发器和一个存储过程，在上述 UPDATE 查询之后将 Authenticated_At 列更新为当前日期和时间.

提前致谢。

Answer 1

1)最简单的解决方案:

UPDATE users SET Authenticated = 1, Authenticated_At = NOW() WHERE ID = {$id};

2) 另一种解决方案是为时间戳值设置一个DEFAULT 值。这是在表定义中实现的，例如:

CREATE TABLE `users` (
    `ID` int(11) NOT NULL,
    `Authenticated` tinyint(1) NOT NULL DEFAULT 0,
    `Name` varchar(20) NOT NULL,
    `Surname` varchar(20) NOT NULL,
    `Username` varchar(20) NOT NULL,
    `Email` varchar(255) NOT NULL,
    `Password` varchar(70) NOT NULL,
    `Created_At` datetime DEFAULT current_timestamp(),
    `Authenticated_At` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

来自the documentation :

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

为了在每次更新记录时自动更新 Authenticated_At 时间戳(而不更新 Created_At)，您需要:

UPDATE users SET Authenticated = 1, Created_At = Created_At WHERE ID = {$id};

3)正如 @Raymond Nijland 明智地评论的那样，最好的解决方案是定义时间戳列仅在 UPDATE 操作上自动更新，例如:

`Authenticated_At` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP