How to Duplicate Rows in MySQL Without Violating Primary Key Constraints?

Duplicating Rows in MySQL Without Enumerating Columns

In MySQL, users encounter the need to create copies of existing rows within a table. While it's tempting to utilize a straightforward approach such as:

this method triggers a "Duplicate entry for key 1" error due to the uniqueness of primary keys. To circumvent this issue, a viable solution involves the creation of a temporary table:

However, a more streamlined approach exists, as suggested by Leonard Challis:

By utilizing a temporary table, we can avoid concerns about primary key duplication. Setting the primary key to null allows MySQL to automatically assign a unique value, eliminating the risk of creating a duplicate.

To ensure singularity during insertion, consider appending LIMIT 1 to the INSERT INTO statement.

Furthermore, it's recommended to incorporate the primary key value (e.g., "tmptable_1" in the example) into the temporary table name for clarity.

The above is the detailed content of How to Duplicate Rows in MySQL Without Violating Primary Key Constraints?. For more information, please follow other related articles on the PHP Chinese website!