mysql 判断数据正确性

-- 创建表时添加唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

-- 查询是否存在重复数据
SELECT username
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

-- 创建表时添加外键约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 查询无效的外键引用
SELECT *
FROM orders
WHERE user_id NOT IN (SELECT user_id FROM users);

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE ValidateUser(
    IN p_username VARCHAR(50),
    OUT p_valid BOOLEAN
)
BEGIN
    SELECT COUNT(*) = 0 INTO p_valid
    FROM users
    WHERE username = p_username;
END //
DELIMITER ;

-- 调用存储过程
CALL ValidateUser('testuser', @valid);
SELECT @valid;