mysql去除重复记录

-- 创建一个临时表来存储唯一的email
CREATE TEMPORARY TABLE temp_users AS
SELECT DISTINCT email
FROM users;

-- 清空原表
TRUNCATE TABLE users;

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

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

-- 创建一个临时表来存储唯一的email
CREATE TEMPORARY TABLE temp_emails AS
SELECT DISTINCT email
FROM (
    SELECT email FROM users
    UNION
    SELECT email FROM orders
) AS combined;

-- 清空原表
TRUNCATE TABLE users;
TRUNCATE TABLE orders;

-- 将临时表中的数据插入回原表
INSERT INTO users (email)
SELECT email FROM temp_emails WHERE email IN (SELECT email FROM users);

INSERT INTO orders (email)
SELECT email FROM temp_emails WHERE email IN (SELECT email FROM orders);

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