MySQL 将自动递增 ID 添加到现有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14753321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add Auto-Increment ID to existing table?
提问by Charles Jenkins
I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter:
我有一个预先存在的表，包含“fname”、“lname”、“email”、“password”和“ip”。但现在我想要一个自动增量列。但是，当我输入时：
ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT
I get the following:
我得到以下信息：
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
Any advice?:)
有什么建议吗？:)
回答by Muhammad Asif Mahmood
Try this
尝试这个
ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
回答by Coderer
If you don't care whether the auto-id is used as PRIMARY KEY, you can just do
如果您不在乎是否将 auto-id 用作PRIMARY KEY，则可以这样做
ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;
I just did this and it worked a treat.
我只是这样做了，它起到了很好的作用。
回答by Bhaskar Bhatt
If you want to add AUTO_INCREMENT in an existing table, need to run following SQL command:
如果要在现有表中添加 AUTO_INCREMENT，需要运行以下 SQL 命令：
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key
回答by jrltt
First you have to remove the primary key of the table
首先你必须删除表的主键
ALTER TABLE nametable DROP PRIMARY KEY
and now yo can add the autoincrement ...
现在你可以添加自动增量......
ALTER TABLE nametable ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
回答by echo_Me
Well, you must first drop the auto_incrementand primary keyyou have and then add yours, as follows:
嗯，你必须首先删除auto_increment和primary key你有，然后添加你的，如下：
-- drop auto_increment capability
alter table `users` modify column id INT NOT NULL;
-- in one line, drop primary key and rebuild one
alter table `users` drop primary key, add primary key(id);
-- re add the auto_increment capability, last value is remembered
alter table `users` modify column id INT NOT NULL AUTO_INCREMENT;
回答by Rajat Dabade
If you run the following command :
如果您运行以下命令：
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT PRIMARY KEY;
This will show you the error :
这将向您显示错误：
ERROR 1060 (42S21): Duplicate column name 'id'
This is because this command will try to add the new column named idto the existing table.
这是因为此命令将尝试将命名的新列添加id到现有表中。
To modify the existing column you have to use the following command :
要修改现有列，您必须使用以下命令：
ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT PRIMARY KEY;
This should work for changing the existing column constraint....!
这应该适用于更改现有的列约束....！
回答by Sunny Verma
Delete the primary key of a table if it exists:
删除表的主键（如果存在）：
ALTER TABLE `tableName` DROP PRIMARY KEY;
Adding an auto-increment column to a table :
向表中添加自增列：
ALTER TABLE `tableName` ADD `Column_name` INT PRIMARY KEY AUTO_INCREMENT;
Modify the column which we want to consider as the primary key:
修改我们要考虑为主键的列：
alter table `tableName` modify column `Column_name` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
回答by John Joe
Just change the ADD to MODIFY and it will works !
只需将 ADD 更改为 MODIFY 即可！
Replace
代替
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT
To
到
ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT;
回答by Odhik Susanto
ALTER TABLE users CHANGE id int( 30 ) NOT NULL AUTO_INCREMENT
the integer parameter is based on my default sql setting have a nice day
整数参数基于我的默认 sql 设置祝你有美好的一天
回答by php
ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key FIRST
