如何向现有列添加AUTO_INCREMENT ?

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

如何向MySQL表的现有列添加auto_increment ?

7 个解决方案

#1

I think you want to MODIFY the column as described for the ALTER TABLE command. It might be something like this:

我认为您希望按照ALTER TABLE命令的描述修改列。可能是这样的:

ALTER TABLE t1 MODIFY b INTEGER NOT NULL AUTO_INCREMENT;

#2

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

方法将AUTO_INCREMENT添加到带有数据的表中，同时避免“重复输入”错误:

Make a copy of the table with the data using INSERT SELECT:

使用INSERT SELECT复制表中的数据:
```
CREATE TABLE backupTable LIKE originalTable; 
INSERT backupTable SELECT * FROM originalTable;
```
Delete data from originalTable (to remove duplicate entries):

从原始数据中删除数据(删除重复条目):
```
TRUNCATE TABLE originalTable;
```
To add AUTO_INCREMENT and PRIMARY KEY

添加AUTO_INCREMENT和主键。
```
ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;
```
Copy data back to originalTable (do not include the newly created column (id), since it will be automatically populated)

将数据复制回原始表(不包括新创建的列(id)，因为它将自动填充)
```
INSERT originalTable (col1, col2, col3) 
SELECT col1, col2,col3
FROM backupTable;
```
Delete backupTable:

删除backupTable:
```
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表、索引和数据

#3

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中出现错误。

#4

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_incre-属性:1，这对我来说是可行的。将整个表导出为.sql文件2)删除导出2后的表)在CREATE_TABLE命令3中是否需要更改)。执行CREATE_TABLE和.sql文件中的INSERT_INTO命令…et中提琴

#5

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

只需在列中添加auto_increment约束或修改列:-

 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;

#6

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，如果需要可以跳过它。

#7

Alter table table_name modify table_name.column_name data_type AUTO_INCREMENT;

修改表table_name修改表_name。column_name data_type AUTO_INCREMENT;

eg:

例如:

Alter table avion modify avion.av int AUTO_INCREMENT;

改变航速改变航速。av int AUTO_INCREMENT;

#1