blocks|key|449884|text|在我的测试中，添加PRIMARY+KEY列的ALTER+TABLE语句工作正常：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|449885|ALTER+TABLE+tbl+ADD+id+INT+PRIMARY+KEY+AUTO_INCREMENT;|code-block|syntax|javascript|449886|在为测试目的而创建的临时表上，上面的语句创建了AUTO_INCREMENT+id列，并为表中的每个现有行插入了从1开始的自动增量值。|449887|entityMap^0|9|B|M|B|0|0|N|E|12|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

An <code>ALTER TABLE</code> statement adding the <code>PRIMARY KEY</code> column works correctly in my testing:

<pre><code>ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;
</code></pre>

On a temporary table created for testing purposes, the above statement created the <code>AUTO_INCREMENT</code> <code>id</code> column and inserted auto-increment values for each existing row in the table, starting with 1.

blocks|key|664459|text|假设你没有像id，no这样的自动增量列，那么你可以使用下面的查询来添加：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|664460|ALTER+TABLE+table_name+ADD+id+int+NOT+NULL+AUTO_INCREMENT+primary+key+FIRST|code-block|syntax|javascript|664461|如果您有列，则使用以下查询更改为自动递增：|664462|+ALTER+TABLE+table_name+MODIFY+column_name+datatype(length)+AUTO_INCREMENT+PRIMARY+KEY|664463|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

suppose you don't have column for auto increment like id, no, then you can add using following query:

<pre><code>ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST
</code></pre>

If you've column, then alter to auto increment using following query:

<pre><code> ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY
</code></pre>

blocks|key|450212|text|对于像我这样遇到Multiple+primary+key+defined错误的人，请尝试：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|450213|ALTER+TABLE+`myTable`+ADD+COLUMN+`id`+INT+AUTO_INCREMENT+UNIQUE+FIRST+NOT+NULL;|code-block|syntax|javascript|450214|在MySQL+v5.5.31上，这将id列设置为我的主键，并用递增的值填充每一行。|450215|entityMap^0|8|S|0|0|I|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

For those like myself getting a <code>Multiple primary key defined</code> error try:

<pre><code>ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;
</code></pre>

On MySQL v5.5.31 this set the <code>id</code> column as the primary key for me and populated each row with an incrementing value.

blocks|key|449946|text|是的，像这样的东西可以做到，但可能不是最好的，你可能想做一个备份|type|unstyled|depth|inlineStyleRanges|entityRanges|data|449947|$get_query+=+mysql_query("SELECT+`any_field`+FROM+`your_table`");

$auto_increment_id+=+1;

while($row+=+mysql_fetch_assoc($get_query))
{
++$update_query+=+mysql_query("UPDATE+`your_table`+SET+`auto_increment_id`=$auto_increment_id+WHERE+`any_field`+=+'".$row['any_field']."'");
++$auto_increment_id%2B%2B;
}|code-block|syntax|javascript|449948|请注意，在更新时，您选择的any_field必须相同。|offset|length|style|CODE|449949|entityMap^0|0|0|D|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

yes, something like this would do it, might not be the best though, you might wanna make a backup

<pre><code>$get_query = mysql_query("SELECT `any_field` FROM `your_table`");

$auto_increment_id = 1;

while($row = mysql_fetch_assoc($get_query))
{
 $update_query = mysql_query("UPDATE `your_table` SET `auto_increment_id`=$auto_increment_id WHERE `any_field` = '".$row['any_field']."'");
 $auto_increment_id++;
}
</code></pre>

notice that the the <code>any_field</code> you select must be the same when updating.

blocks|key|664503|text|我找到的最简单最快的就是这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|664504|ALTER+TABLE+mydb.mytable+
ADD+COLUMN+mycolumnname+INT+NOT+NULL+AUTO_INCREMENT+AFTER+updated,
ADD+UNIQUE+INDEX+mycolumnname_UNIQUE+(mycolumname+ASC);|code-block|syntax|javascript|664505|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The easiest and quickest I find is this

<pre><code>ALTER TABLE mydb.mytable 
ADD COLUMN mycolumnname INT NOT NULL AUTO_INCREMENT AFTER updated,
ADD UNIQUE INDEX mycolumnname_UNIQUE (mycolumname ASC);
</code></pre>

blocks|key|450709|text|为了将现有的主键设置为auto_increment，您可以使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|450710|ALTER+TABLE+table_name+MODIFY+id+INT+AUTO_INCREMENT;|code-block|syntax|javascript|450711|entityMap^0|B|E|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

In order to make the existing primary key as <code>auto_increment</code>, you may use:
<pre><code>ALTER TABLE table_name MODIFY id INT AUTO_INCREMENT;
</code></pre>

blocks|key|664539|text|导出表，然后清空表，然后添加字段作为唯一的INT，然后将其更改为AUTO_INCREMENT，然后再次导入之前导出的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|664540|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|$]]

Export your table, then empty your table, then add field as unique INT, then change it to AUTO_INCREMENT, then import your table again that you exported previously.

