PHP MySQL 复制同一表中的一行...使用主键和唯一键

PHP MySQL Copy a row within the same table... with a Primary and Unique key

提问by Norse

My table has two keys, one is an auto incrementing id (PRIMARY), the other is the name of the item (UNIQUE).

我的表有两个键，一个是自动递增的 id (PRIMARY)，另一个是项目的名称 (UNIQUE)。

Is it possible to duplicate a row within this same table? I have tried:

是否可以在同一个表中复制一行？我试过了：

INSERT INTO items
SELECT * FROM items WHERE id = '9198'

This gives the error Duplicate entry '9198' for key 'PRIMARY'

这给出了错误 Duplicate entry '9198' for key 'PRIMARY'

I have also tried:

我也试过：

INSERT INTO items
SELECT * FROM items WHERE id = '9198'
ON DUPLICATE KEY UPDATE id=id+1

Which gives the error Column 'id' in field list is ambiguous

这给出了错误 Column 'id' in field list is ambiguous

And as far as the item name (UNIQUE) field goes, is there a way to append (Copy)to the item name, since this field must also be unique?

至于项目名称 (UNIQUE) 字段，有没有办法附加(Copy)到项目名称，因为该字段也必须是唯一的？

回答by Mark Byers

Select all columns explicitly, except the id column:

显式选择所有列，id 列除外：

INSERT INTO items
(col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM items
WHERE id = '9198'

Your next question will probably be:

你的下一个问题可能是：

Is there a way to do this without listing all the columns explicitly?

有没有办法在不明确列出所有列的情况下做到这一点？

Answer: No, I don't think so.

回答：不，我不这么认为。

回答by Phius

If you reallydon't want to list all the table columns like in Mark's answer, you can try this:

如果您真的不想像 Mark 的回答那样列出所有表格列，您可以试试这个：

CREATE TEMPORARY TABLE temp_tbl SELECT * FROM items WHERE id = '9198';
SELECT @maxId := MAX(id) + 1 FROM items;
UPDATE temp_tbl SET id = @maxId;
INSERT INTO items SELECT * FROM temp_tbl;
DROP TABLE temp_tbl;

Not beautiful, not fast. But works.

不漂亮，不快。但是有效。

回答by hobailey

Alternatively, if you don't want to write all the columns explicitly (and don't want to start creating/dropping tables), you can just get the columns of the table and build the query automagically:

或者，如果您不想显式写入所有列（并且不想开始创建/删除表），您可以只获取表的列并自动构建查询：

//get the columns
$cols=array();
$result = mysql_query("SHOW COLUMNS FROM [table]"); 
 while ($r=mysql_fetch_assoc($result)) {
  if (!in_array($r["Field"],array("[unique key]"))) {//add other columns here to want to exclude from the insert
   $cols[]= $r["Field"];
  } //if
}//while

//build and do the insert       
$result = mysql_query("SELECT * FROM [table] WHERE [queries against want to duplicate]");
  while($r=mysql_fetch_array($result)) {
    $insertSQL = "INSERT INTO [table] (".implode(", ",$cols).") VALUES (";
    $count=count($cols);
    foreach($cols as $counter=>$col) {
      $insertSQL .= "'".$r[$col]."'";
  if ($counter<$count-1) {$insertSQL .= ", ";}//dont want a , on the last one
    }//foreach
  $insertSQL .= ")";

  mysql_query($insertSQL);//execute the query
  }//while

Note that this uses the depreciated code of MySQL and it should be MySQLi. I'm sure it could also be improved, but it's what I'm using and it works very well.

注意这里使用的是 MySQL 的折旧代码，应该是 MySQLi。我相信它也可以改进，但这是我正在使用的，而且效果很好。

回答by Mark

Thanks to hobailey for providing a great maintenance-free solution.

感谢 hobailey 提供了出色的免维护解决方案。

Here is the code I ended up using, which is updated for MySQLi:

这是我最终使用的代码，它针对 MySQLi 进行了更新：

// Get the columns
$cols = array();
$result = $mysqli->query("SHOW COLUMNS FROM [TABLE]"); // Change table name

while ($r = $result->fetch_array(MYSQLI_ASSOC)) {
    if (!in_array($r["Field"], array("COLA", "COL4", "COL8"))) { // Edit array with any column names you want to exclude
        $cols[] = $r["Field"];
    }
}

