mysql 重复记录

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

CREATE INDEX idx_username ON users(username);

import mysql.connector

def add_user(username, email):
    conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
    cursor = conn.cursor()
    
    # 检查记录是否已存在
    cursor.execute("SELECT * FROM users WHERE username = %s OR email = %s", (username, email))
    if cursor.fetchone():
        print("User already exists")
        return
    
    # 插入新记录
    cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))
    conn.commit()
    cursor.close()
    conn.close()

-- 删除完全重复的记录，保留id最小的记录
DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE t1.id > t2.id AND t1.username = t2.username AND t1.email = t2.email;

-- 删除部分重复的记录，例如删除重复的email，保留id最小的记录
DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE t1.id > t2.id AND t1.email = t2.email;