在 mysql 表 auto_increment 中创建一个 ID（事后）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/251479/
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
make an ID in a mysql table auto_increment (after the fact)
提问by Gene R
I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code.
我从另一个开发人员那里获得了一个数据库。他没有在任何表上使用 auto_incrementers。它们都有主键 ID，但他在代码中手动完成了所有递增。
Can I turn those into Auto_incrementers now?
我现在可以把它们变成 Auto_incrementers 吗？
Wow, very nice, thanks a ton. It worked without a hitch on one of my tables. But a second table, i'm getting this error...Error on rename of '.\DBNAME#sql-6c8_62259c' to '.\DBNAME\dealer_master_events'
哇，非常好，非常感谢。它在我的一张桌子上顺利运行。但是第二个表，我收到此错误...将“.\DBNAME#sql-6c8_62259c”重命名为“.\DBNAME\dealer_master_events”时出错
回答by Bill Karwin
For example, here's a table that has a primary key but is not AUTO_INCREMENT:
例如，这是一个有主键但没有主键的表AUTO_INCREMENT：
mysql> CREATE TABLE foo (
id INT NOT NULL,
PRIMARY KEY (id)
);
mysql> INSERT INTO foo VALUES (1), (2), (5);
You can MODIFYthe column to redefine it with the AUTO_INCREMENToption:
您可以MODIFY使用以下AUTO_INCREMENT选项重新定义该列：
mysql> ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
Verify this has taken effect:
验证这是否生效：
mysql> SHOW CREATE TABLE foo;
Outputs:
输出：
CREATE TABLE foo (
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
Note that you have modified the column definition in place, without requiring creating a second column and dropping the original column. The PRIMARY KEYconstraint is unaffected, and you don't need to mention in in the ALTER TABLEstatement.
请注意，您已就地修改了列定义，无需创建第二列并删除原始列。该PRIMARY KEY约束是不受影响的，你不需要提及的ALTER TABLE声明。
Next you can test that an insert generates a new value:
接下来，您可以测试插入是否生成了新值：
mysql> INSERT INTO foo () VALUES (); -- yes this is legal syntax
mysql> SELECT * FROM foo;
Outputs:
输出：
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
| 6 |
+----+
4 rows in set (0.00 sec)
I tested this on MySQL 5.0.51 on Mac OS X.
我在 Mac OS X 上的 MySQL 5.0.51 上对此进行了测试。
I also tested with ENGINE=InnoDBand a dependent table. Modifying the idcolumn definition does not interrupt referential integrity.
我还测试了ENGINE=InnoDB一个依赖表。修改id列定义不会中断参照完整性。
To respond to the error 150 you mentioned in your comment, it's probably a conflict with the foreign key constraints. My apologies, after I tested it I thought it would work. Here are a couple of links that may help to diagnose the problem:
为了回应您在评论中提到的错误 150，它可能与外键约束发生冲突。我很抱歉，在我测试之后我认为它会起作用。以下是一些可能有助于诊断问题的链接：
回答by Stephen Walcher
I'm guessing that you don't need to re-increment the existing data so, why can't you just run a simple ALTER TABLE command to change the PK's attributes?
我猜你不需要重新增加现有数据，为什么你不能运行一个简单的 ALTER TABLE 命令来改变 PK 的属性？
Something like:
就像是：
ALTER TABLE `content` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT
I've tested this code on my own MySQL database and it works but I have not tried it with any meaningful number of records. Once you've altered the row then you need to reset the increment to a number guaranteed not to interfere with any other records.
我已经在我自己的 MySQL 数据库上测试了这段代码，它可以工作，但我没有尝试过任何有意义的记录数。更改行后，您需要将增量重置为保证不会干扰任何其他记录的数字。
ALTER TABLE `content` auto_increment = MAX(`id`) + 1
Again, untested but I believe it will work.
同样，未经测试，但我相信它会起作用。
回答by Michael A. Griffey
None of the above worked for my table. I have a table with an unsigned integer as the primary key with values ranging from 0 to 31543. Currently there are over 19 thousand records. I had to modify the column to AUTO_INCREMENT(MODIFY COLUMN'id'INTEGER UNSIGNED NOT NULL AUTO_INCREMENT) and set the seed(AUTO_INCREMENT = 31544) in the same statement.
以上都不适用于我的桌子。我有一个以无符号整数作为主键的表，其值范围从 0 到 31543。目前有超过 19,000 条记录。我必须将列修改为AUTO_INCREMENT( MODIFY COLUMN'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT) 并AUTO_INCREMENT = 31544在同一语句中设置种子 ( )。
ALTER TABLE `'TableName'` MODIFY COLUMN `'id'` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 31544;
回答by Michael A. Griffey
This worked for me (i wanted to make id primary and set auto increment)
这对我有用（我想让 id 成为主要的并设置自动增量）
ALTER TABLE table_nameCHANGE ididINT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE table_nameCHANGE ididINT PRIMARY KEY AUTO_INCREMENT;
回答by Dima Dz
ALTER TABLE `foo` MODIFY COLUMN `bar_id` INT NOT NULL AUTO_INCREMENT;
or
或者
ALTER TABLE `foo` CHANGE `bar_id` `bar_id` INT UNSIGNED NOT NULL AUTO_INCREMENT;
But none of these will work if your bar_idis a foreign key in another table: you'll be getting
但是，如果您bar_id是另一个表中的外键，则这些都不起作用：您将获得
an error 1068: Multiple primary key defined
To solve this, temporary disable foreign key constraint checks by
为了解决这个问题，临时禁用外键约束检查
set foreign_key_checks = 0;
and after running the statements above, enable them back again.
并在运行上述语句后，再次启用它们。
set foreign_key_checks = 1;
回答by Alex Weinstein
Yes, easy. Just run a data-definition query to update the tables, adding an AUTO_INCREMENT column.
是的，容易。只需运行一个数据定义查询来更新表，添加一个 AUTO_INCREMENT 列。
If you have an existing database, be careful to preserve any foreign-key relationships that might already be there on the "artificially created" primary keys.
如果您有一个现有的数据库，请小心保留“人工创建的”主键上可能已经存在的任何外键关系。
回答by Adam
As long as you have unique integers (or some unique value) in the current PK, you could create a new table, and insert into it with IDENTITY INSERT ON. Then drop the old table, and rename the new table.
只要您在当前 PK 中有唯一的整数（或某些唯一值），您就可以创建一个新表，并使用 IDENTITY INSERT ON 将其插入其中。然后删除旧表，并重命名新表。
Don't forget to recreate any indexes.
不要忘记重新创建任何索引。
