ALTER 表 - 在 MySQL 中添加 AUTOINCREMENT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6957370/
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 table - adding AUTOINCREMENT in MySQL
提问by Sumit Gupta
I created a table in MySQL with on column itemID. After creating the table, now I want to change this column to AUTOINCREMENT. How can this be done using ALTER statements?
我在 MySQL 中用 on column 创建了一个表itemID。创建表后，现在我想将此列更改为AUTOINCREMENT. 如何使用 ALTER 语句完成此操作？
Table definition:
表定义：
ALLITEMS (itemid int(10) unsigned, itemname varchar(50))
ALLITEMS (itemid int(10) unsigned, itemname varchar(50))
I am using the following code but it is throwing error: syntax incorrect.
我正在使用以下代码，但它抛出错误：语法不正确。
ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTOINCREMENT;
回答by ThinkCode
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 CHANGEand MODIFYkeywords before too:
我之前也对CHANGE和MODIFY关键字感到困惑：
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_INCREMENTcan also start with a predefined number:
当我们在那里时，还要注意也AUTO_INCREMENT可以以预定义的数字开头：
ALTER TABLE tbl AUTO_INCREMENT = 100;
回答by blejzz
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).
但是该表需要一个定义的键（例如 itemId 上的主键）。
回答by dee-see
ALTER TABLE `ALLITEMS`
CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
回答by Mark Amery
Basic syntax for adding an AUTO_INCREMENT PRIMARY KEY to the OP's existing table:
将 AUTO_INCREMENT PRIMARY KEY 添加到 OP 现有表的基本语法：
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_INCREMENTcolumn 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_INCREMENTcolumnsUNSIGNED. From the docs:Use the UNSIGNED attribute if possible to allow a greater range.
- When using a
CHANGEorMODIFYclause to make a columnAUTO_INCREMENT(or indeed wheneveryou use aCHANGEorMODIFYclause) you should be careful to include all modifiers for the column, likeNOT NULLorUNSIGNED, that show up in the table definition when you callSHOW CREATE TABLE yourtable. These modifiers will be lost otherwise.
- 一
AUTO_INCREMENT列必须有一个索引。（通常，您会希望它是 PRIMARY KEY，但 MySQL 不需要这个。） - 制作
AUTO_INCREMENT列通常是个好主意UNSIGNED。从文档：如果可能，请使用 UNSIGNED 属性以允许更大的范围。
- 当使用
CHANGEorMODIFY子句创建列时AUTO_INCREMENT（或者实际上无论何时使用CHANGEorMODIFY子句），您应该小心地包含该列的所有修饰符，例如NOT NULLorUNSIGNED，当您调用SHOW CREATE TABLE yourtable. 否则这些修饰符将丢失。
回答by Mark Amery
ALTER TABLE allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;
回答by Ajay Singh Rathore
ALTER TABLE tblcatalog
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;
回答by Rahul Yadav
ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
