Как исправить Duplicate entry for key в MySQL

 1-- Найди дублирующиеся значения в колонке
 2SELECT column_name, COUNT(*) as count
 3FROM table_name
 4GROUP BY column_name
 5HAVING COUNT(*) > 1;
 6
 7-- Найди конкретные дублирующиеся записи
 8SELECT * FROM table_name 
 9WHERE column_name IN (
10    SELECT column_name 
11    FROM table_name 
12    GROUP BY column_name 
13    HAVING COUNT(*) > 1
14)
15ORDER BY column_name;

 1-- Удали дубликаты, оставив только одну запись
 2DELETE t1 FROM table_name t1
 3INNER JOIN table_name t2 
 4WHERE t1.id > t2.id 
 5AND t1.column_name = t2.column_name;
 6
 7-- Альтернативный способ с ROW_NUMBER()
 8DELETE FROM table_name 
 9WHERE id NOT IN (
10    SELECT id FROM (
11        SELECT id, ROW_NUMBER() OVER (
12            PARTITION BY column_name ORDER BY id
13        ) as rn
14        FROM table_name
15    ) t WHERE t.rn = 1
16);

 1-- Проверь текущее значение автоинкремента
 2SELECT AUTO_INCREMENT 
 3FROM information_schema.TABLES 
 4WHERE TABLE_SCHEMA = 'database_name' 
 5AND TABLE_NAME = 'table_name';
 6
 7-- Исправь значение автоинкремента
 8ALTER TABLE table_name AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM table_name);
 9
10-- Или сбрось автоинкремент
11ALTER TABLE table_name AUTO_INCREMENT = 1;

 1# Python с MySQL Connector
 2import mysql.connector
 3from mysql.connector import Error
 4
 5def insert_with_duplicate_handling(data):
 6    try:
 7        conn = mysql.connector.connect(
 8            host='localhost', user='user', password='password', database='db'
 9        )
10        cursor = conn.cursor()
11        
12        sql = "INSERT INTO users (email, name) VALUES (%s, %s)"
13        cursor.execute(sql, (data['email'], data['name']))
14        conn.commit()
15        
16        return {'success': True, 'id': cursor.lastrowid}
17        
18    except Error as e:
19        if e.errno == 1062:  # Duplicate entry error
20            # Попробуй обновить существующую запись
21            update_sql = "UPDATE users SET name = %s WHERE email = %s"
22            cursor.execute(update_sql, (data['name'], data['email']))
23            conn.commit()
24            
25            return {'success': True, 'updated': True}
26        else:
27            return {'success': False, 'error': str(e)}
28    finally:
29        cursor.close()
30        conn.close()

 1-- MySQL UPSERT
 2INSERT INTO users (email, name, updated_at) 
 3VALUES ('user@example.com', 'John Doe', NOW())
 4ON DUPLICATE KEY UPDATE 
 5    name = VALUES(name),
 6    updated_at = NOW();
 7
 8-- Или используй REPLACE
 9REPLACE INTO users (email, name, created_at) 
10VALUES ('user@example.com', 'John Doe', NOW());

 1# duplicate_monitor.py
 2import mysql.connector
 3from datetime import datetime
 4
 5def check_duplicates_mysql(host, user, password, database, table, unique_columns):
 6    try:
 7        conn = mysql.connector.connect(
 8            host=host, user=user, password=password, database=database
 9        )
10        cursor = conn.cursor()
11        
12        results = {}
13        
14        for column in unique_columns:
15            cursor.execute(f"""
16                SELECT {column}, COUNT(*) as count
17                FROM {table}
18                GROUP BY {column}
19                HAVING COUNT(*) > 1
20            """)
21            
22            duplicates = cursor.fetchall()
23            results[column] = {
24                'duplicates': len(duplicates),
25                'details': duplicates
26            }
27        
28        cursor.close()
29        conn.close()
30        
31        return {
32            'table': table,
33            'duplicates': results,
34            'timestamp': datetime.now()
35        }
36    except Exception as e:
37        return {
38            'table': table,
39            'error': str(e),
40            'timestamp': datetime.now()
41        }
42
43# Мониторинг
44unique_columns = ['email', 'username', 'phone']
45
46status = check_duplicates_mysql(
47    'localhost', 'user', 'password', 'database', 'users', unique_columns
48)
49print(f"Duplicate check: {status}")

 1# auto_fix_duplicates.py
 2def fix_duplicates_mysql(host, user, password, database, table, column):
 3    try:
 4        conn = mysql.connector.connect(
 5            host=host, user=user, password=password, database=database
 6        )
 7        cursor = conn.cursor()
 8        
 9        # Найди дубликаты
10        cursor.execute(f"""
11            SELECT {column}, COUNT(*) as count
12            FROM {table}
13            GROUP BY {column}
14            HAVING COUNT(*) > 1
15        """)
16        
17        duplicates = cursor.fetchall()
18        
19        for duplicate in duplicates:
20            value, count = duplicate
21            
22            # Оставь только первую запись, удали остальные
23            cursor.execute(f"""
24                DELETE t1 FROM {table} t1
25                INNER JOIN {table} t2 
26                WHERE t1.id > t2.id 
27                AND t1.{column} = t2.{column}
28                AND t1.{column} = %s
29            """, (value,))
30        
31        conn.commit()
32        cursor.close()
33        conn.close()
34        
35        return {
36            'fixed_duplicates': len(duplicates),
37            'success': True
38        }
39    except Exception as e:
40        return {
41            'error': str(e),
42            'success': False
43        }