如何将主键添加到 MySQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5132923/
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
How to add a primary key to a MySQL table?
提问by mysql_go
This is what I tried but it fails:
这是我尝试过但失败的方法：
alter table goods add column `id` int(10) unsigned primary AUTO_INCREMENT;
Does anyone have a tip?
有人有提示吗？
回答by The Scrum Meister
After adding the column, you can always add the primary key:
添加列后，您可以随时添加主键：
ALTER TABLE goods ADD PRIMARY KEY(id)
ALTER TABLE goods ADD PRIMARY KEY(id)
As to why your script wasn't working, you need to specify PRIMARY KEY, not just the word PRIMARY:
至于为什么您的脚本不起作用，您需要指定PRIMARY KEY，而不仅仅是单词PRIMARY：
alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;
回答by MER
Existing Column
现有列
If you want to add a primary key constraint to an existing column all of the previously listed syntax will fail.
如果要将主键约束添加到现有列，则所有先前列出的语法都将失败。
To add a primary key constraint to an existing column use the form:
要将主键约束添加到现有列，请使用以下表单：
ALTER TABLE `goods`
MODIFY COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;
回答by Denis Makarskiy
If your table is quite big better not use the statement:
如果您的表很大，最好不要使用以下语句：
alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;
because it makes a copy of all data in a temporary table, alter table and then copies it back. Better do it manually. Rename your table:
因为它复制了一个临时表中的所有数据，alter table 然后将它复制回来。最好手动完成。重命名您的表：
rename table goods to goods_old;
create new table with primary key and all necessary indexes:
使用主键和所有必要的索引创建新表：
create table goods (
id int(10) unsigned not null AUTO_INCREMENT
... other columns ...
primary key (id)
);
move all data from the old table into new, disabling keys and indexes to speed up copying:
将旧表中的所有数据移动到新表中，禁用键和索引以加快复制速度：
-- USE THIS FOR MyISAMTABLES:
-- 将其用于MyISAM表：
SET UNIQUE_CHECKS=0;
ALTER TABLE goods DISABLE KEYS;
INSERT INTO goods (... your column names ...) SELECT ... your column names FROM goods_old;
ALTER TABLE goods ENABLE KEYS;
SET UNIQUE_CHECKS=1;
OR
或者
-- USE THIS FOR InnoDBTABLES:
-- 将其用于InnoDB表：
SET AUTOCOMMIT = 0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;
INSERT INTO goods (... your column names ...) SELECT ... your column names FROM goods_old;
SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1; COMMIT; SET AUTOCOMMIT = 1;
It takes 2 000 seconds to add PK to a table with ~200 mln rows.
将 PK 添加到具有约 2 亿行的表需要 2000 秒。
回答by ftrotter
Not sure if this matter to anyone else, but I prefer the id table to the first column in the database. The syntax for that is:
不确定这对其他人是否重要，但我更喜欢 id 表而不是数据库中的第一列。其语法是：
ALTER TABLE your_db.your_table ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
Which is just a slight improvement over the first answer. If you wanted it to be in a different position, then
这只是比第一个答案略有改进。如果你想让它处于不同的位置，那么
ALTER TABLE unique_address ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT AFTER some_other_column;
HTH, -ft
HTH，-ft
回答by RATAN KUMAR
Use this query,
使用这个查询，
alter table `table_name` add primary key(`column_name`);
回答by Micha? Myszor
This code work in my mysql db:
此代码在我的 mysql 数据库中工作：
ALTER TABLE `goods`
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);
回答by Vimit Patel
ALTER TABLE GOODS MODIFY ID INT(10) NOT NULL PRIMARY KEY;
回答by phponwebsites
Try this,
尝试这个，
alter table goods add column `id` int(10) unsigned primary key auto_increment
回答by av3000
For me, none of suggestions worked giving me the errors of syntax, so I just gave a try using phpmyadmin(version 4.9.2), (10.4.10-MariaDB) and added idcolumn with auto-increment primary key. Idcolumn was nicely added from the first element.
对我来说，没有任何建议给我带来语法错误，所以我只是尝试使用 phpmyadmin(version 4.9.2), (10.4.10-MariaDB) 并添加了id带有自动增量主键的列。Id列是从第一个元素很好地添加的。
Query output was:
查询输出为：
ALTER TABLE table_nameADD idINT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
ALTER TABLE table_nameADD idINT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY ( id);
回答by Raja Muthukutty
Remove quotes to work properly...
删除引号以正常工作...
alter table goods add column id int(10) unsigned primary KEY AUTO_INCREMENT;