// Build and do the insert
$result = $mysqli->query("SELECT * FROM [TABLE] WHERE [SELECTION CRITERIA];"); // Change table name and add selection criteria

while ($r = $result->fetch_array(MYSQLI_ASSOC)) {

    $insertSQL = "INSERT INTO [TABLE] (" . implode(", ",$cols) . ") VALUES ("; // Change table name
    $count = count($cols);

    foreach($cols as $counter=>$col) {
// This is where you can add any code to change the value of existing columns
        $insertSQL .= "'" . $mysqli->real_escape_string($r[$col]) . "'";
        if ($counter < ($count - 1)) {
            $insertSQL .= ", ";
        }
    } // END foreach

    $insertSQL .= ");";

    $mysqli->query($insertSQL);
    if ($mysqli->affected_rows < 1) {
// Add code if the insert fails
    } else {
// Add code if the insert is successful
    }

} // END while

回答by Olivier

The question title does state you want to do this from PHP.

问题标题确实说明您想从 PHP 执行此操作。

I've encountered the same problem and writing out all the column names is tedious and hard to maintain if you change your table structure (add/remove columns)... and I don't like the solutions that use temp tables.

我遇到了同样的问题，如果您更改表结构（添加/删除列），写出所有列名既乏味又难以维护……而且我不喜欢使用临时表的解决方案。

I've elected to solve this problem with two queries sent from PHP - works great and no maintenance required (disclaimer: I use the meekrodb library for database access):

我选择用从 PHP 发送的两个查询来解决这个问题 - 效果很好，不需要维护（免责声明：我使用 meekrodb 库进行数据库访问）：

//get the data as an associative array
$row = DB::queryFirstRow("SELECT * FROM your_table WHERE id=%i",$id);
if ($row){
    unset($row["id"]); //unset the primary key
    DB::insert("your_table",$row);
    return DB::insertId();
} else {
    return false;
}

You can even perform more manipulations on the internal data (unset other columns to ignore, edit values, etc) before re-inserting.

您甚至可以在重新插入之前对内部数据执行更多操作（取消将其他列设置为忽略、编辑值等）。

回答by Oliver-xx

Another solution in PHPfor copy a row in the same table without a specific column(s) / e.g. primary key - and without "TEMPORARY TABLE" and "SHOW COLUMNS FROM..."-method:

PHP 中的另一种解决方案，用于在没有特定列/例如主键的情况下复制同一表中的行 - 并且没有“TEMPORARY TABLE”和“SHOW COLUMNS FROM...”方法：

