mysql 多表重复的数据

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查找重复数据
SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1;

-- 删除重复数据
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id < u2.id AND u1.username = u2.username AND u1.email = u2.email;

ALTER TABLE users ADD UNIQUE (username);
ALTER TABLE users ADD UNIQUE (email);

DELIMITER //

CREATE PROCEDURE InsertUser(IN p_username VARCHAR(255), IN p_email VARCHAR(255))
BEGIN
    DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
    
    INSERT INTO users (username, email)
    VALUES (p_username, p_email);
    
    IF SQLWARNING THEN
        SIGNAL duplicate_key SET MESSAGE_TEXT = 'Duplicate entry detected';
    END IF;
END //

DELIMITER ;