blocks|key|3122510|text|使用INSERT+...+SELECT|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3122511|insert+into+your_table+(c1,+c2,+...)
select+c1,+c2,+...
from+your_table
where+id+=+1|code-block|syntax|javascript|3122512|其中，c1,+c2,+...是除id之外的所有列。如果要显式插入时id为2，则将其包括在插入列列表和SELECT中：|3122513|insert+into+your_table+(id,+c1,+c2,+...)
select+2,+c1,+c2,+...
from+your_table
where+id+=+1|3122514|当然，在第二种情况下，您必须注意可能重复的id+2。|3122515|entityMap^0|2|H|0|0|3|B|G|2|X|2|0|0|L|2|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|R|$]]

Use <code>INSERT ... SELECT</code>:

<pre><code>insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1
</code></pre>

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

<pre><code>insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1
</code></pre>

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

blocks|key|2908154|text|在IMO中，最好只使用SQL语句来复制该行，同时只引用您必须和想要更改的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2908155|CREATE+TEMPORARY+TABLE+temp_table+ENGINE=MEMORY

SELECT+*+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;|code-block|syntax|javascript|2908156|另请参阅av8n.com+-+How+to+Clone+an+SQL+Record|offset|length|2908157|好处：|2908158|2908159|+SQL语句2只提到在克隆过程中需要更改的字段。他们不知道-或者不关心-其他领域。其他字段只是顺其自然，原封不动。这使得SQL语句更容易编写、更容易阅读、更容易维护，并且使用了更多的extensible.|unordered-list-item|2908160|Only普通MySQL语句。不需要其他工具或编程语言。|2908161|在一个原子操作中将完全正确的记录插入到your_table中。|style|CODE|2908162|2908163|entityMap|0|LINK|mutability|MUTABLE|url|http://www.av8n.com/computer/htm/clone-sql-record.htm#sec-nontrivial^0|0|0|4|11|0|0|0|0|0|0|J|A|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|17|8|@]|9|@$I|18|J|19|1|1A]]|A|$]]|$1|K|3|L|5|6|7|1B|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|R|5|P|7|1E|8|@]|9|@]|A|$]]|$1|S|3|T|5|P|7|1F|8|@$I|1G|J|1H|U|V]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|14]]]]

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.
<pre><code>CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY

SELECT * 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;
</code></pre>
See also <a href="http://www.av8n.com/computer/htm/clone-sql-record.htm#sec-nontrivial" rel="nofollow noreferrer">av8n.com - How to Clone an SQL Record</a>
Benefits:
<ul>
<li>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.</li>
<li>Only ordinary MySQL statements are used. No other tools or programming languages are required.</li>
<li>A fully-correct record is inserted in <code>your_table</code> in one atomic operation.</li>
</ul>

blocks|key|3122517|text|假设表是user(id,+user_name,+user_email)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3122518|您可以使用以下查询：|3122519|INSERT+INTO+user+(SELECT+NULL,user_name,+user_email+FROM+user+WHERE+id+=+1)|code-block|syntax|javascript|3122520|entityMap^0|4|V|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Say the table is <code>user(id, user_name, user_email)</code>.

You can use this query:

<pre><code>INSERT INTO user (SELECT NULL,user_name, user_email FROM user WHERE id = 1)
</code></pre>

blocks|key|240774|text|这很有帮助，它支持BLOB/文本列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|240775|CREATE+TEMPORARY+TABLE+temp_table
AS
SELECT+*+FROM+source_table+WHERE+id=2;
UPDATE+temp_table+SET+id=NULL+WHERE+id=2;
INSERT+INTO+source_table+SELECT+*+FROM+temp_table;
DROP+TEMPORARY+TABLE+temp_table;
USE+source_table;|code-block|syntax|javascript|240776|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This helped and it supports a BLOB/TEXT columns.

<pre><code>CREATE TEMPORARY TABLE temp_table
AS
SELECT * FROM source_table WHERE id=2;
UPDATE temp_table SET id=NULL WHERE id=2;
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;
USE source_table;
</code></pre>

