mysql 数据库重复数据

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_column ON table_name (column_name);

-- 删除重复数据
DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE t1.id > t2.id AND t1.column_name = t2.column_name;

-- 删除重复数据并保留一条记录
ALTER IGNORE TABLE table_name ADD UNIQUE INDEX idx_unique_column (column_name);

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- 插入重复数据
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', 'alice@example.com');

-- 删除重复数据并保留一条记录
ALTER IGNORE TABLE users ADD UNIQUE INDEX idx_unique_email (email);