在 MySQL 中，我可以复制一行插入同一个表吗？

Question

提问by lina

insert into table select * from table where primarykey=1

I just want to copy one row to insert into the same table (i.e., I want to duplicate an existing row in the table) but I want to do this without having to list all the columns after the "select", because this table has too many columns.

我只想复制一行以插入到同一个表中（即，我想复制表中的现有行）但我想这样做而不必在“选择”之后列出所有列，因为该表具有列太多。

But when I do this, I get the error:

但是当我这样做时，我收到错误：

Duplicate entry 'xxx' for key 1

密钥 1 的重复条目“xxx”

I can handle this by creating another table with the same columns as a temporary container for the record I want to copy:

我可以通过创建另一个表来处理这个问题，该表的列与我要复制的记录的临时容器相同：

create table oldtable_temp like oldtable;
insert into oldtable_temp select * from oldtable where key=1;
update oldtable_tem set key=2;
insert into oldtable select * from oldtable where key=2;

Is there a simpler way to solve this?

有没有更简单的方法来解决这个问题？

Answer 1

回答by Grim...

I used Leonard Challis's technique with a few changes:

我使用了 Leonard Challis 的技术并进行了一些更改：

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;

As a temp table, there should never be more than one record, so you don't have to worry about the primary key. Setting it to null allows MySQL to choose the value itself, so there's no risk of creating a duplicate.

作为临时表，记录永远不会超过一条，因此您不必担心主键。将其设置为 null 允许 MySQL 自己选择值，因此不存在创建重复项的风险。

If you want to be super-sure you're only getting one row to insert, you could add LIMIT 1 to the end of the INSERT INTO line.

如果你想非常确定你只插入一行，你可以将 LIMIT 1 添加到 INSERT INTO 行的末尾。

Note that I also appended the primary key value (1 in this case) to my temporary table name.

请注意，我还将主键值（在本例中为 1）附加到我的临时表名。

Answer 2

回答by LeonardChallis

Update 07/07/2014 - The answer based on my answer, by Grim..., is a better solution as it improves on my solution below, so I'd suggest using that.

2014 年 7 月 7 日更新 - 基于我的答案的答案，由Grim...是一个更好的解决方案，因为它改进了我下面的解决方案，所以我建议使用它。

You can do this without listing all the columns with the following syntax:

您可以在不使用以下语法列出所有列的情况下执行此操作：

CREATE TEMPORARY TABLE tmptable SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable SET primarykey = 2 WHERE primarykey = 1;
INSERT INTO table SELECT * FROM tmptable WHERE primarykey = 2;

You may decide to change the primary key in another way.

您可以决定以另一种方式更改主键。

Answer 3

回答by Jordan Running

I'm assuming you want the new record to have a new primarykey? If primarykeyis AUTO_INCREMENTthen just do this:

我假设您希望新记录有一个新的primarykey? 如果primarykey是AUTO_INCREMENT那么就这样做：

