在MySQL中,我可以复制一行插入同一张表中吗?
insert into table select * from table where primarykey=1
我只想复制一行以插入到同一个表中(即,我想复制表中的现有行),但我想这样做而不必列出“select”之后的所有列,因为该表有太多的列。
但是当我这样做时,我收到错误:
密钥 1 的条目“xxx”重复
我可以通过创建另一个表来处理此问题,该表具有相同的列作为我要复制的记录的临时容器:
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;
是否有更简单的方法来解决此问题?
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
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(27)
这就是我在谷歌上发现的,因为我发现这很有帮助但有问题,所以我想为其他挖掘这个问题的人做出重要的修改。
首先,我使用的是 SQL Server,而不是 MySQL,但我认为它应该类似地工作。我使用了 Leonard Challis 的解决方案,因为它最简单并且满足了需求,但是这有一个问题 - 如果您只是简单地获取 PK 并将其增加 1,那么如果您在添加相关行后添加了其他记录,会发生什么情况。我决定最好让系统处理 PK 的自动增量,因此我执行了以下操作:
我相信这在 MySQL 中也能类似地工作,但我没有对此进行测试。
This is what I turned up with google and since I found this helpful but problematic I wanted to contribute an important modification for anyone else who digs this up.
First off, I'm using SQL Server, not MySQL, but I think it should work similarly. I used Leonard Challis' solution because it was simplest and met the need, however there's a problem with this - if you simply take the PK and increment it by 1 then what happens if you've added other records since the row in question was added. I decided it was best to just let the system handle the autoincrementing of the PK, so I did the following:
I believe this would work similarly in MySQL, but I didn't test this.
我会在下面使用,
I would use below,
我只需要这样做,这是我的手动解决方案:
如果您不知道 PRIMARY 字段是什么,请返回您的 phpmyadmin 页面,单击'Structure' 选项卡,然后单击在页面底部的'索引'下,它将显示哪个'字段'具有'Keyname'值'PRIMARY'< /强>。
有点绕,但如果您不想处理标记而只需要复制一行,那么您就可以了。
I just had to do this and this was my manual solution:
If you don't know what the PRIMARY field is, look back at your phpmyadmin page, click on the 'Structure' tab and at the bottom of the page under 'Indexes' it will show you which 'Field' has a 'Keyname' value 'PRIMARY'.
Kind of a long way around, but if you don't want to deal with markup and just need to duplicate a single row there you go.
只是想发布我的一段 PHP 代码,因为我认为我收集列的方式在代码中比前面的示例更干净一些。此外,这还展示了如何轻松更改字段,在本例中添加字符串。但是,如果您还想复制一些子记录,您也可以用新添加的记录替换外键字段。
Just wanted to post my piece of PHP code, because I think the way I collect the columns is a bit cleaner in code than the previous examples. Also this shows how you could easily alter an field, in this case adding a string. But you could also replace a foreign key field with the newly added record, in case you want to copy some child records as well.
上面显示的这个解决方案也适用于选定的行。例如,我正在为我的 Nice2work 项目创建演示行,这非常完美。
This solution showed above works perfect also for selected rows. For example I am creating demonstration rows for my nice2work project, and this works perfect.
我知道这是一个老问题,但这里有另一个解决方案:
假设主键是自动递增的,这会复制主表中的一行,并使用新的主表 ID 创建链接表数据的副本。
获取列名称的其他选项:
-显示
表名
中的列; (列名称:字段)-DESCRIBE
表名
(列名:字段)-SELECT column_name FROM information_schema.columns WHERE table_name = 'tablename' (列名称:column_name)
I know it's an old question, but here is another solution:
This duplicates a row in the main table, assuming the primary key is auto-increment, and creates copies of linked-tables data with the new main table id.
Other options for getting column names:
-SHOW COLUMNS FROM
tablename
; (Column name: Field)-DESCRIBE
tablename
(Column name: Field)-SELECT column_name FROM information_schema.columns WHERE table_name = 'tablename' (Column name: column_name)
创建一个表
插入一行
克隆行插入上面
测试
Create a table
Insert a row
Clone row insert above
Test
这是我在这个网站上在线找到的答案
描述如何执行上述操作1
您可以在页面底部找到答案。基本上,您所做的是将要复制的行复制到内存中保存的临时表中。然后,您可以使用 update 更改主键编号。然后将其重新插入到目标表中。然后你放下桌子。
这是它的代码:
我创建了临时表rescueteam。我从原始表 Fitnessreport4 中复制了该行。然后,我将临时表中的行的主键设置为 null,以便我可以将其复制回原始表,而不会出现重复键错误。我昨天晚上尝试了这段代码并且有效。
Here's an answer I found online at this site
Describes how to do the above1
You can find the answer at the bottom of the page. Basically, what you do is copy the row to be copied to a temporary table held in memory. You then change the Primary Key number using update. You then re-insert it into the target table. You then drop the table.
This is the code for it:
I created the temporary table rescueteam. I copied the row from my original table fitnessreport4. I then set the primary key for the row in the temporary table to null so that I can copy it back to the original table without getting a Duplicate Key error. I tried this code yesterday evening and it worked.
这是对“Grim...”答案的附加解决方案
有一些关于主键为空的评论。
关于它的一些评论不起作用。
以及对解决方案的一些评论。
这些解决方案都不适合我们。我们有带有 InnoDB 表的 MariaDB。
我们无法将主键设置为允许为空。
使用 0 而不是 NULL 会导致主键出现重复值错误。
SET SQL_SAFE_UPDATES = 0;
也不起作用。如果我们将主键更改为唯一,那么“严峻...”的解决方案确实有效
This is an additional solution to the answer by "Grim..."
There have been some comments on it having a primary key as null.
Some comments about it not working.
And some comments on solutions.
None of the solutions worked for us. We have MariaDB with the InnoDB table.
We could not set the primary key to allow null.
Using 0 instead of NULL led to duplicate value error for the primary key.
SET SQL_SAFE_UPDATES = 0;
Did not work either.The solution from "Grim..." did work IF we changed our PRIMARY KEY to UNIQUE instead
max233 肯定是在正确的轨道上,至少对于自动增量的情况来说是这样。
但是,不要执行 ALTER TABLE。只需在其中设置自动增量字段即可
临时表为NULL。这将出现错误,但以下 INSERT
临时表中的所有字段都会发生并且NULL auto字段将获得
独特的价值。
max233 was certainly on the right track, at least for the autoincrement case.
However, do not do the ALTER TABLE. Simply set the auto-increment field in the
temporary table to NULL. This will present an error, but the following INSERT of
all fields in the temporary table will happen and the NULL auto field will obtain
a unique value.
根据上面的答案以及 SO 上其他地方的答案,我将其作为克隆一条记录的最终代码:
将主键 Thing_ID 的值设置为 = 0 比更改临时表以允许 NULL 然后设置要短PRIMARY key 为 NULL - 对我来说,它在 MySQL 中有效。
Drawing from answers above, and elsewhere on SO, I came to this as my final code for cloning one record:
Setting the value of the PRIMARY key, the Thing_ID, to be = 0 is shorter than altering the temptable to allow NULL and then setting the PRIMARY key to NULL - and, for me, it works in MySQL.
如果表的主键字段是自增字段,则可以使用列查询。例如,名为
test_tbl
的表有 3 个字段:id、name、age
。id
是主键字段并且自动递增,因此您可以使用以下查询来复制行:此查询会导致复制每一行。
如果您的表的主键字段不是自动递增字段,则可以使用以下方法:
此查询的结果是插入
id=20< 的重复行
id=19
/代码>。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_tbl
has 3 fields asid, name, age
.id
is a primary key field and auto increment, so you can use the following query to duplicate the row: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:
The result of this query is a duplicate row of
id=19
inserted asid=20
.我使用了 Grim 的技术,做了一点改变:如果有人寻找这个查询是因为由于主键问题而无法执行简单的查询:
随着我的 MySql 安装 5.6.26,键不可为空并产生错误:
所以之后创建临时表我将主键更改为可为空。
I used Grim's technique with a little change: If someone looking for this query is because can't do a simple query due to primary key problem:
With my MySql install 5.6.26, key isn't nullable and produce an error:
So after create temporary table I change the primary key to a be nullable.
克隆具有更新字段和自动增量值的行
clone row with update fields and auto increment value
我在 Koha 数据库 中使用在条形码 列中插入带有“C”前缀的重复项目:
I used in my Koha database to insert duplicate items with the 'C' prefix in barcode column:
以下一些内容是从该网站收集的。这就是我在具有任意数量字段的表中复制记录的方法:
这还假设您在表的开头有一个 AI 字段
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
我可能会迟到,但我有一个对我有用的类似解决方案。
这样我就不需要创建临时表等。由于行被复制到同一个表中,因此可以轻松使用
Max(PK)+1
函数。我来寻找这个问题的解决方案(忘记了语法),最后我做了自己的查询。有时事情的进展很有趣。
问候
I might be late in this, but I have a similar solution which has worked for me.
This way I don't need to create a temporary table and etc. As the row is copied in the same table the
Max(PK)+1
function can be used easily.I came looking for the solution of this question (had forgotten the syntax) and I ended up making my own query. Funny how things work out some times.
Regards
如果主键是自增,则只需指定除主键外的各个字段即可。
插入表(字段1,字段2,字段3)
从表中选择(字段1,字段2,字段3)
WHERE 主键=1
If the Primary Key is Auto Increment, just specify each field except the primary key.
INSERT INTO table(field1,field2,field3)
SELECT (field1,field2,field3) FROM table
WHERE primarykey=1
我更新了@LeonardChallis 的解决方案,因为它对我不起作用,其他人也没有。我删除了临时表中的
WHERE
子句和SET PrimaryKey = 0
,这样MySQL就会自动增加primaryKey,这当然是复制all表中的行。
I updated @LeonardChallis's solution as it didn't work for me as none of the others. I removed the
WHERE
clauses andSET primaryKey = 0
in the temp table so MySQL auto-increments itself the primaryKeyThis is of course to duplicate all the rows in the table.
我使用了 Leonard Challis 的技术,并进行了一些更改:
作为临时表,永远不应该有超过一条记录,因此您不必担心主键。将其设置为 null 允许 MySQL 自行选择值,因此不存在创建重复项的风险。
如果您想非常确定只插入一行,可以将 LIMIT 1 添加到 INSERT INTO 行的末尾。
请注意,我还将主键值(在本例中为 1)附加到临时表名称中。
I used Leonard Challis's technique with a few changes:
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.
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.
Note that I also appended the primary key value (1 in this case) to my temporary table name.
更新 07/07/2014 - 基于我的答案(Grim...)的答案是一个更好的解决方案,因为它改进了我下面的解决方案,所以我建议使用它。
您可以使用以下语法来执行此操作,而无需列出所有列:
您可以决定以其他方式更改主键。
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.
You can do this without listing all the columns with the following syntax:
You may decide to change the primary key in another way.
我假设您希望新记录有一个新的
主键
?如果primarykey
是AUTO_INCRMENT
则只需执行以下操作:...其中
col1, col2, col3, ...
是表除了主键
。如果它不是
AUTO_INCRMENT
列,并且您希望能够为primarykey
选择新值,则类似:...其中
567
是primarykey
的新值。I'm assuming you want the new record to have a new
primarykey
? Ifprimarykey
isAUTO_INCREMENT
then just do this:...where
col1, col2, col3, ...
is all of the columns in the table except forprimarykey
.If it's not an
AUTO_INCREMENT
column and you want to be able to choose the new value forprimarykey
it's similar:...where
567
is the new value forprimarykey
.您几乎在第一个查询中就完成了,您只需要指定列,这样您就可以在插入中排除您的主键,这将在表上执行您可能拥有的自动增量,以自动为该表创建一个新的主键入口。
例如,将其更改为:
对此:
只需不在查询的 INSERT 或 SELECT 部分的列列表中包含主键列即可。
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:
To this:
Just don't include the primarykey column in either the column list for the INSERT or for the SELECT portions of the query.
您还可以尝试转储表,找到插入命令并对其进行编辑:
--skip-extended-insert
为您提供每行一个插入命令。然后,您可以在您最喜欢的文本编辑器中找到该行,提取命令并将主键更改为“默认”。You could also try dumping the table, finding the insert command and editing it:
The
--skip-extended-insert
gives 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".这可以通过一些创造性来实现:
这将导致新行获得自动递增的 id,而不是所选行的 id。
This can be achieved with some creativity:
This will result in the new row getting an auto incremented id instead of the id from the selected row.
此过程假设:
当然这并不完美,但在某些(可能是大多数)情况下它会起作用。
This procedure assumes that:
Of course this is not perfect, but in certain (probably most) cases it will work.