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|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|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|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|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|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|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.

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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