MySQL 将自增主键插入现有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9070764/
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
Insert auto increment primary key to existing table
提问by FlyingCat
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.
我正在尝试更改没有主键和 auto_increment 列的表。我知道如何添加主键列，但我想知道是否可以自动将数据插入主键列（我已经在数据库中有 500 行并想给它们 id 但我不想手动执行） . 有什么想法吗？非常感谢。
回答by Michael Berkowski
An ALTER TABLEstatement adding the PRIMARY KEYcolumn works correctly in my testing:
ALTER TABLE添加PRIMARY KEY列的语句在我的测试中正常工作：
ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;
On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENTidcolumn and inserted auto-increment values for each existing row in the table, starting with 1.
在为测试目的创建的临时表上，上述语句AUTO_INCREMENTid为表中的每个现有行创建了列并插入了自动增量值，从 1 开始。
回答by php
suppose you don't have column for auto increment like id, no, then you can add using following query:
假设您没有像 id 这样的自动增量列，不，那么您可以使用以下查询添加：
ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRST
If you've column, then alter to auto increment using following query:
如果您有列，则使用以下查询更改为自动递增：
ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY
回答by Grigor
yes, something like this would do it, might not be the best though, you might wanna make a backup
是的，这样的事情可以做到，但可能不是最好的，你可能想要备份
$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++;
}
notice that the the any_fieldyou select must be the same when updating.
请注意，any_field更新时您选择的必须相同。
回答by reflexiv
For those like myself getting a Multiple primary key definederror try:
对于像我这样遇到Multiple primary key defined错误的人，请尝试：
ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;
On MySQL v5.5.31 this set the idcolumn as the primary key for me and populated each row with an incrementing value.
在 MySQL v5.5.31 上，这将id列设置为我的主键，并用递增值填充每一行。
回答by Dave
The easiest and quickest I find is this
我发现最简单和最快的是这个
ALTER TABLE mydb.mytable
ADD COLUMN mycolumnname INT NOT NULL AUTO_INCREMENT AFTER updated,
ADD UNIQUE INDEX mycolumnname_UNIQUE (mycolumname ASC);
回答by drchuck
I was able to adapt these instructions take a table with an existingnon-increment primary key, and add an incrementing primary key to the table and create a new composite primary key with both the old and new keys as a composite primary key using the following code:
我能够调整这些指令，获取一个带有现有非增量主键的表，并向表中添加一个增量主键，并使用以下命令创建一个新的复合主键，将旧键和新键作为复合主键代码：
DROP TABLE IF EXISTS SAKAI_USER_ID_MAP;
CREATE TABLE SAKAI_USER_ID_MAP (
USER_ID VARCHAR (99) NOT NULL,
EID VARCHAR (255) NOT NULL,
PRIMARY KEY (USER_ID)
);
INSERT INTO SAKAI_USER_ID_MAP VALUES ('admin', 'admin');
INSERT INTO SAKAI_USER_ID_MAP VALUES ('postmaster', 'postmaster');
ALTER TABLE SAKAI_USER_ID_MAP
DROP PRIMARY KEY,
ADD _USER_ID INT AUTO_INCREMENT NOT NULL FIRST,
ADD PRIMARY KEY ( _USER_ID, USER_ID );
When this is done, the _USER_ID field exists and has all number values for the primary key exactly as you would expect. With the "DROP TABLE" at the top, you can run this over and over to experiment with variations.
完成此操作后，_USER_ID 字段存在并且具有与您期望的完全相同的主键的所有数字值。使用顶部的“DROP TABLE”，您可以反复运行它以尝试各种变化。
What I have not been able to get working is the situation where there are incoming FOREIGN KEYs that already point at the USER_ID field. I get this message when I try to do a more complex example with an incoming foreign key from another table.
我无法开始工作的情况是，传入的 FOREIGN KEY 已经指向 USER_ID 字段。当我尝试使用来自另一个表的传入外键执行更复杂的示例时，我收到此消息。
#1025 - Error on rename of './zap/#sql-da07_6d' to './zap/SAKAI_USER_ID_MAP' (errno: 150)
I am guessing that I need to tear down all foreign keys before doing the ALTER table and then rebuild them afterwards. But for now I wanted to share this solution to a more challenging version of the original question in case others ran into this situation.
我猜我需要在执行 ALTER 表之前拆除所有外键，然后再重建它们。但是现在我想将这个解决方案分享给原始问题的更具挑战性的版本，以防其他人遇到这种情况。
回答by David 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.
导出您的表格，然后清空您的表格，然后将字段添加为唯一的 INT，然后将其更改为 AUTO_INCREMENT，然后再次导入您之前导出的表格。
回答by Nagesh Hugar
You can add a new Primary Key column to an existing table, which can have sequence numbers, using command:
您可以使用以下命令将新的主键列添加到现有表中，该表可以具有序列号：
ALTER TABLE mydb.mytable ADD pk_columnName INT IDENTITY
回答by Humphrey
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
我遇到了同样的问题，所以我所做的我删除了主键的字段，然后重新创建了它并确保它是自动增量的。那对我有用。我希望它能帮助别人
回答by Hrushikesh vanga
ALTER TABLE tableNameMODIFY tableNameIDMEDIUMINTNOT NULL AUTO_INCREMENT;
ALTER TABLE tableNameMODIFY tableNameID MEDIUMINTNOT NULL AUTO_INCREMENT;
Here tableNameis name of your table,
这里tableName是你的表的名称，
tableNameis your column name which is primary has to be modified
tableName是您的主列名，必须修改
MEDIUMINTis a data type of your existing primary key
MEDIUMINT是您现有主键的数据类型
AUTO_INCREMENTyou have to add just auto_increment after not null
AUTO_INCREMENT你必须在非空之后添加 auto_increment
It will make that primary key auto_increment......
它将使主键 auto_increment ......
Hope this is helpful:)
希望这有帮助:)
