mysql 密码更改记录

CREATE TABLE password_change_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(50) NOT NULL,
    old_password VARCHAR(255) NOT NULL,
    new_password VARCHAR(255) NOT NULL,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE PROCEDURE change_password_with_history(
    IN p_user VARCHAR(50),
    IN p_new_password VARCHAR(255)
)
BEGIN
    DECLARE old_password VARCHAR(255);
    
    -- 获取当前密码
    SELECT password INTO old_password FROM mysql.user WHERE User = p_user;
    
    -- 更新密码
    UPDATE mysql.user SET password = PASSWORD(p_new_password) WHERE User = p_user;
    
    -- 插入更改记录
    INSERT INTO password_change_history (user, old_password, new_password)
    VALUES (p_user, old_password, p_new_password);
    
    -- 刷新权限
    FLUSH PRIVILEGES;
END //

DELIMITER ;

SELECT * FROM password_change_history;