将自增主键插入现有表中

Question

我试图更改一张没有主键或自增列的表。我知道如何添加一个主键列，但我想知道是否可能自动插入数据到主键列中（我已经有了500行数据，想给它们分配id，但不想手动完成）。有什么想法吗？非常感谢。

- FlyingCat

---

你可以轻松地使用 alter table 命令添加键，但 MySQL 不会为没有 ID 的字段生成 ID。你需要手动更新现有的字段，然后确保新的自增值从正确的偏移量开始 - 它默认为“1”，否则你最终会遇到重复键错误。 - Marc B

@MarcB 我刚刚进行了测试，实际上它确实从1开始为现有行插入ID。 - Michael Berkowski

Answer 1

在我的测试中，添加了 PRIMARY KEY 列的 ALTER TABLE 语句能够正常工作：

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

在为测试目的创建的临时表中，上述语句创建了AUTO_INCREMENT id列，并为表中每个现有行插入自增值，从1开始计数。

- Michael Berkowski

---

是的，这也是我的经验。从来没有记得过需要运行单独的脚本来填充auto_increment ids... - Mike Purcell

哈哈，谢谢。是我不好，绿色标记是给你的，因为你的实现方式更简单。:D - FlyingCat

将其变为第一列，请使用FIRST，例如ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;。 - David Thomas

@Nikkie SQLite的机制非常不同。您可以在此处找到建议 https://dev59.com/rXNA5IYBdhLWcg3wdtz5 - Michael Berkowski

如果您使用自增方式创建了行并需要立即通过ID检索它，则MySQL提供了LAST_INSERT_ID()函数来获取该值，例如SELECT * FROM table_name WHERE id = LAST_INSERT_ID()。大多数编程语言API都提供了一些函数/方法来在应用程序代码中返回该值。 - Michael Berkowski

Answer 2

假设您没有像id、no这样的自动递增列，那么您可以使用以下查询语句添加：

假設您沒有像id、no這樣的自動遞增列，那麼您可以使用以下查詢語句添加：

ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

如果你有一个列，想要将其修改为自动递增，请使用以下查询语句：

 ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

- php

---

"FIRST" 最后是干什么用的？ - Rafael Herscovici

这将使新的“id”列成为表中第一列，而不是最后一列，这是默认行为。 - fmalina

工作得很好！谢谢！ - Majedur

Answer 3

为了将现有的主键设为auto_increment，您可以使用以下代码：

ALTER TABLE table_name MODIFY id INT AUTO_INCREMENT;

- Fathima Fasna

---

为避免“多个主键定义”错误，应使用此方法。 - IamVickyAV

Answer 4

对于像我一样遇到“Multiple primary key defined”错误的人，请尝试以下方法：

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;

在MySQL v5.5.31上，这将把id列设置为我的主键，并为每行填充一个递增的值。

- reflexiv

Answer 5

是的，这样的东西可以做到这一点，虽然可能不是最好的。您可能需要备份：

$get_query = mysql_query("SELECT `any_field` FROM `your_table`");

$auto_increment_id = 1;

while($row = mysql_fetch_assoc($get_query))
{
  $update_query = mysql_query("UPDATE `your_table` SET `auto_increment_id`=$auto_increment_id WHERE `any_field` = '".$row['any_field']."'");
  $auto_increment_id++;
}

请注意，更新时选择的any_field名称必须与之前选择的相同。

- Grigor

Answer 6

我能够根据以下代码，将一个具有现有非自增主键的表格添加一个自增主键，并创建一个新的复合主键，其中旧主键和新主键作为复合主键：

DROP TABLE  IF EXISTS SAKAI_USER_ID_MAP;

CREATE TABLE SAKAI_USER_ID_MAP (
       USER_ID             VARCHAR (99) NOT NULL,
       EID                 VARCHAR (255) NOT NULL,
       PRIMARY KEY (USER_ID)
);

INSERT INTO SAKAI_USER_ID_MAP VALUES ('admin', 'admin');
INSERT INTO SAKAI_USER_ID_MAP VALUES ('postmaster', 'postmaster');

ALTER TABLE  SAKAI_USER_ID_MAP 
  DROP PRIMARY KEY, 
  ADD _USER_ID INT AUTO_INCREMENT NOT NULL FIRST, 
  ADD PRIMARY KEY ( _USER_ID, USER_ID );

当完成此操作后，_USER_ID字段存在并具有与主键完全相同的所有数字值，就像您期望的那样。通过在顶部使用“DROP TABLE”，您可以一遍又一遍地运行它以尝试各种变化。

我无法使其正常工作的情况是，在已经指向USER_ID字段的外来FOREIGN KEY存在的情况下。当我尝试使用来自另一个表的更复杂的示例进行操作时，会出现此消息。

#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)

我猜在执行 ALTER table 命令之前需要先删除所有的外键, 然后再重新建立它们。如果其他人遇到同样的情况，现在我想分享一下解决原问题更具挑战性版本的方法。

- drchuck

Answer 7

我发现最简单和最快的方法是这样的：

ALTER TABLE mydb.mytable 
ADD COLUMN mycolumnname INT NOT NULL AUTO_INCREMENT AFTER updated,
ADD UNIQUE INDEX mycolumnname_UNIQUE (mycolumname ASC);

- Dave

Answer 8

没有现有的主键

ALTER TABLE `db`.`table` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);
;

表格已经存在一个主键列

(它不会删除旧的主键列)

ALTER TABLE `db`.`table` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
CHANGE COLUMN `prev_column` `prev_column` VARCHAR(2000) NULL ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`);
;

注意：列必须首先进行自动递增，这就是为什么要使用FIRST命令。

- Michael Kaufman

Answer 9

修改表 tableName 的主键 tableNameID，将数据类型改为 MEDIUMINT，并设置为非空和自增长。

这里的 tableName 是你的表名，

tableNameID 是需要修改的主键列名，

MEDIUMINT 是现有主键的数据类型，

在非空后添加 auto_increment 即可实现主键自增长......

希望这对你有所帮助：）

- Hrushikesh vanga

---

如果您能[编辑]并添加一些解释，那将非常有用。 - Shashanth

Answer 10

导出您的表格，然后清空您的表格，接着添加唯一的INT字段，然后将其更改为自动增量，最后再次导入之前导出的表格。

- David C