ALTER 表 - 在 MySQL 中添加 AUTOINCREMENT

2021-11-27 00:00:00 sql mysql alter-table

我在 MySQL 中用 itemID 列创建了一个表.

I created a table in MySQL with on column itemID.

创建表格后，现在我想将此列更改为AUTOINCREMENT.

After creating the table, now I want to change this column to AUTOINCREMENT.

如何使用 ALTER 语句完成此操作?

表定义:

ALLITEMS (itemid int(10) unsigned, itemname varchar(50))

我正在使用以下代码，但它引发了语法错误

I am using the following code but it is throwing a syntax error

错误:语法不正确.

ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTOINCREMENT;

推荐答案

CREATE TABLE ALLITEMS(
    itemid INT(10)UNSIGNED,
    itemname VARCHAR(50)
);

ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY;

DESC ALLITEMS;

INSERT INTO ALLITEMS(itemname)
VALUES
    ('Apple'),
    ('Orange'),
    ('Banana');

SELECT
    *
FROM
    ALLITEMS;

我之前也对 CHANGE 和 MODIFY 关键字感到困惑:

I was confused with CHANGE and MODIFY keywords before too:

ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE ALLITEMS MODIFY itemid INT(5);

当我们在那里时，还要注意 AUTO_INCREMENT 也可以以预定义的数字开头:

While we are there, also note that AUTO_INCREMENT can also start with a predefined number:

ALTER TABLE tbl AUTO_INCREMENT = 100;

相关文章