blocks|key|2908175|text|对于不需要命名列的快速、干净的解决方案，您可以使用如下所述的预准备语句：https://stackoverflow.com/a/23964285/292677|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2908176|如果您需要一个复杂的解决方案以便经常执行此操作，则可以使用以下步骤：|2908177|DELIMITER+$$

CREATE+PROCEDURE+`duplicateRows`(_schemaName+text,+_tableName+text,+_whereClause+text,+_omitColumns+text)
SQL+SECURITY+INVOKER
BEGIN
++SELECT+IF(TRIM(_omitColumns)+<>+'',+CONCAT('id',+',',+TRIM(_omitColumns)),+'id')+INTO+@omitColumns;

++SELECT+GROUP_CONCAT(COLUMN_NAME)+FROM+information_schema.columns+
++WHERE+table_schema+=+_schemaName+AND+table_name+=+_tableName+AND+FIND_IN_SET(COLUMN_NAME,@omitColumns)+=+0+ORDER+BY+ORDINAL_POSITION+INTO+@columns;

++SET+@sql+=+CONCAT('INSERT+INTO+',+_tableName,+'(',+@columns,+')',
++'SELECT+',+@columns,+
++'+FROM+',+_schemaName,+'.',+_tableName,+'+',++_whereClause);

++PREPARE+stmt1+FROM+@sql;
++EXECUTE+stmt1;
END|code-block|syntax|javascript|2908178|您可以使用以下命令运行它：|2908179|CALL+duplicateRows('database',+'table',+'WHERE+condition+=+optional',+'omit_columns_optional');|2908180|示例|2908181|duplicateRows('acl',+'users',+'WHERE+id+=+200');+--+will+duplicate+the+row+for+the+user+with+id+200
duplicateRows('acl',+'users',+'WHERE+id+=+200',+'created_ts');+--+same+as+above+but+will+not+copy+the+created_ts+column+value++++
duplicateRows('acl',+'users',+'WHERE+id+=+200',+'created_ts,updated_ts');+--+same+as+above+but+also+omits+the+updated_ts+column
duplicateRows('acl',+'users');+--+will+duplicate+all+records+in+the+table|2908182|免责声明:此解决方案仅适用于经常在多个表中重复复制行的用户。它在一个流氓用户手中可能是危险的。|2908183|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/23964285/292677^0|10|17|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|19|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|C|$]]|$1|Q|3|R|5|H|7|1B|8|@]|9|@]|C|$I|J]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]]]

For a quick, clean solution that doesn't require you to name columns, you can use a prepared statement as described here:
<a href="https://stackoverflow.com/a/23964285/292677">https://stackoverflow.com/a/23964285/292677</a>

If you need a complex solution so you can do this often, you can use this procedure:

<pre><code>DELIMITER $$

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _whereClause text, _omitColumns text)
SQL SECURITY INVOKER
BEGIN
 SELECT IF(TRIM(_omitColumns) &lt;&gt; '', CONCAT('id', ',', TRIM(_omitColumns)), 'id') INTO @omitColumns;

 SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
 WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 ORDER BY ORDINAL_POSITION INTO @columns;

 SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ')',
 'SELECT ', @columns, 
 ' FROM ', _schemaName, '.', _tableName, ' ', _whereClause);

 PREPARE stmt1 FROM @sql;
 EXECUTE stmt1;
END
</code></pre>

You can run it with:

<pre><code>CALL duplicateRows('database', 'table', 'WHERE condition = optional', 'omit_columns_optional');
</code></pre>

Examples

<pre><code>duplicateRows('acl', 'users', 'WHERE id = 200'); -- will duplicate the row for the user with id 200
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts'); -- same as above but will not copy the created_ts column value 
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts,updated_ts'); -- same as above but also omits the updated_ts column
duplicateRows('acl', 'users'); -- will duplicate all records in the table
</code></pre>

DISCLAIMER: This solution is only for someone who will be repeatedly duplicating rows in many tables, often. It could be dangerous in the hands of a rogue user.

