What to do when duplicate primary keys occur when adding data in MySQL?

When duplicate primary keys occur while adding data in MySQL, it means you're trying to insert a record with a primary key value that already exists in the table. Since primary keys must be unique, MySQL will reject the insertion by default and throw an error like Duplicate entry 'value' for key 'PRIMARY'.

How to Handle It

Here are common strategies to deal with this issue:

---

1. Avoid Inserting Duplicates (Check Before Insert)

Before inserting, check if the primary key already exists. You can do this with a SELECT statement.

Example:

SELECT * FROM users WHERE id = 1;
-- If the result is empty, then insert
INSERT INTO users (id, name) VALUES (1, 'Alice');

This method is simple but not atomic. In concurrent environments, it may still lead to race conditions.

---

2. Use INSERT IGNORE

The INSERT IGNORE statement tells MySQL to ignore any errors during the insert operation, including duplicate key errors. If a duplicate primary key is encountered, the insert is skipped silently.

Example:

INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');

• If the primary key 1 already exists, the row is not inserted, and no error is thrown.
• This is useful when you want to insert multiple rows and skip duplicates without interrupting the whole operation.

---

3. Use ON DUPLICATE KEY UPDATE

This is one of the most flexible and commonly used approaches. If a row with the same primary key already exists, instead of inserting, it updates the existing row with new values.

Example:

INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = 'Alice', email = 'alice@example.com';

• If the primary key 1 does not exist, a new row is inserted.
• If it does exist, the name and email fields of the existing row are updated.

This is very useful for syncing or upserting (update or insert) data.

---

4. Replace Into (Use with Caution)

The REPLACE INTO statement first attempts to insert the row. If a duplicate primary key is found, it deletes the existing row and inserts the new one.

Example:

REPLACE INTO users (id, name) VALUES (1, 'Alice');

• Warning: This actually performs a DELETE followed by an INSERT, which can have side effects such as resetting auto-increment values or triggering ON DELETE constraints.

Use this only if you intend to fully replace the old row.

---

5. Handle in Application Logic

In your application code (e.g., Python, Java, Node.js), you can catch the duplicate key exception and decide how to proceed — whether to ignore, update, or notify the user.

For example, in Python with a MySQL driver:

try:
    cursor.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")
except mysql.connector.IntegrityError as e:
    if e.errno == 1062:  # Duplicate entry error code
        print("Duplicate primary key, skipping insert")
    else:
        raise e

---

When Using Cloud Database Services (e.g., TencentDB for MySQL)

If you are using TencentDB for MySQL, a managed MySQL database service on Tencent Cloud, the same principles apply. TencentDB offers high availability, automated backups, and scalability, which is suitable for production environments handling frequent read/write operations.

You can monitor and optimize your database performance via the Tencent Cloud Console, and use tools like TencentDB for MySQL Data Sync or Database Audit services to track data changes and avoid accidental duplicates.

Additionally, when performing bulk imports or migrations, consider using tools like mysqldump or Tencent Cloud Database Migration Service to handle primary key conflicts more gracefully during large-scale operations.

---

Recommendation:
For most use cases, ON DUPLICATE KEY UPDATE is the preferred method because it combines insert and update logic efficiently without needing separate queries or complex error handling. Use INSERT IGNORE if you simply want to skip duplicates, and avoid REPLACE INTO unless you explicitly need row replacement.