MySQL:如何自动记录上次密码更新的时间戳

MySQL 自动记录密码更新时间戳的方法

基础概念

在MySQL中记录密码更新时间戳是一个常见的需求，特别是在需要满足安全合规要求或审计追踪的场景中。这可以帮助管理员了解用户密码的最后修改时间，便于执行密码过期策略或安全审计。

实现方法

方法1：使用触发器

-- 首先创建一个用于存储密码修改时间的表
CREATE TABLE password_history (
    user_id VARCHAR(32) NOT NULL,
    password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES mysql.user(User) ON DELETE CASCADE
);

-- 创建触发器，在密码修改时记录时间戳
DELIMITER //
CREATE TRIGGER after_password_change
AFTER UPDATE ON mysql.user
FOR EACH ROW
BEGIN
    IF NEW.Password != OLD.Password THEN
        INSERT INTO password_history (user_id, password_changed_at)
        VALUES (NEW.User, NOW())
        ON DUPLICATE KEY UPDATE password_changed_at = NOW();
    END IF;
END//
DELIMITER ;

方法2：使用ALTER USER语句（MySQL 5.7.6+）

MySQL 5.7.6及以上版本支持在ALTER USER语句中直接设置密码过期时间：

-- 修改密码并设置过期时间
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 查看密码最后修改时间
SELECT user, password_last_changed FROM mysql.user WHERE user='username';

方法3：创建用户自定义表

-- 创建扩展用户表
CREATE TABLE user_metadata (
    username VARCHAR(32) NOT NULL,
    last_password_change TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (username)
);

-- 创建存储过程来修改密码并记录时间
DELIMITER //
CREATE PROCEDURE change_password(IN p_username VARCHAR(32), IN p_new_password VARCHAR(255))
BEGIN
    -- 修改密码
    SET @sql = CONCAT('ALTER USER ''', p_username, '''@''%'' IDENTIFIED BY ''', p_new_password, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 记录修改时间
    INSERT INTO user_metadata (username, last_password_change)
    VALUES (p_username, NOW())
    ON DUPLICATE KEY UPDATE last_password_change = NOW();
END//
DELIMITER ;

注意事项

对于触发器方法，需要有足够的权限修改mysql系统表
MySQL 8.0+中密码字段已从Password改为authentication_string
在生产环境中，考虑添加索引以提高查询效率
可能需要定期清理历史记录以避免表过大

应用场景

安全合规审计
密码过期策略实施
异常登录检测
用户行为分析

查询密码最后修改时间

-- 方法1的查询方式
SELECT * FROM password_history WHERE user_id='username';

-- 方法2的查询方式
SELECT user, password_last_changed FROM mysql.user WHERE user='username';

-- 方法3的查询方式
SELECT * FROM user_metadata WHERE username='username';

以上方法可以根据实际MySQL版本和业务需求选择最适合的实现方式。