ALTER 表——在 MySQL 中添加 AUTOINCREMENT

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

创建表之后，现在我要将该列更改为 AUTOINCREMENT。

如何使用 ALTER 语句 实现这一点？

表格定义:

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

我正在使用下面的代码，但它抛出了一个语法错误

错误: 语法不正确 。

ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTOINCREMENT;
371321 次浏览
小开

The syntax: 

   ALTER TABLE `table1` CHANGE `itemId` `itemId` INT( 11 ) NOT NULL AUTO_INCREMENT

But the table needs a defined key (ex primary key on itemId).

小开 
ALTER TABLE `ALLITEMS`
CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
小开 
ALTER TABLE allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;
小开
最佳答案 
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;

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);

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

ALTER TABLE tbl AUTO_INCREMENT = 100;
小开 
ALTER TABLE tblcatalog
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;
小开 
ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
小开

Basic syntax for adding an AUTO_INCREMENT PRIMARY KEY to the OP's existing table:

ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY;

Or for a new table, here's the syntax example from the docs:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

Traps and things to note:

  • An AUTO_INCREMENT column must have an index on it. (Usually, you'll want it to be the PRIMARY KEY, but MySQL does not require this.)
  • It's usually a good idea to make your AUTO_INCREMENT columns UNSIGNED. From the docs:

    Use the UNSIGNED attribute if possible to allow a greater range.

  • When using a CHANGE or MODIFY clause to make a column AUTO_INCREMENT (or indeed whenever you use a CHANGE or MODIFY clause) you should be careful to include all modifiers for the column, like NOT NULL or UNSIGNED, that show up in the table definition when you call SHOW CREATE TABLE yourtable. These modifiers will be lost otherwise.
小开

ALTER TABLE employees CHANGE id id int AUTO_INCREMENT PRIMARY KEY;


提交答案