MySQL 复制行但使用新 ID

Copy row but with new id

提问by Martin

I have a table "test" with an auto incremented idand an arbitrary number of columns.

我有一个自动递增的表“测试”id和任意数量的列。

I want to make a copy of a row in this table with all columns the same except for the idof course.

我想复制此表中的一行，所有列都相同id，当然除外。

Is there a way to do this without naming all columns?

有没有办法在不命名所有列的情况下做到这一点？

I thought INSERT... SELECT... ON DUPLICATE KEYwould help me until I realised that it never makes an INSERT ON DUPLICATE, it just updates the existing row.

我认为INSERT... SELECT... ON DUPLICATE KEY这会对我有所帮助，直到我意识到它永远不会生成INSERT ON DUPLICATE，它只会更新现有行。

回答by Ravinder Reddy

Let us say your table has following fields:

假设您的表具有以下字段：

( pk_id int not null auto_increment primary key,
  col1 int,
  col2 varchar(10)
)

then, to copy values from one row to the other row with new key value, following query may help

然后，要将值从一行复制到具有新键值的另一行，以下查询可能会有所帮助

insert into my_table( col1, col2 ) select col1, col2 from my_table where pk_id=?;

This will generate a new value for pk_idfield and copy values from col1, and col2of the selected row.

这将为pk_id字段生成一个新值，并从所选行的col1、 和复制值col2。

You can extend this sample to apply for more fields in the table.

您可以扩展此示例以申请表中的更多字段。

UPDATE:
In due respect to the comments from JohnP and Martin-

更新：
适当尊重 JohnP 和 Martin 的评论-

We can use temporary table to buffer first from main table and use it to copy to main table again. Mere update of pk reference field in temp table will not help as it might already be present in the main table. Instead we can drop the pk field from the temp table and copy all other to the main table.

我们可以使用临时表先从主表缓冲，然后再用它复制到主表。仅更新临时表中的 pk 参考字段将无济于事，因为它可能已经存在于主表中。相反，我们可以从临时表中删除 pk 字段并将所有其他字段复制到主表中。

With reference to the answer by Tim Ruehsenin the referred posting:

参考Tim Ruehsen在引用的帖子中的回答：

CREATE TEMPORARY TABLE tmp SELECT * from my_table WHERE ...;
ALTER TABLE tmp drop pk_id; # drop autoincrement field
# UPDATE tmp SET ...; # just needed to change other unique keys
INSERT INTO my_table SELECT 0,tmp.* FROM tmp;
DROP TEMPORARY TABLE tmp;

Hope this helps.

希望这可以帮助。

回答by rcalvachi

This works in MySQL all versions and Amazon RDS Aurora:

这适用于 MySQL 所有版本和 Amazon RDS Aurora：

INSERT INTO my_table SELECT 0,tmp.* FROM tmp;

or

或者

Setting the index column to NULL and then doing the INSERT.

将索引列设置为 NULL，然后执行 INSERT。

But not in MariaDB, I tested version 10.

但不是在 MariaDB 中，我测试了版本 10。

回答by UraniumSteel

THIS WORKS FOR DUPLICATING ONE ROW ONLY

这仅适用于复制一行

• Select your ONE row from your table
• Fetch all associative
• unset the ID row (Unique Index key)
• Implode the array[0] keys into the column names
• Implode the array[0] values into the column values
• Run the query

• 从表中选择一行
• 获取所有关联
• 取消设置 ID 行（唯一索引键）
• 将 array[0] 键内爆到列名中
• 将数组 [0] 值内爆为列值
• 运行查询

The code:

编码：

 $qrystr = "SELECT * FROM mytablename  WHERE id= " . $rowid;
 $qryresult = $this->connection->query($qrystr);
 $result = $qryresult->fetchAll(PDO::FETCH_ASSOC);
 unset($result[0]['id']); //Remove ID from array
 $qrystr = " INSERT INTO mytablename";
 $qrystr .= " ( " .implode(", ",array_keys($result[0])).") ";
 $qrystr .= " VALUES ('".implode("', '",array_values($result[0])). "')";
 $result = $this->connection->query($qrystr);
 return $result;

Of course you should use PDO:bindparam and check your variables against attack, etc but gives the example

当然，您应该使用 PDO:bindparam 并检查您的变量是否受到攻击等，但给出了示例

additional info

附加信息

If you have a problem with handling NULLvalues, you can use following codes so that implodingnames and values only for whose value is not NULL.

如果您在处理NULL值时遇到问题，您可以使用以下代码，以便imploding名称和值仅用于其值不是NULL.

foreach ($result[0] as $index => $value) {
    if ($value === null) unset($result[0][$index]);
}

回答by sukebe7

depending on how many columns there are, you could just name the columns, sans the ID, and manually add an ID or, if it's in your table, a secondary ID (sid):

根据有多少列，您可以只命名列，没有 ID，然后手动添加一个 ID，或者，如果它在您的表中，则添加一个辅助 ID (sid)：

insert into PROG(date, level, Percent, sid) select date, level, Percent, 55 from PROG where sid = 31Here, if sid 31 has more than one resultant row, all of them will be copied over to sid 55 and your auto iDs will still get auto-generated. for ID only: insert into PROG(date, level, Percent, ID) select date, level, Percent, 55 from PROG where ID = 31 where 55 is the next available ID in the table and ID 31 is the one you want to copy.

insert into PROG(date, level, Percent, sid) select date, level, Percent, 55 from PROG where sid = 31在这里，如果 sid 31 有多个结果行，所有这些都将被复制到 sid 55 并且您的自动 ID 仍将自动生成。仅用于 ID： insert into PROG(date, level, Percent, ID) select date, level, Percent, 55 from PROG where ID = 31 其中 55 是表中的下一个可用 ID，而 ID 31 是您要复制的 ID。

回答by Ashu

INSERT into table_name (  
    `product_id`, 
    `other_products_url_id`, 
    `brand`, 
    `title`, 
    `price`, 
    `category`, 
    `sub_category`, 
    `quantity`, 
    `buy_now`, 
    `buy_now_url`, 
    `is_available`, 
    `description`, 
    `image_url`, 
    `image_type`, 
    `server_image_url`, 
    `reviews`, 
    `hits`, 
    `rating`, 
    `seller_name`, 
    `seller_desc`, 
    `created_on`, 
    `modified_on`, 
    `status`) 
SELECT 
    `product_id`, 
    `other_products_url_id`, 
    `brand`, 
    `title`, 
    `price`, 
    `category`, 
    `sub_category`, 
    `quantity`, 
    `buy_now`, 
    concat(`buy_now_url`,'','#test123456'), 
    `is_available`, 
    `description`, 
    `image_url`, 
    `image_type`, 
    `server_image_url`, 
    `reviews`, 
    `hits`, 
    `rating`, 
    `seller_name`, 
    `seller_desc`, 
    `created_on`, 
    `modified_on`, 
    `status` 
FROM `table_name` WHERE id='YourRowID';