在MySQL中复制记录 [英] Duplicate a record in MySQL
问题描述
我有一个表,我想复制表中的特定行.我知道这不是最好的处理方式,但我们正在寻找一种快速的解决方案.
I have a table and I want to duplicate specific rows in the table. I know this is not the best way to do things but we are looking for a quick solution.
这比我最初想的要难,我要做的就是将整个记录复制到MySql中自动增量表中的新记录,而无需指定每个字段.这是因为该表将来可能会更改,并且可能会中断重复.我将从PHP复制MySQL记录.
Here's something harder than I initially thought, all I need to do is copy an entire record to a new record in an auto-increment table in MySql without the need to specify each field. This is because the table can change in future and might break duplication. I will be duplicating MySQL records from PHP.
这是一个问题,因为MySql将在'SELECT *'查询中尝试复制要复制的记录的ID,这会产生重复的ID错误.
It is a problem because in a 'SELECT * ' query MySql will try to copy the ID of the record being copied which genenerates a duplicate ID error.
这可以阻止:
INSERT INTO customer SELECT * FROM customer WHERE customerid=9181
.它还会阻止INSERT INTO customer (Field1, Field2, ...) SELECT Field1, Field2, ..... FROM customer WHERE customerid=9181.
This blocks out:
INSERT INTO customer SELECT * FROM customer WHERE customerid=9181
. It also blocks out INSERT INTO customer (Field1, Field2, ...) SELECT Field1, Field2, ..... FROM customer WHERE customerid=9181.
是否可以通过PHP或MySQL做到这一点?
Is there a way to do this from PHP or MySQL?
推荐答案
我终于找到了这段代码.我相信它将对将来的人们有所帮助.所以就在这里.
I finally found this code. I am sure it will help people in the future. So here it is.
function DuplicateMySQLRecord ($table, $id_field, $id) {
// load the original record into an array
$result = mysql_query("SELECT * FROM {$table} WHERE {$id_field}={$id}");
$original_record = mysql_fetch_assoc($result);
// insert the new record and get the new auto_increment id
mysql_query("INSERT INTO {$table} (`{$id_field}`) VALUES (NULL)");
$newid = mysql_insert_id();
// generate the query to update the new record with the previous values
$query = "UPDATE {$table} SET ";
foreach ($original_record as $key => $value) {
if ($key != $id_field) {
$query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", ';
}
}
$query = substr($query,0,strlen($query)-2); # lop off the extra trailing comma
$query .= " WHERE {$id_field}={$newid}";
mysql_query($query);
// return the new id
return $newid;
}
这是文章 http://www.epigroove.com/posts/79的链接/how_to_duplicate_a_record_in_mysql_using_php
这篇关于在MySQL中复制记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!