blocks|key|2947041|text|语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2947042|+++ALTER+TABLE+`table1`+CHANGE+`itemId`+`itemId`+INT(+11+)+NOT+NULL+AUTO_INCREMENT+|code-block|syntax|javascript|2947043|但是该表需要一个已定义的键(itemId上的ex主键)。|2947044|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The syntax: 

<pre><code> ALTER TABLE `table1` CHANGE `itemId` `itemId` INT( 11 ) NOT NULL AUTO_INCREMENT 
</code></pre>

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

blocks|key|256663|text|ALTER+TABLE+`ALLITEMS`
++++CHANGE+COLUMN+`itemid`+`itemid`+INT(10)+UNSIGNED+NOT+NULL+AUTO_INCREMENT;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|256664|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER TABLE `ALLITEMS`
 CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
</code></pre>

blocks|key|258840|text|ALTER+TABLE+allitems
CHANGE+itemid+itemid+INT(10)+AUTO_INCREMENT;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|258841|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER TABLE allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;
</code></pre>

blocks|key|3160938|text|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;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3160939|我之前也把CHANGE和MODIFY关键字搞混了：|unstyled|offset|length|style|CODE|3160940|ALTER+TABLE+ALLITEMS+CHANGE+itemid+itemid+INT(10)AUTO_INCREMENT+PRIMARY+KEY;

ALTER+TABLE+ALLITEMS+MODIFY+itemid+INT(5);|3160941|同时，还要注意，AUTO_INCREMENT也可以从一个预定义的数字开始：|3160942|ALTER+TABLE+tbl+AUTO_INCREMENT+=+100;|3160943|entityMap^0|0|5|6|C|6|0|0|8|E|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@$G|U|H|V|I|J]|$G|W|H|X|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|Z|8|@$G|10|H|11|I|J]]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$B|C]]|$1|Q|3|-4|5|F|7|13|8|@]|9|@]|A|$]]]|R|$]]

<pre class="lang-sql prettyprint-override"><code>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;
</code></pre>

I was confused with <code>CHANGE</code> and <code>MODIFY</code> keywords before too:

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE ALLITEMS MODIFY itemid INT(5);
</code></pre>

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

<pre class="lang-sql prettyprint-override"><code>ALTER TABLE tbl AUTO_INCREMENT = 100;
</code></pre>

blocks|key|261963|text|ALTER+TABLE+tblcatalog
++++CHANGE+COLUMN+id+id+INT(11)+NOT+NULL+AUTO_INCREMENT+FIRST;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|261964|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER TABLE tblcatalog
 CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;
</code></pre>

blocks|key|256666|text|ALTER+TABLE+t_name+modify+c_name+INT(10)+AUTO_INCREMENT+PRIMARY+KEY;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|256667|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
</code></pre>

blocks|key|260655|text|向OP的现有表添加AUTO_INCREMENT主键的基本语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260656|ALTER+TABLE+allitems
MODIFY+itemid+INT(10)+UNSIGNED+AUTO_INCREMENT+PRIMARY+KEY;|code-block|syntax|javascript|260657|或者对于新表，下面是来自the+docs的语法示例|offset|length|260658|CREATE+TABLE+animals+(
+++++id+MEDIUMINT+NOT+NULL+AUTO_INCREMENT,
+++++name+CHAR(30)+NOT+NULL,
+++++PRIMARY+KEY+(id)
);|260659|陷阱和注意事项：|260660|260661|+AUTO_INCREMENT列上必须有索引。(通常，您会希望它是主键，但MySQL不要求这样做。)|unordered-list-item|style|CODE|260662|将AUTO_INCREMENT列设置为UNSIGNED通常是个好主意。在文档中:如果可能，使用UNSIGNED属性以允许更大的范围。|260663|260664|260665|当使用CHANGE或MODIFY子句创建列代码时(或者实际上，每当您使用CHANGE或MODIFY子句时)，您应该注意包含该列的所有修饰符，如NOT+NULL或UNSIGNED，这些修饰符在您调用<+AUTO_INCREMENT+>d21CHANGE>时出现在表定义中。否则这些修饰符将丢失。|260666|260667|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/en/example-auto-increment.html^0|0|0|C|8|0|0|0|0|0|1|E|0|1|E|J|8|0|0|0|3|6|A|6|10|6|17|6|1Z|8|28|8|2S|E|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1B|8|@]|9|@$I|1C|J|1D|1|1E]]|A|$]]|$1|K|3|L|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|1G|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1I|8|@$I|1J|J|1K|S|T]]|9|@]|A|$]]|$1|U|3|V|5|R|7|1L|8|@$I|1M|J|1N|S|T]|$I|1O|J|1P|S|T]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|R|7|1S|8|@$I|1T|J|1U|S|T]|$I|1V|J|1W|S|T]|$I|1X|J|1Y|S|T]|$I|1Z|J|20|S|T]|$I|21|J|22|S|T]|$I|23|J|24|S|T]|$I|25|J|26|S|T]]|9|@]|A|$]]|$1|10|3|-4|5|6|7|27|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|28|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]]]

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

<pre><code>ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY;
</code></pre>

Or for a new table, here's the syntax example from <a href="http://dev.mysql.com/doc/refman/en/example-auto-increment.html" rel="noreferrer">the docs</a>:

<pre><code>CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
);
</code></pre>

Traps and things to note:

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

<blockquote>
 Use the UNSIGNED attribute if possible to allow a greater range. 
</blockquote></li>
<li>When using a <code>CHANGE</code> or <code>MODIFY</code> clause to make a column <code>AUTO_INCREMENT</code> (or indeed whenever you use a <code>CHANGE</code> or <code>MODIFY</code> clause) you should be careful to include all modifiers for the column, like <code>NOT NULL</code> or <code>UNSIGNED</code>, that show up in the table definition when you call <code>SHOW CREATE TABLE yourtable</code>. These modifiers will be lost otherwise.</li>
</ul>

blocks|key|2947112|text|ALTER+TABLE+employees+id+int+AUTO_INCREMENT主键；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2947113|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

ALTER TABLE employees CHANGE id id int AUTO_INCREMENT PRIMARY KEY;

I created a table in MySQL with on column <code>itemID</code>.
After creating the table, now I want to change this column to <code>AUTOINCREMENT</code>.
How can this be done using ALTER statements?
Table definition:
<code>ALLITEMS (itemid int(10) unsigned, itemname varchar(50))</code>
I am using the following code but it is throwing a syntax error
Error: syntax incorrect.
<pre><code>ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTOINCREMENT; 
</code></pre>

ALTER table - adding AUTOINCREMENT in MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我用itemID列在MySQL中创建了一个表。在创建表之后，现在我想将此列更改为AUTOINCREMENT。如何使用ALTER语句来完成此操作表定义：ALLITEMS (itemid int(10) unsigned, itemname varchar(50))我使用了以下代码，但它抛出了一个语法错误错误:语法不正确的...

问ALTER table -在MySQL中添加自动增量
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ALTER table -在MySQL中添加自动增量EN