做一个mysql表中的ID auto_increment(事后)make an ID in a mysql table auto_increment (after the fact)
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
mysql> CREATE TABLE foo ( id INT NOT NULL, PRIMARY KEY (id) ); mysql> INSERT INTO foo VALUES (1), (2), (5);
MODIFY the column to redefine it with the
mysql> ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
Verify this has taken effect:
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
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)
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:
(原文：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?
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
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;
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.
(原文：That option is specific to Microsoft SQL Server and it has no relevance to MySQL.)
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)
id INT PRIMARY KEY AUTO_INCREMENT;
- 简单随机样本从一个Sql数据库Simple Random Samples from a Sql database
- 查询优化技术?(关闭)Query optimization techniques? [closed]
- 什么最好的方法访问数据库在一个PHP类?What the best way to access the database inside a class in PHP?
- MySqlCommand参数不工作MySqlCommand Parameter not Working
- 重装MYSQL(windows)——怎么去旧的数据与新安装吗?Re-installing MYSQL(windows) - How to get to the old data with the new install?
- 做一个mysql表中的ID auto_increment(事后)make an ID in a mysql table auto_increment (after the fact)
- 我如何升级mysql ?How do I upgrade mysql?
- 做数值主键删除记录在数据库中得到重用未来新记录?Do numerical primary keys of deleted records in a database get reused for future new records?
- 加入两个表没有返回的行Joining two tables without returning unwanted row
- 有干净的清理方式在MySQL重复的条目吗?Is there a clean way of cleaning up duplicate entries in MySQL?
- 社会安全号码存储Storing Social Security Numbers