将 MySQL 列更改为 AUTO_INCREMENT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2169080/
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
Alter a MySQL column to be AUTO_INCREMENT
提问by C. Ross
I'm trying to modify a table to make its primary key column AUTO_INCREMENTafter the fact. I have tried the following SQL, but got a syntax error notification.
我试图在AUTO_INCREMENT事后修改一个表以使其主键列。我尝试了以下 SQL，但收到了语法错误通知。
ALTER TABLE document
ALTER COLUMN document_id AUTO_INCREMENT
Am I doing something wrong or is this not possible?
我做错了什么还是这是不可能的？
+--------------------+ | VERSION() | +--------------------+ | 5.0.75-0ubuntu10.2 | +--------------------+
回答by roman
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment
回答by Roland Bouman
Roman is right, but note that the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY):
Roman 是对的，但请注意 auto_increment 列必须是 PRIMARY KEY 或 UNIQUE KEY 的一部分（并且在几乎 100% 的情况下，它应该是构成 PRIMARY KEY 的唯一列）：
ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY
回答by Rabia Naz khan
In my case it only worked when I put not null. I think this is a constraint.
在我的情况下，它只在我把not null. 我认为这是一个限制。
ALTER TABLE document MODIFY COLUMN document_id INT NOT NULL AUTO_INCREMENT;
回答by Steven Oxley
The SQL to do this would be:
执行此操作的 SQL 将是：
ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;
There are a couple of reasons that your SQL might not work. First, you must re-specify the data type (INTin this case). Also, the column you are trying to alter must be indexed (it does not have to be the primary key, but usually that is what you would want). Furthermore, there can only be one AUTO_INCREMENTcolumn for each table. So, you may wish to run the following SQL (if your column is not indexed):
您的 SQL 可能不起作用的原因有几个。首先，您必须重新指定数据类型（INT在本例中）。此外，您尝试更改的列必须被索引（它不必是主键，但通常这是您想要的）。此外，AUTO_INCREMENT每个表只能有一个列。因此，您可能希望运行以下 SQL（如果您的列未编入索引）：
ALTER TABLE `document` MODIFY `document_id` INT AUTO_INCREMENT PRIMARY KEY;
You can find more information in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/alter-table.htmlfor the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.htmlfor more information about specifying columns.
您可以在 MySQL 文档中找到更多信息：http: //dev.mysql.com/doc/refman/5.1/en/alter-table.html了解修改列语法和http://dev.mysql.com/doc /refman/5.1/en/create-table.html有关指定列的更多信息。
回答by H?vard S
You must specify the type of the column before the auto_incrementdirective, i.e. ALTER TABLE document MODIFY COLUMN document_id INT AUTO_INCREMENT.
您必须在auto_increment指令之前指定列的类型，即ALTER TABLE document MODIFY COLUMN document_id INT AUTO_INCREMENT.
回答by Dan Soap
AUTO_INCREMENTis part of the column's datatype, you have to define the complete datatype for the column again:
AUTO_INCREMENT是列数据类型的一部分，您必须再次为列定义完整的数据类型：
ALTER TABLE document
ALTER COLUMN document_id int AUTO_INCREMENT
(inttaken as an example, you should set it to the type the column had before)
（int例如，您应该将其设置为该列之前的类型）
回答by Aditya Kumar
You can do it like this:
你可以这样做：
alter table [table_name] modify column [column_name] [column_type] AUTO_INCREMENT;
回答by Azhar Zafar
You can use the following query to make document_id to increment automatically
您可以使用以下查询使 document_id 自动递增
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment
It is preferred to make document_id primary key as well
最好也制作 document_id 主键
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment PRIMARY KEY;
回答by KKK
AUTO_INCREMENTis part of the column's datatype, you have to define the complete datatype for the column again:
AUTO_INCREMENT是列数据类型的一部分，您必须再次为列定义完整的数据类型：
ALTER TABLE document
MODIFY COLUMN document_id int AUTO_INCREMENT
(int taken as an example, you should set it to the type the column had before)
（以int为例，您应该将其设置为该列之前的类型）
回答by Susie
If none of the above works try this. This is what I did in MYSQL and yes, you need to write the column name (document_id) twice.
如果以上都不起作用，请尝试此操作。这就是我在 MYSQL 中所做的，是的，您需要将列名 (document_id) 写入两次。
ALTER TABLE document
CHANGE COLUMN document_id document_id INT(11) NOT NULL AUTO_INCREMENT ;
