How to Clone a Row in MySQL without Explicitly Selecting Columns?

Cloning a Row in MySQL: Alternative to Explicit Column Selection

In MySQL, replicating a table row quickly and efficiently is often desired. The common approach involves extracting row data using "SELECT *" and inserting it back into the same table. However, this method becomes unwieldy with tables containing a large number of columns.

Leonard Challis proposed a clever solution that avoids the need to list all columns:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

This technique involves creating a temporary table to store the desired row. The primary key for this temporary table is set to null, allowing MySQL to generate a new value during insertion. To guarantee a single row is retrieved, "LIMIT 1" can be added to the INSERT INTO statement.

To further ensure that only the intended row is inserted, the primary key value should be appended to the temporary table name as seen in the code snippet. This technique simplifies the process of row copying without the need for explicit column selection, making it a valuable tool for managing large tables in MySQL.

The above is the detailed content of How to Clone a Row in MySQL without Explicitly Selecting Columns?. For more information, please follow other related articles on the PHP Chinese website!