MySQL 如何将 AUTO_INCREMENT 添加到现有列？

How to add AUTO_INCREMENT to an existing column?

提问by mpen

How do I add auto_incrementto an existing column of a MySQL table?

如何添加auto_increment到 MySQL 表的现有列？

回答by Don Kirkby

I think you want to MODIFYthe column as described for the ALTER TABLEcommand. It might be something like this:

我认为您想要MODIFY按照ALTER TABLE命令中所述的列。它可能是这样的：

ALTER TABLE users MODIFY id INTEGER NOT NULL AUTO_INCREMENT;

Before running above ensure that idcolumn has a Primary index.

在上面运行之前，请确保该id列具有主索引。

回答by Arian Acosta

Method to add AUTO_INCREMENT to a table with datawhile avoiding “Duplicate entry” error:

将 AUTO_INCREMENT 添加到包含数据的表同时避免“重复条目”错误的方法：

1. Make a copy of the table withthe data using INSERT SELECT:

   CREATE TABLE backupTable LIKE originalTable; 
   INSERT backupTable SELECT * FROM originalTable;
   

2. Delete data from originalTable (to remove duplicate entries):

   TRUNCATE TABLE originalTable;
   

3. To add AUTO_INCREMENT andPRIMARY KEY

   ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;
   

4. Copy data back to originalTable (do notinclude the newly created column (id), since it will be automatically populated)

   INSERT originalTable (col1, col2, col3) 
   SELECT col1, col2,col3
   FROM backupTable;
   

5. Delete backupTable:

   DROP TABLE backupTable;
   

1. 使该表的副本与使用INSERT SELECT中的数据：

   CREATE TABLE backupTable LIKE originalTable; 
   INSERT backupTable SELECT * FROM originalTable;
   

2. 从 originalTable 中删除数据（删除重复条目）：

   TRUNCATE TABLE originalTable;
   

3. 添加 AUTO_INCREMENT和PRIMARY KEY

   ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;
   

4. 将数据复制回 originalTable（不包括新创建的列（id），因为它会被自动填充）

   INSERT originalTable (col1, col2, col3) 
   SELECT col1, col2,col3
   FROM backupTable;
   

5. 删除备份表：

   DROP TABLE backupTable;
   

I hope this is useful!

我希望这是有用的！

More on the duplication of tables using CREATE LIKE:

有关使用 CREATE LIKE 复制表的更多信息：

Duplicating a MySQL table, indexes and data

复制 MySQL 表、索引和数据

回答by php

Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

You should add primary key to auto increment, otherwise you got error in mysql.

您应该将主键添加到自动增量，否则您会在 mysql 中出错。

回答by Zigri2612

Simply just add auto_increment Constraint In column or MODIFY COLUMN :-

只需在列中添加 auto_increment Constraint 或 MODIFY COLUMN :-

 ALTER TABLE `emp` MODIFY COLUMN `id` INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;

Or add a column first then change column as -

或者先添加一列然后将列更改为 -

1. Alter TABLE `emp` ADD COLUMN `id`;

2. ALTER TABLE `emp` CHANGE COLUMN `id` `Emp_id` INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;

回答by walt

This worked for me in case you want to change the AUTO_INCREMENT-attribute for a not-empty-table:

1.)Exported the whole table as .sql file
2.)Deleted the table after export
2.)Did needed change in CREATE_TABLE command
3.)Executed the CREATE_TABLE and INSERT_INTO commands from the .sql-file
...et viola

如果您想更改非空表的 AUTO_INCREMENT 属性，这对我有用：

1.) 将整个表导出为 .sql 文件
2.) 导出后删除表
2.) 是否需要更改 CREATE_TABLE 命令
3.) 从 .sql 文件中执行 CREATE_TABLE 和 INSERT_INTO 命令
...et viola

回答by Fluch

I managed to do this with the following code:

我设法用以下代码做到了这一点：

ALTER TABLE `table_name`
CHANGE COLUMN `colum_name` `colum_name` INT(11) NOT NULL AUTO_INCREMENT FIRST;

This is the only way I could make a column auto increment.

这是我可以使列自动递增的唯一方法。

INT(11) shows that the maximum int length is 11, you can skip it if you want.

INT(11) 显示最大 int 长度为 11，您可以根据需要跳过它。

回答by Maria

Alter table table_name modify table_name.column_name data_type AUTO_INCREMENT;

Alter table table_name modify table_name.column_name data_type AUTO_INCREMENT;

eg:

例如：

Alter table avion modify avion.av int AUTO_INCREMENT;

Alter table avion modify avion.av int AUTO_INCREMENT;

回答by user4626745

if you have FK constraints and you don't want to remove the constraint from the table. use "index" instead of primary. then you will be able to alter it's type to auto increment

如果您有 FK 约束并且不想从表中删除约束。使用“索引”而不是主要的。然后您将能够将其类型更改为自动递增

回答by Gopal Mehakare

ALTER TABLE Table name ADD column datatype AUTO_INCREMENT,ADD primary key(column);