blocks|key|1853487|text|只要您在当前主键中有唯一的整数(或某个唯一的值)，您就可以创建一个新表，并在IDENTITY+insert为ON的情况下插入其中。然后删除旧的表，并重命名新的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1853488|不要忘记重新创建任何索引。|1853489|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|2067514|text|是的，很简单。只需运行数据定义查询来更新表，添加一个AUTO_INCREMENT列即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2067515|如果您有一个现有的数据库，请注意保留可能已经存在于“人工创建的”主键上的任何外键关系。|2067516|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1853713|text|我猜你不需要重新增加已有的数据，为什么你不能运行一个简单的ALTER+TABLE命令来改变主键的属性呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1853714|类似于：|1853715|ALTER+TABLE+`content`+CHANGE+`id`+`id`+SMALLINT(+5+)+UNSIGNED+NOT+NULL+AUTO_INCREMENT+|code-block|syntax|javascript|1853716|我已经在我自己的MySQL数据库上测试了这段代码，它可以工作，但我没有用任何有意义的记录数量来尝试它。一旦更改了行，则需要将增量重置为一个保证不会干扰任何其他记录的数字。|1853717|ALTER+TABLE+`content`+auto_increment+=+MAX(`id`)+%2B+1|1853718|再一次，未经测试，但我相信它会工作。|1853719|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>ALTER TABLE `content` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT 
</code></pre>

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.

<pre><code>ALTER TABLE `content` auto_increment = MAX(`id`) + 1
</code></pre>

Again, untested but I believe it will work.

blocks|key|1853747|text|上面的方法都不适用于我的表格。我有一个表，它的主键是一个无符号整数，值的范围从0到31543。目前有超过19,000条记录。我必须将列修改为AUTO_INCREMENT+(MODIFY+COLUMN'id'INTEGER+UNSIGNED+NOT+NULL+AUTO_INCREMENT)，并在同一条语句中设置种子(AUTO_INCREMENT+=+31544)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1853748|ALTER+TABLE+`'TableName'`+MODIFY+COLUMN+`'id'`+INTEGER+UNSIGNED+NOT+NULL+AUTO_INCREMENT,+AUTO_INCREMENT+=+31544;|code-block|syntax|javascript|1853749|entityMap^0|1Y|E|2E|D|2V|14|4E|M|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

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 <code>AUTO_INCREMENT</code> (<code>MODIFY COLUMN</code>'id'<code>INTEGER UNSIGNED NOT NULL AUTO_INCREMENT</code>) and set the seed(<code>AUTO_INCREMENT = 31544</code>) in the same statement.

<pre><code>ALTER TABLE `'TableName'` MODIFY COLUMN `'id'` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 31544;
</code></pre>

blocks|key|2067734|text|这对我很有效(我想把id设为主值并设置自动递增)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2067735|ALTER+TABLE+table_name+CHANGE+id+id+INT+PRIMARY+KEY+AUTO_INCREMENT；|offset|length|style|CODE|2067736|entityMap^0|0|C|A|U|2|X|2|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|I|$]]

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

ALTER TABLE <code>table_name</code> CHANGE <code>id</code> <code>id</code> INT PRIMARY KEY AUTO_INCREMENT;

blocks|key|1853829|text|ALTER+TABLE+`foo`+MODIFY+COLUMN+`bar_id`+INT+NOT+NULL+AUTO_INCREMENT;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1853830|或|unstyled|1853831|ALTER+TABLE+`foo`+CHANGE+`bar_id`+`bar_id`+INT+UNSIGNED+NOT+NULL+AUTO_INCREMENT;|1853832|但是，如果您的bar_id是另一个表中的外键，那么所有这些都不会起作用：|offset|length|style|CODE|1853833|an+error+1068:+Multiple+primary+key+defined|1853834|要解决此问题，请通过以下方法临时禁用外键约束检查|1853835|set+foreign_key_checks+=+0;|1853836|在运行上面的语句之后，再次启用它们。|1853837|set+foreign_key_checks+=+1;|1853838|entityMap^0|0|0|0|7|6|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|11|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|13|8|@$K|14|L|15|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$B|C]]|$1|Q|3|R|5|F|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$B|C]]|$1|U|3|V|5|F|7|19|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$B|C]]|$1|Y|3|-4|5|F|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

<pre><code>ALTER TABLE `foo` MODIFY COLUMN `bar_id` INT NOT NULL AUTO_INCREMENT;
</code></pre>

or 

<pre><code>ALTER TABLE `foo` CHANGE `bar_id` `bar_id` INT UNSIGNED NOT NULL AUTO_INCREMENT;
</code></pre>

But none of these will work if your <code>bar_id</code> is a foreign key in another table: you'll be getting 

<pre><code>an error 1068: Multiple primary key defined
</code></pre>

To solve this, temporary disable foreign key constraint checks by

<pre><code>set foreign_key_checks = 0;
</code></pre>

and after running the statements above, enable them back again. 

<pre><code>set foreign_key_checks = 1;
</code></pre>

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?

<hr>

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'

make an ID in a mysql table auto_increment (after the fact)

SQL数据库

数据库

MySQL

我从另一个开发人员那里获得了一个数据库。他没有在任何桌子上使用auto_incrementers。它们都有主键ID，但他在代码中手动完成了所有的递增。我现在可以把它们转换成Auto_incrementers了吗？哇，非常好，非常感谢。它在我的一张桌子上工作得很顺利。但是第二个表，我在将'.\DBNAME#sql-6c8...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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