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

MySQL

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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