做一个mysql表中的ID auto_increment(事后)make an ID in a mysql table auto_increment (after the fact)

- 此内容更新于:2014-12-30
主题:

原文:

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.

Can I turn those into Auto_incrementers now?


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

解决方案:
原文:

For example, here's a table that has a primary key but is not AUTO_INCREMENT:

mysql> CREATE TABLE foo (
  id INT NOT NULL,
  PRIMARY KEY (id)
);
mysql> INSERT INTO foo VALUES (1), (2), (5);

You can MODIFY the column to redefine it with the AUTO_INCREMENT option:

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 KEY constraint is unaffected, and you don't need to mention in in the ALTER TABLE statement.

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.

I also tested with ENGINE=InnoDB and a dependent table. Modifying the id column definition does not interrupt referential integrity.


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:

Gene R的回复:哇,很好,谢谢一吨。它工作顺利我的一个表。但第二个表,我# 39;我得到这个错误…错误的重命名& # 39;。 DBNAME # sql-6c8_62259c& # 39;& # 39;。 DBNAME dealer_master_events& # 39;(errno:150)

(原文: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 '.DBNAMEdealer_master_events' (errno: 150))

Manan Shah的回复:谢谢aloat。你救我。谢谢

(原文:thanks aloat. you save me. thanks)

解决方案:
原文:

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?

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.

ALTER TABLE `content` auto_increment = MAX(`id`) + 1

Again, untested but I believe it will work.

解决方案:
以上为我的工作表。我有一个表和一个无符号整数主键值从0到31543不等。目前有超过1.9万条记录。我不得不修改列AUTO_INCREMENT(修改列' id 'INTEGER无符号NOT NULL AUTO_INCREMENT)并设置种子(AUTO_INCREMENT = 31544)在同一语句。
原文:

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.

ALTER TABLE `'TableName'` MODIFY COLUMN `'id'` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 31544;
解决方案:
只要你有独特的整数(或一些独特的价值)在当前的PK中,你可以创建一个新表,并插入身份插入。然后删除旧表,重命名新表。 别忘了重新创建任何索引。
原文:

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.

Don't forget to recreate any indexes.

Bill Karwin的回复:这个选项是特定于Microsoft SQL Server和MySQL没有相关性。

(原文:That option is specific to Microsoft SQL Server and it has no relevance to MySQL.)

解决方案:
是的,很容易。只运行一个数据定义查询更新表,添加一个AUTO_INCREMENT列。 如果你有一个现有的数据库,小心保存任何外键关系,可能已经在“人为地创造了“主键。
原文:

Yes, easy. Just run a data-definition query to update the tables, adding an AUTO_INCREMENT column.

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.

解决方案:
这个工作对我来说(我想让id主并设置自动增量) ALTER TABLE table_name改变AUTO_INCREMENT INT id id主键;
原文:

This worked for me (i wanted to make id primary and set auto increment)

ALTER TABLE table_name CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;