blocks|key|664676|text|您可以使用以下命令向现有表中添加新的主键列，该表可以具有序列号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|664677|ALTER+TABLE+mydb.mytable+ADD+pk_columnName+INT+IDENTITY+|code-block|syntax|javascript|664678|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can add a new Primary Key column to an existing table, which can have sequence numbers, using command: 

<pre><code>ALTER TABLE mydb.mytable ADD pk_columnName INT IDENTITY 
</code></pre>

blocks|key|450557|text|我遇到了同样的问题，所以我删除了主键的字段，然后重新创建了它，并确保它是自动递增的。这对我很管用。我希望它能帮助其他人。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|450558|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|$]]

I was facing the same problem so what I did I dropped the field for the primary key then I recreated it and made sure that it is auto incremental . That worked for me . I hope it helps others

blocks|key|450610|text|有多种方法可以做到这一点：-if如果您的表中没有任何数据，只需删除它并重新创建即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|450611|删除现有字段并重新创建它，如下所示|450612|ALTER+TABLE+test+DROP+PRIMARY+KEY,+DROP+test_id,+ADD+test_id+int+AUTO_INCREMENT+NOT+NULL+FIRST,+ADD+PRIMARY+KEY+(test_id);|code-block|syntax|javascript|450613|或者只是修改它|450614|ALTER+TABLE+test+MODIFY+test_id+INT+AUTO_INCREMENT+NOT+NULL,+ADD+PRIMARY+KEY+(test_id);|450615|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Well, you have multiple ways to do this:
-if you don't have any data on your table, just drop it and create it again.
Dropping the existing field and creating it again like this
<pre><code>ALTER TABLE test DROP PRIMARY KEY, DROP test_id, ADD test_id int AUTO_INCREMENT NOT NULL FIRST, ADD PRIMARY KEY (test_id);
</code></pre>
Or just modify it
<pre><code>ALTER TABLE test MODIFY test_id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (test_id);
</code></pre>

blocks|key|450441|text|如何编写PHP来修改已经存在的字段(在本例中为name)，使其成为主键？当然，W/o，将任何额外的'id‘字段添加到表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|450442|这是一个当前创建的表-找到的记录数:4+name+VARCHAR(20)+YES+breed+(30)+YES+color+VARCHAR(20)+YES+weight+SMALLINT(7)+YES|450443|这是寻求的最终结果(表描述)-|450444|找到的记录数:4+name+VARCHAR(20)+NO+PRI+breed+VARCHAR(30)+YES+color+VARCHAR(20)+YES+weight+SMALLINT(7)+YES|450445|而不是得到这个-|450446|找到的记录数:5+id+int(11)+NO+PRI+name+VARCHAR(20)+YES+breed+VARCHAR(30)+YES+color+VARCHAR(20)+YES+weight+SMALLINT(7)+YES|450447|在尝试之后..。|450448|$query+=+"ALTER+TABLE+racehorses+id+INT+NOT+NULL+AUTO_INCREMENT+FIRST，ADD+PRIMARY+KEY+(id)"；|450449|怎么弄到这个？|450450|找到的记录数:4+name+VARCHAR(20)+NO+PRI+breed+VARCHAR(30)+YES+color+VARCHAR(20)+YES+weight+SMALLINT(7)+YES|450451|即插入/添加..等等，将主键添加到第一字段记录中(如前所述，没有添加附加的'id‘字段。|450452|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|14|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|16|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|17|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|W|$]]

How to write PHP to ALTER the already existing field (name, in this example) to make it a primary key? W/o, of course, adding any additional 'id' fields to the table..

This a table currently created - Number of Records found: 4 name VARCHAR(20) YES
breed VARCHAR(30) YES
color VARCHAR(20) YES
weight SMALLINT(7) YES

This an end result sought (TABLE DESCRIPTION) - 

Number of records found: 4 
name VARCHAR(20) NO PRI
breed VARCHAR(30) YES
color VARCHAR(20) YES
weight SMALLINT(7) YES

Instead of getting this - 

Number of Records found: 5
id int(11) NO PRI
name VARCHAR(20) YES
breed VARCHAR(30) YES
color VARCHAR(20) YES
weight SMALLINT(7) YES

after trying..

$query = "ALTER TABLE racehorses ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id)";

how to get this? -

Number of records found: 4
name VARCHAR(20) NO PRI
breed VARCHAR(30) YES
color VARCHAR(20) YES
weight SMALLINT(7) YES

i.e. INSERT/ADD.. etc. the primary key INTO the first field record (w/o adding an additional 'id' field, as stated earlier.

I am trying to alter a table which has no primary key nor auto_increment column. I know how to add an primary key column but I was wondering if it's possible to insert data into the primary key column automatically (I already have 500 rows in DB and want to give them id but I don't want to do it manually). Any thoughts? Thanks a lot.

Insert auto increment primary key to existing table

MySQL

我正在尝试修改一个既没有主键也没有auto_increment列的表。我知道如何添加主键列，但我想知道是否有可能自动将数据插入到主键列中(我在DB中已经有500行，我想给它们提供id，但我不想手动完成)。有什么想法吗？非常感谢。

问在现有表中插入自动增量主键
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在现有表中插入自动增量主键EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在现有表中插入自动增量主键
EN