$stmt = $db->prepare("select * from table where id = :id;");
$stmt->bindValue(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
unset($row['id']);      //remove primary key

$columns = array_keys($row);
$query = "insert into table (`".implode('`, `', $columns)."`) select `".implode('`, `', $columns)."` from  data_ticket_serie where id = ".$_GET['id'].";";
// echo $query;
$stmt = $db->prepare($query);
$stmt->execute();

The INSERT is a SELECT-statement, so the values are not direct in the statement --> no problems with "real_escape_string" or something like that.

INSERT 是一个 SELECT 语句，因此语句中的值不是直接的 --> “real_escape_string”或类似的东西没有问题。

回答by Andy Gee

I had to do something similar recently so I thought I post my solution for any size table, example included. It just take a configuration array which can be adjusted to practically any size table.

我最近不得不做类似的事情，所以我想我发布了我的任何尺寸表的解决方案，包括示例。它只需要一个配置数组，可以调整到几乎任何大小的表。

$copy_table_row = array(
    'table'=>'purchase_orders',     //table name
    'primary'=>'purchaseOrderID',   //primary key (or whatever column you're lookin up with index)
    'index'=>4084,                  //primary key index number
    'fields' => array(
        'siteID',             //copy colunm
        ['supplierID'=>21],   //overwrite this column to arbirary value by wrapping it in an array
        'status',             //copy colunm
        ['notes'=>'copied'],  //changes to "copied"
        'dateCreated',        //copy colunm
        'approved',           //copy colunm
    ),
);
echo copy_table_row($copy_table_row);



function copy_table_row($cfg){
    $d=[];
    foreach($cfg['fields'] as $i => $f){
        if(is_array($f)){
            $d['insert'][$i] = "`".current(array_keys($f))."`";
            $d['select'][$i] = "'".current($f)."'";
        }else{
            $d['insert'][$i] = "`".$f."`";
            $d['select'][$i] = "`".$f."`";
        }
    }
    $sql = "INSERT INTO `".$cfg['table']."` (".implode(', ',$d['insert']).")
        SELECT ".implode(',',$d['select'])."
        FROM `".$cfg['table']."`
        WHERE `".$cfg['primary']."` = '".$cfg['index']."';";
    return $sql;
}

This will output something like:

这将输出如下内容：

INSERT INTO `purchase_orders` (`siteID`, `supplierID`, `status`, `notes`, `dateCreated`, `approved`)
SELECT `siteID`,'21',`status`,'copied',`dateCreated`,`approved`
FROM `purchase_orders`
WHERE `purchaseOrderID` = '4084';

回答by The Amerloc

I am surprised anyone didn't mention using phpMyAdmin to create the query. Because this would make it fast to add all the columns and then you just set the id to null or o as mentioned above by wlf.

我很惊讶有人没有提到使用 phpMyAdmin 创建查询。因为这样可以快速添加所有列，然后您只需将 id 设置为 null 或 o，如上所述 wlf。

This is by far the simplest way to do it

这是迄今为止最简单的方法

INSERT INTO users SELECT 0,email,user FROM users WHERE id=10

回答by rockdaboot

For tables with many columns, I use a (yes, slow) method similar to Phius idea.
I put it here just for completeness.

对于包含许多列的表，我使用类似于 Phius 想法的（是的，慢）方法。
我把它放在这里只是为了完整性。

Let's assume, table 'tbl' has an 'id' defined like

让我们假设，表 'tbl' 的 'id' 定义如下

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Then you can clone/copy a row by following these steps:

然后您可以按照以下步骤克隆/复制一行：

1. create a tmp table

1. 创建一个 tmp 表

CREATE TEMPORARY TABLE tbl_tmp LIKE tbl;

创建临时表 tbl_tmp LIKE tbl;

1. Insert one or more entries you want to clone / copy

1. 插入一个或多个要克隆/复制的条目

INSERT INTO tbl_tmp SELECT * FROM tbl WHERE ...;

INSERT INTO tbl_tmp SELECT * FROM tbl WHERE ...;

1. remove the AUTOINCREMENT tag from 'id'

1. 从“id”中删除 AUTOINCREMENT 标签

ALTER TABLE tbl_tmp MODIFY id INT;

更改表 tbl_tmp 修改 ID INT；

1. drop the primary index

1. 删除主索引

ALTER TABLE tbl_tmp DROP PRIMARY KEY;

更改表 tbl_tmp 删除主键；

1. update your unique indices and set 'id' to 0 (0 needed for step 6. to work)

1. 更新您的唯一索引并将“id”设置为 0（第 6 步需要 0 才能工作）

UPDATE tbl_tmp SET unique_value=?,id=0;

更新 tbl_tmp SET unique_value=?,id=0;

1. copy your modified rows into 'tbl' with 'id' being autogenerated.

1. 将修改后的行复制到 'tbl' 中，并自动生成 'id'。

INSERT INTO tbl SELECT * FROM tbl_tmp;

插入 tbl SELECT * FROM tbl_tmp;

1. cleanup (or just close the DB connection)

1. 清理（​​或只是关闭数据库连接）

DROP TABLE tbl_tmp;

删除表 tbl_tmp;

If you also need clone/copy some dependant data in other tables, do the above for each row. After step 6 you can get the last inserted key and use this to clone/copy the dependant rows within other tables using the same procedure.

如果您还需要克隆/复制其他表中的一些相关数据，请对每一行执行上述操作。在第 6 步之后，您可以获得最后插入的键，并使用相同的过程使用它来克隆/复制其他表中的相关行。

回答by wlf

Say the table is user(id,email,user)and because you have a WHEREclause you can't use MAX(id)+1:

说这个表是user(id,email,user)，因为你有一个WHERE你不能使用的子句MAX(id)+1：

INSERT INTO users SELECT 0,email,user FROM users WHERE id=10

Bear in mind though that you should always specify the column names when using INSERT.

但请记住，在使用 INSERT 时应始终指定列名。