将MySQL列更改为AUTO_INCREMENT

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after 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 |
+--------------------+

16 个解决方案

#1

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

#2

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列必须是主键或唯一键的一部分(在几乎100%的情况下，它应该是组成主键的唯一列):

ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY

#3

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;

#4

AUTO_INCREMENT is 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

(int taken as an example, you should set it to the type the column had before)

(以int为例，应该将它设置为列之前的类型)

#5

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 (INT in 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_INCREMENT column 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.html for the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.html for 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/createtable.html”。

#6

You must specify the type of the column before the auto_increment directive, i.e. ALTER TABLE document MODIFY COLUMN document_id INT AUTO_INCREMENT.

必须在auto_increment指令之前指定列的类型，即ALTER TABLE document修改列document_id INT auto_increment。

#7

AUTO_INCREMENT is 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为例，应该将它设置为列之前的类型)

#8

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 ;

#9

Below statement works. Note that you need to mention the data type again for the column name

下面的语句。注意，您需要再次提到数据类型以获取列名。

ALTER TABLE document
MODIFY COLUMN document_id int AUTO_INCREMENT;

#10

To modify the column in mysql we use alter and modify keywords. Suppose we have created a table like:

要修改mysql中的列，我们使用alter和modify关键字。假设我们创建了这样一个表:

create table emp(
    id varchar(20),
    ename varchar(20),
    salary float
);

Now we want to modify type of the column id to integer with auto increment. You could do this with a command like:

现在，我们希望将列id的类型修改为具有自动增量的整数。您可以使用如下命令:

alter table emp modify column id int(10) auto_increment;

#11

Previous Table syntax:

以前的表的语法:

CREATE TABLE apim_log_request (TransactionId varchar(50) DEFAULT NULL);

For changing the TransactionId to auto increment use this query

要将TransactionId更改为自动增量，请使用此查询

ALTER TABLE apim_log_request MODIFY COLUMN TransactionId INT auto_increment;

#12

You can try like this:

你可以这样尝试:

 alter table [table_name] modify column [column_name] [column_type] AUTO_INCREMENT;

#13

alter table tbl_user MODIFY COLUMN id int(10) auto_increment;

#14

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;

#15

As you are redefining the column again, you have to specify the datatype again and add auto_increment to it as it's a part of datatype.

当您重新定义该列时，必须再次指定数据类型，并将auto_increment添加到数据类型中，因为它是数据类型的一部分。

ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;

#16

Use the following queries:

使用以下查询:

ALTER TABLE YourTable DROP COLUMN IDCol

ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)