插入具有相同唯一ID的多行 [英] Insert multiple rows with same unique ID

问题描述

我正在使用一个查询插入多行，很显然，ID列会自动增加每一行.我想创建另一个ID列，并使查询期间插入的所有行的ID保持不变.因此，如果我在一个查询期间插入10行，则希望所有10行的ID为"1".如何才能做到这一点?感谢您的帮助

I am inserting multiple rows using one query and, obviously, the ID column auto increments each row. I want to create another ID column and have the ID remain the same for all rows inserted during the query. So if I insert 10 rows during one query, I want all 10 rows to have the id "1". How can this be done? Thanks for any help

推荐答案

如果我正确理解了您的问题，则希望为特定的INSERT语句组提供ID.

If I understood your question correctly, you want to supply an ID for the specific group of INSERT statements.

假设您具有此架构

CREATE TABLE TableName
(
    RecordID INT AUTO_INCREMENT PRIMARY KEY,
    OtherColumn VARCHAR(25) NOT NULL,
    GroupID INT NOT NULL
)

您可以有两个声明:

1.)获取最后一个GroupID，并将其增加1.

1.) Getting the last GroupID and increment it by 1.

SELECT COALESCE(MAX(GroupID), 0) + 1 AS newGroupID FROM TableName

2.)执行后，将值存储在变量中.将此变量用于所有插入语句，

2.) once you have executed it, store the value in a variable. Use this variable for all the insert statement,

$groupID = row['newGroupID'];
$insert1 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('a', $groupID)";
$insert2 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('b', $groupID)";
$insert3 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('c', $groupID)";

更新1

• SQLFiddle演示

这篇关于插入具有相同唯一ID的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！