INSERT INTO table (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM table
  WHERE primarykey = 1

...where col1, col2, col3, ...is all of the columns in the table except forprimarykey.

...col1, col2, col3, ...表中除primarykey.之外的所有列都在哪里？

If it's not an AUTO_INCREMENTcolumn and you want to be able to choose the new value for primarykeyit's similar:

如果它不是一AUTO_INCREMENT列，并且您希望能够为其选择新值，primarykey则类似：

INSERT INTO table (primarykey, col2, col3, ...)
SELECT 567, col2, col3, ... FROM table
  WHERE primarykey = 1

...where 567is the new value for primarykey.

...567的新值在哪里primarykey？

Answer 4

回答by Braeden Black

You almost had it with the your first query you just need to specify the columns, that way you can exclude your primary key in the insert which will enact the auto-increment you likely have on the table to automatically create a new primary key for the entry.

您几乎可以在第一个查询中获得它，您只需要指定列，这样您就可以在插入中排除主键，这将启用您可能在表中拥有的自动增量，以自动为入口。

For example change this:

例如改变这个：

insert into table select * from table where primarykey=1

To this:

对此：

INSERT INTO table (col1, col2, col3) 
SELECT col1, col2, col3 
FROM table 
WHERE primarykey = 1

Just don't include the primarykey column in either the column list for the INSERT or for the SELECT portions of the query.

只是不要在 INSERT 或查询的 SELECT 部分的列列表中包含主键列。

Answer 5

回答by donothingsuccessfully

You could also try dumping the table, finding the insert command and editing it:

您还可以尝试转储表，找到插入命令并对其进行编辑：

mysqldump -umyuser -p mydatabase --skip-extended-insert mytable > outfile.sql

The --skip-extended-insertgives you one insert command per row. You may then find the row in your favourite text editor, extract the command and alter the primary key to "default".

该--skip-extended-insert给你每行一个INSERT命令。然后，您可以在您喜欢的文本编辑器中找到该行，提取命令并将主键更改为“默认”。

Answer 6

回答by Piotr Kaczmarek

This procedure assumes that:

此过程假设：

you don't have _duplicate_temp_table
your primary key is int
you have access to create table

你没有 _duplicate_temp_table
你的主键是 int
您有权创建表

Of course this is not perfect, but in certain (probably most) cases it will work.

当然这并不完美，但在某些（可能是大多数）情况下它会起作用。

DELIMITER $$
CREATE PROCEDURE DUPLICATE_ROW(copytable VARCHAR(255), primarykey VARCHAR(255), copyid INT, out newid INT)
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error=1;
        SET @temptable = '_duplicate_temp_table';
        SET @sql_text = CONCAT('CREATE TABLE ', @temptable, ' LIKE ', copytable);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('INSERT INTO ', @temptable, ' SELECT * FROM ', copytable, ' where ', primarykey,'=', copyid);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('SELECT max(', primarykey, ')+1 FROM ', copytable, ' INTO @newid');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('UPDATE ', @temptable, ' SET ', primarykey, '=@newid');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('INSERT INTO ', copytable, ' SELECT * FROM ', @temptable, '');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @sql_text = CONCAT('DROP TABLE ', @temptable);
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT @newid INTO newid;
END $$
DELIMITER ;

CALL DUPLICATE_ROW('table', 'primarykey', 1, @duplicate_id);
SELECT @duplicate_id;

Answer 7

回答by user2595171

If your table's primary key field is an auto increment field, then you can use query with columns. For example, your table named test_tblhas 3 fields as id, name, age. idis a primary key field and auto increment, so you can use the following query to duplicate the row:

如果您的表的主键字段是自动增量字段，那么您可以使用列查询。例如，您命名的表test_tbl有 3 个字段为id, name, age。id是主键字段和自动增量，因此您可以使用以下查询来复制行：

INSERT INTO `test_tbl` (`name`,`age`) SELECT `name`,`age` FROM `test_tbl`;

This query results in duplicating every row.

此查询导致复制每一行。

If your table's primary key field is not an auto increment field, then you can use the following method:

如果你的表的主键字段不是自增字段，那么你可以使用以下方法：

INSERT INTO `test_tbl` (`id`,`name`,`age`)
  SELECT 20,`name`,`age` FROM `test_tbl` WHERE id = 19;

The result of this query is a duplicate row of id=19inserted as id=20.

此查询的结果是id=19insert as的重复行id=20。

Answer 8

回答by curmil

This can be achieved with some creativity:

这可以通过一些创造力来实现：

SET @sql = CONCAT('INSERT INTO <table> SELECT null, 
    ', (SELECT GROUP_CONCAT(COLUMN_NAME) 
    FROM information_schema.columns 
    WHERE table_schema = '<database>' 
    AND table_name = '<table>' 
    AND column_name NOT IN ('id')), ' 
from <table> WHERE id = <id>');  

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

This will result in the new row getting an auto incremented id instead of the id from the selected row.

这将导致新行获得自动递增的 id，而不是来自所选行的 id。

Answer 9

回答by Shrinivas Mahindrakar

clone row with update fields and auto increment value

带有更新字段和自动增量值的克隆行

CREATE TEMPORARY TABLE `temp` SELECT * FROM `testing` WHERE id = 14;

UPDATE `temp` SET id = (SELECT id FROM testing ORDER by id DESC LIMIT 1
 )+1, user_id = 252 ,policy_no = "mysdddd12" where id = 14;

INSERT INTO `testing` SELECT * FROM `temp`;

DROP TEMPORARY TABLE IF EXISTS `temp`;

Answer 10

回答by richardwhitney

Some of the following was gleaned off of this site. This is what I did to duplicate a record in a table with any number of fields:

以下部分内容来自本网站。这是我在具有任意数量字段的表中复制记录的操作：

This also assumes you have an AI field at the beginning of the table

这也假设您在表的开头有一个 AI 字段

function duplicateRow( $id = 1 ){
dbLink();//my db connection
$qColumnNames = mysql_query("SHOW COLUMNS FROM table") or die("mysql error");
$numColumns = mysql_num_rows($qColumnNames);

for ($x = 0;$x < $numColumns;$x++){
$colname[] = mysql_fetch_row($qColumnNames);
}

$sql = "SELECT * FROM table WHERE tableId = '$id'";
$row = mysql_fetch_row(mysql_query($sql));
$sql = "INSERT INTO table SET ";
for($i=1;$i<count($colname)-4;$i++){//i set to 1 to preclude the id field
//we set count($colname)-4 to avoid the last 4 fields (good for our implementation)
$sql .= "`".$colname[$i][0]."`  =  '".$row[$i]. "', ";
}
$sql .= " CreateTime = NOW()";// we need the new record to have a new timestamp
mysql_query($sql);
$sql = "SELECT MAX(tableId) FROM table";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);
return $row[0];//gives the new ID from auto incrementing
}

在 MySQL 中，我可以复制一行插入同一个表吗？

提问by lina

回答by Grim...

回答by LeonardChallis

回答by Jordan Running

回答by Braeden Black

回答by donothingsuccessfully

回答by Piotr Kaczmarek

回答by user2595171

回答by curmil

回答by Shrinivas Mahindrakar

回答by richardwhitney

相关推荐

最近更新

标签

在 MySQL 中，我可以复制一行插入同一个表吗？

提问by lina

回答by Grim...

回答by LeonardChallis

回答by Jordan Running

回答by Braeden Black

回答by donothingsuccessfully

回答by Piotr Kaczmarek

回答by user2595171

回答by curmil

回答by Shrinivas Mahindrakar

回答by richardwhitney

相关推荐

MySQL 我可以在单个 Amazon RDS 实例上创建多少个数据库

mysql 日期时间比较

MySQL 如何使用phpmyadmin将Excel文件导入mysql数据库

如何通过多个连接加速 MySQL 查询

相关推荐

最近更新

标签