在 mysql 表 auto_increment 中创建一个 ID(事后)

2021-11-30 00:00:00 mysql auto-increment primary-key

我从另一个开发人员那里获得了一个数据库.他没有在任何表上使用 auto_incrementers.它们都有主键 ID，但他在代码中手动完成了所有递增.

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

Can I turn those into Auto_incrementers now?

哇，非常好，非常感谢.它在我的一张桌子上顺利运行.但是第二个表，我收到此错误...将.DBNAME#sql-6c8_62259c"重命名为.DBNAMEdealer_master_events"时出错

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'

推荐答案

例如，这里有一个有主键但不是AUTO_INCREMENT的表:

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

您可以MODIFY使用AUTO_INCREMENT选项重新定义列:

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;

验证这是否生效:

mysql> SHOW CREATE TABLE foo;

输出:

CREATE TABLE foo (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

请注意，您已就地修改了列定义，无需创建第二列并删除原始列.PRIMARY KEY 约束不受影响，您无需在 ALTER TABLE 语句中提及.

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;

输出:

+----+
| id |
+----+
|  1 | 
|  2 | 
|  5 | 
|  6 | 
+----+
4 rows in set (0.00 sec)

我在 Mac OS X 上的 MySQL 5.0.51 上对此进行了测试.

I tested this on MySQL 5.0.51 on Mac OS X.

我还用 ENGINE=InnoDB 和一个依赖表进行了测试.修改 id 列定义不会中断参照完整性.

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

为了回应你在评论中提到的错误 150，它可能与外键约束冲突.我很抱歉，在我测试之后，我认为它会起作用.以下是一些可能有助于诊断问题的链接:

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:

  • 什么mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) 是什么意思?
  • http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html

相关文章