blocks|key|2908185|text|insert+into+MyTable(field1,+field2,+id_backup)
++++select+field1,+field2,+uniqueId+from+MyTable+where+uniqueId+=+@Id;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2908186|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>insert into MyTable(field1, field2, id_backup)
 select field1, field2, uniqueId from MyTable where uniqueId = @Id;
</code></pre>

blocks|key|239711|text|这里有很多很棒的答案。下面是我为正在开发的Web应用程序完成此任务而编写的存储过程示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|239712|--+SET+NOCOUNT+ON+added+to+prevent+extra+result+sets+from
--+interfering+with+SELECT+statements.
SET+NOCOUNT+ON

--+Create+Temporary+Table
SELECT+*+INTO+#tempTable+FROM+<YourTable>+WHERE+Id+=+Id

--To+trigger+the+auto+increment
UPDATE+#tempTable+SET+Id+=+NULL+

--Update+new+data+row+in+#tempTable+here!

--Insert+duplicate+row+with+modified+data+back+into+your+table
INSERT+INTO+<YourTable>+SELECT+*+FROM+#tempTable

--+Drop+Temporary+Table
DROP+TABLE+#tempTable|code-block|syntax|javascript|239713|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A lot of great answers here. Below is a sample of the stored procedure that I wrote to accomplish this task for a Web App that I am developing: 

<pre><code>-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- Create Temporary Table
SELECT * INTO #tempTable FROM &lt;YourTable&gt; WHERE Id = Id

--To trigger the auto increment
UPDATE #tempTable SET Id = NULL 

--Update new data row in #tempTable here!

--Insert duplicate row with modified data back into your table
INSERT INTO &lt;YourTable&gt; SELECT * FROM #tempTable

-- Drop Temporary Table
DROP TABLE #tempTable
</code></pre>

blocks|key|3122622|text|如果你能使用MySQL工作台，你可以右击该行并选择“复制行”，然后右击空行并选择“粘贴行”，然后更改ID，然后点击“应用”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3122623|复制该行：|3122624|​|3122625|📷|atomic|offset|length|3122626|3122627|将复制的行粘贴到空白行中：|3122628|3122629|3122630|3122631|更改ID：|3122632|3122633|3122634|3122635|申请：|3122636|3122637|3122638|3122639|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/d6b821ad372936cbf2a95135910a01f5.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/91c3081907fe7b74b83200aa66d182cb.png|2|https://ask.qcloudimg.com/http-save/yehe-900000/a930b196fa65a3922a6ab023cf439773.png|3|https://ask.qcloudimg.com/http-save/yehe-900000/c5db75033264b24cc5ad396a95e42a08.png^0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0|0|0|1|2|0|0|0|0|0|1|3|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1G|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1H|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1I|8|@]|9|@$I|1J|J|1K|1|1L]]|A|$]]|$1|K|3|E|5|6|7|1M|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1N|8|@]|9|@]|A|$]]|$1|N|3|E|5|6|7|1O|8|@]|9|@]|A|$]]|$1|O|3|G|5|H|7|1P|8|@]|9|@$I|1Q|J|1R|1|1S]]|A|$]]|$1|P|3|E|5|6|7|1T|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1U|8|@]|9|@]|A|$]]|$1|S|3|E|5|6|7|1V|8|@]|9|@]|A|$]]|$1|T|3|G|5|H|7|1W|8|@]|9|@$I|1X|J|1Y|1|1Z]]|A|$]]|$1|U|3|E|5|6|7|20|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|21|8|@]|9|@]|A|$]]|$1|X|3|E|5|6|7|22|8|@]|9|@]|A|$]]|$1|Y|3|G|5|H|7|23|8|@]|9|@$I|24|J|25|1|26]]|A|$]]|$1|Z|3|E|5|6|7|27|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|28|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17|18|-4]]|19|$5|13|14|15|A|$16|1A|18|-4]]|1B|$5|13|14|15|A|$16|1C|18|-4]]|1D|$5|13|14|15|A|$16|1E|18|-4]]]]

If you're able to use MySQL Workbench, you can do this by right-clicking the row and selecting 'Copy row', and then right-clicking the empty row and selecting 'Paste row', and then changing the ID, and then clicking 'Apply'.

