在mysql表auto_increment中创建一个ID(在事实之后) [英] make an ID in a mysql table auto_increment (after the fact)
问题描述
我从另一个开发人员那里获得了一个数据库.他没有在任何表上使用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.
我现在可以把它们变成自动增量器吗?
Can I turn those into Auto_incrementers now?
哇,非常好,非常感谢.我的一张桌子工作顺利.但是在第二张表中,我收到此错误...将.\ DBNAME#sql-6c8_62259c"重命名为.\ DBNAME \ dealer_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 '.\DBNAME\dealer_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错误1025(HY000):重命名"./foo"时出现错误(错误编号:150)是什么意思?
- http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html
- What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?
- http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html
这篇关于在mysql表auto_increment中创建一个ID(在事实之后)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!