mysql重复数据的行数据

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

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

-- 删除重复数据并保留一条记录
DELETE t1 FROM table_name t1
JOIN table_name t2 
WHERE t1.id > t2.id AND t1.column_name = t2.column_name;

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

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

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

-- 删除重复数据并保留一条记录
DELETE t1 FROM users t1
JOIN users t2 
WHERE t1.id > t2.id AND t1.email = t2.email;