Copy the row:

<a href="https://i.stack.imgur.com/4fdiP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4fdiP.png" alt="enter image description here"></a>

Paste the copied row into the blank row:

<a href="https://i.stack.imgur.com/TB1a9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TB1a9.png" alt="enter image description here"></a>

Change the ID:

<a href="https://i.stack.imgur.com/sS1k4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sS1k4.png" alt="enter image description here"></a>

Apply:

<a href="https://i.stack.imgur.com/fJgEA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fJgEA.png" alt="enter image description here"></a>

blocks|key|2908171|text|您还可以传入'0‘作为列的值，以便自动递增，创建记录时将使用正确的值。这比临时表要容易得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2908172|来源：Copying+rows+in+MySQL+(参见TRiG对第一个解决方案的第二个评论，作者是Lore)|offset|length|2908173|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/229998/copying-rows-in-mysql^0|0|3|L|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can also pass in '0' as the value for the column to auto-increment, the correct value will be used when the record is created. This is so much easier than temporary tables.

Source:
<a href="https://stackoverflow.com/questions/229998/copying-rows-in-mysql">Copying rows in MySQL</a> 
(see the second comment, by TRiG, to the first solution, by Lore)

blocks|key|240778|text|我倾向于使用mu太短的帖子的变体：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|240779|INSERT+INTO+something_log
SELECT+NULL,+s.*
FROM+something+AS+s
WHERE+s.id+=+1;|code-block|syntax|javascript|240780|只要这两个表具有相同的字段(除了日志表上的自动递增)，这就可以很好地工作。|240781|由于我尽可能地使用存储过程(为了使不太熟悉数据库的其他程序员的工作更轻松)，这解决了每次向表中添加新字段时都必须返回并更新过程的问题。|240782|它还确保当您向表中添加新字段时，它们将立即开始出现在日志表中，而不必更新数据库查询(当然，除非您有一些显式设置了字段的字段)|240783|警告：您需要确保同时向两个表中添加任何新字段，以便字段顺序保持不变...否则，你将开始得到奇怪的bug。如果你是唯一一个编写数据库接口的人，而且你非常小心，那么这个方法效果很好。否则，请坚持为所有字段命名。|offset|length|style|BOLD|240784|注意：再三考虑，除非您正在处理一个单独的项目，并且您确信不会有其他人参与其中，否则请坚持显式列出所有字段名称，并在您的模式更改时更新您的日志语句。这条捷径可能不值得它带来的长期头痛…尤其是在生产系统上。|240785|entityMap^0|0|0|0|0|0|0|3|0|0|3|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@$O|12|P|13|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@$O|15|P|16|Q|R]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

I tend to use a variation of what mu is too short posted:

<pre><code>INSERT INTO something_log
SELECT NULL, s.*
FROM something AS s
WHERE s.id = 1;
</code></pre>

As long as the tables have identical fields (excepting the auto increment on the log table), then this works nicely.

