How to copy a row and insert in same table with a autoincrement field in MySQL?

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)select c1, c2, ...from your_tablewhere id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)select 2, c1, c2, ...from your_tablewhere id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

IMO, the best seems to use sql statements only to copy that row, while at the same time only referencing the columns you must and want to change.

CREATE TEMPORARY TABLE temp_table ENGINE=MEMORYSELECT * FROM your_table WHERE id=1;UPDATE temp_table SET id=0; /* Update other values at will. */INSERT INTO your_table SELECT * FROM temp_table;DROP TABLE temp_table;

See also av8n.com - How to Clone an SQL Record

Benefits:

• The SQL statements 2 mention only the fields that need to be changed during the cloning process. They do not know about – or care about – other fields. The other fields just go along for the ride, unchanged. This makes the SQL statements easier to write, easier to read, easier to maintain, and more extensible.
• Only ordinary MySQL statements are used. No other tools or programming languages are required.
• A fully-correct record is inserted in your_table in one atomic operation.

Say the table is user(id, user_name, user_email).

You can use this query:

INSERT INTO user (SELECT NULL,user_name, user_email FROM user WHERE id = 1)