Duplicate entry for key 'PRIMARY' - How to solve MySQL error: Duplicate record of primary key

How to solve the MySQL error: duplicate primary key records, specific code examples are needed

When using MySQL database, we often encounter the problem of duplicate primary key records. When we insert a new piece of data into the database, if the primary key value of the data is the same as the primary key value of an existing record, a duplicate primary key record error will occur. The prompt for this error is usually "Duplicate entry for key 'PRIMARY'".

So, how to solve this problem? This article will provide several solutions and give specific code examples.

1. Use the ON DUPLICATE KEY UPDATE statement
   The ON DUPLICATE KEY UPDATE statement is a mechanism provided by MySQL to handle duplicate primary key records. When a primary key duplicate record error occurs when inserting data, you can use this statement to update existing records.

Sample code:

INSERT INTO table_name (id, name, age) VALUES (1, 'John', 30) 
ON DUPLICATE KEY UPDATE name = 'John', age = 30;

In the above example, we insert a piece of data into the table named table_name. If the primary key id of the data already exists, the UPDATE statement will be executed to update the corresponding The value of the record's name and age fields.

2. Use the INSERT IGNORE statement
   Another solution is to use the INSERT IGNORE statement. When this statement is inserting data, if a primary key duplicate record error occurs, the error will be ignored and the insertion operation will not be interrupted.

Sample code:

INSERT IGNORE INTO table_name (id, name, age) VALUES (1, 'John', 30);

In the above example, if the record with id 1 already exists, the error will be ignored and the insertion operation will continue.

3. Modify the primary key value
   If the primary key duplicate recording error occurs when data is manually inserted, we can modify the value of the primary key to avoid duplication.

Sample code:

SET @max_id = (SELECT MAX(id) FROM table_name);
INSERT INTO table_name (id, name, age) VALUES (@max_id + 1, 'John', 30);

In the above example, we query the maximum value of id in the current table and store it in the variable @max_id. Then, when inserting a piece of data, set the id to @max_id 1 to ensure that the id of the newly inserted record does not overlap with the existing record.

It should be noted that when modifying the primary key value, you need to operate with caution to ensure that other problems are not introduced.

Summary:
Duplicate primary key records are one of the common errors in MySQL. When solving this problem, you can use the ON DUPLICATE KEY UPDATE statement to update existing records, use the INSERT IGNORE statement to ignore duplicate record errors, or modify the primary key value to avoid duplication. Choose the right solution for your situation and adapt your code to your actual needs.

I hope the solutions and code examples provided in this article can help you solve the problem of MySQL error: duplicate primary key records.

The above is the detailed content of Duplicate entry for key 'PRIMARY' - How to solve MySQL error: Duplicate record of primary key. For more information, please follow other related articles on the PHP Chinese website!