Since I use stored procedures whenever possible (to make life easier on other programmers who aren't too familiar with databases), this solves the problem of having to go back and update procedures every time you add a new field to a table.

It also ensures that if you add new fields to a table they will start appearing in the log table immediately without having to update your database queries (unless of course you have some that set a field explicitly)

Warning: You will want to make sure to add any new fields to both tables at the same time so that the field order stays the same... otherwise you will start getting odd bugs. If you are the only one that writes database interfaces AND you are very careful then this works nicely. Otherwise, stick to naming all of your fields.

Note: On second thought, unless you are working on a solo project that you are sure won't have others working on it stick to listing all field names explicitly and update your log statements as your schema changes. This shortcut probably is not worth the long term headache it can cause... especially on a production system.

blocks|key|2908285|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2908286|INSERT+INTO+test_table+(SELECT+null,txt+FROM+test_table)|code-block|syntax|javascript|2908287|每次运行此查询时，都会使用新ids再次插入所有行。值，并将呈指数级增长。|2908288|我使用的表有两列，即id和txt，id是自动递增的。|2908289|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Try this:
<pre><code>INSERT INTO test_table (SELECT null,txt FROM test_table)
</code></pre>
Every time you run this query, This will insert all the rows again with new ids. values in your table and will increase exponentially.
I used a table with two columns i.e id and txt and id is auto increment.

blocks|key|237167|text|我一直在寻找相同的功能，但我不使用MySQL。我想复制所有的字段，当然除了主键(id)。这是一个一次性查询，不能在任何脚本或代码中使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|237168|我找到了使用PL/SQL的方法，但我相信任何其他SQL+IDE都可以。我做了一个基本的|237169|SELECT+*+
FROM+mytable+
WHERE+id=42;|code-block|syntax|javascript|237170|然后将其导出到SQL文件，我可以在该文件中找到|237171|INSERT+INTO+table+(col1,+col2,+col3,+...+,+col42)+
VALUES+(1,+2,+3,+...,+42);|237172|我只是编辑并使用了它：|237173|INSERT+INTO+table+(col1,+col2,+col3,+...+,+col42)+
VALUES+(mysequence.nextval,+2,+3,+...,+42);|237174|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I was looking for the same feature but I don't use MySQL. I wanted to copy ALL the fields except of course the primary key (id). This was a one shot query, not to be used in any script or code.

I found my way around with PL/SQL but I'm sure any other SQL IDE would do. I did a basic

<pre><code>SELECT * 
FROM mytable 
WHERE id=42;
</code></pre>

Then export it to a SQL file where I could find the 

<pre><code>INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (1, 2, 3, ..., 42);
</code></pre>

I just edited it and used it :

<pre><code>INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (mysequence.nextval, 2, 3, ..., 42);
</code></pre>

blocks|key|2908291|text|insert+into+your_table(col1，col2，col3)+select+col1%2B1，col2，col3+from+your_table+where+col1=1；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2908292|注意:如果col1是主键，请确保递增后col1的新值不是重复条目。|2908293|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

insert into your_table(col1,col2,col3) select col1+1,col2,col3 from your_table where col1=1;
Note:make sure that after increment the new value of col1 is not duplicate entry if col1 is primary key.

blocks|key|2908295|text|CREATE+TEMPORARY++TABLE+IF+NOT+EXISTS++`temp_table`+LIKE+source_table;
DELETE+FROM+`purchasing2`+;
INSERT+INTO+temp_table+SELECT+*+FROM+source_table+where+columnid+=+2;
ALTER+TABLE+temp_table+MODIFY+id+INT+NOT+NULL;
ALTER+TABLE+temp_table+DROP+PRIMARY+KEY;
UPDATE+temp_table+SET+id=NULL+;
INSERT+INTO+source_table+SELECT+*+FROM+temp_table;
DROP+TEMPORARY++TABLE+IF+EXISTS++temp_table+;+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2908296|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE TEMPORARY TABLE IF NOT EXISTS `temp_table` LIKE source_table;
DELETE FROM `purchasing2` ;
INSERT INTO temp_table SELECT * FROM source_table where columnid = 2;
ALTER TABLE temp_table MODIFY id INT NOT NULL;
ALTER TABLE temp_table DROP PRIMARY KEY;
UPDATE temp_table SET id=NULL ;
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE IF EXISTS temp_table ; 
</code></pre>

blocks|key|240787|text|将您想要的行转储为SQL，然后使用生成的sql，去掉ID列将其重新导入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|240788|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Dump the row you want to sql and then use the generated SQL, less the ID column to import it back in.

In MySQL I am trying to copy a row with an autoincrement <code>column ID=1</code> and insert the data into same table as a new row with <code>column ID=2</code>.

How can I do this in a single query?

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

在MySQL中，我尝试使用autoincrement column ID=1复制一行，并使用 column ID=2将数据插入到与新行相同的表中。我如何在一个查询中做到这一点？

问如何在MySQL中复制一行并插入到具有自动增量字段的同一个表中？
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中复制一行并插入到具有自动增量字段的同一个表中？EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中复制一行并插入到具有自动增量字段的同一个表中？
EN