mysql删除相同数据

-- 创建示例表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

-- 插入重复数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Alice Duplicate', 'alice@example.com');

DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id < u2.id AND u1.email = u2.email;

-- 创建临时表
CREATE TEMPORARY TABLE temp_users AS
SELECT MIN(id) as id, email
FROM users
GROUP BY email;

-- 删除原表中的数据
DELETE FROM users WHERE id NOT IN (SELECT id FROM temp_users);

-- 将临时表的数据插入原表
INSERT INTO users (id, name, email)
SELECT id, name, email FROM temp_users;

-- 删除临时表
DROP TEMPORARY TABLE temp_users;