mysql表去重复

-- 创建一个新表，并插入去重后的数据
CREATE TABLE new_table AS
SELECT DISTINCT * FROM original_table;

-- 或者使用GROUP BY
CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM original_table
GROUP BY column1, column2, ...;

-- 删除重复行，保留id最小的行
DELETE t1 FROM original_table t1
JOIN original_table t2 
WHERE t1.id > t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;

-- 创建唯一索引
ALTER TABLE original_table ADD UNIQUE INDEX idx_unique_column (column1, column2);

-- 创建一个新表，并插入去重后的数据
CREATE TABLE users_unique AS
SELECT DISTINCT email, name FROM users;

-- 删除原表中的重复行，保留id最小的行
DELETE u1 FROM users u1
JOIN users u2 
WHERE u1.id > u2.id AND u1.email = u2.email;

-- 创建唯一索引防止插入重复数据
ALTER TABLE users ADD UNIQUE INDEX idx_unique_email (email);