mysql密码明文

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password_hash CHAR(64) NOT NULL,
    salt CHAR(32) NOT NULL
);

-- 插入用户数据（假设使用SHA-256哈希算法）
DELIMITER //
CREATE PROCEDURE InsertUser(IN p_username VARCHAR(255), IN p_password VARCHAR(255))
BEGIN
    DECLARE v_salt CHAR(32);
    SET v_salt = LPAD(HEX(RAND()), 32, '0');
    INSERT INTO users (username, password_hash, salt)
    VALUES (p_username, SHA2(CONCAT(p_password, v_salt), 256), v_salt);
END //
DELIMITER ;

-- 调用存储过程插入用户
CALL InsertUser('testuser', 'testpassword');

-- 验证用户密码
DELIMITER //
CREATE PROCEDURE VerifyPassword(IN p_username VARCHAR(255), IN p_password VARCHAR(255))
BEGIN
    DECLARE v_salt CHAR(32);
    DECLARE v_password_hash CHAR(64);
    SELECT salt, password_hash INTO v_salt, v_password_hash FROM users WHERE username = p_username;
    IF v_password_hash = SHA2(CONCAT(p_password, v_salt), 256) THEN
        SELECT 'Password is correct';
    ELSE
        SELECT 'Password is incorrect';
    END IF;
END //
DELIMITER ;

-- 调用存储过程验证密码
CALL VerifyPassword('testuser', 'testpassword');