如何重新排列主键? [英] How to reorder a primary key?
本文介绍了如何重新排列主键?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张5700条记录的表格.主键是一个整数.现在我注意到一些值丢失了.像这样:
I have a table of 5700 records. The primary key is an integer. Now I noticed that some values are missing. Like this:
100 data
101 data
102 data
104 data
103
丢失.如何在一条SQL命令中更新所有行,以使顺序正确(在我的示例中,104
变为103
)?
103
is missing. How I can update all the rows so that the order becomes correct (104
becomes 103
in my example) in one SQL command?
推荐答案
不确定一个命令,但是您可以使用四个命令来实现:
Not sure about one command, but you can do it in four commands:
CREATE TABLE `temp` SELECT * FROM `orig_tbl`;
TRUNCATE `orig_tbl`;
-- counter doesn't reset in some old versions
ALTER TABLE `orig_tbl` AUTO_INCREMENT = 1;
-- now we omit primary key column to let it seal the holes
INSERT INTO `orig_tbl` (`col1`, `col2`) SELECT `col1`, `col2` FROM `temp`;
除非您这样做是为了使随机选择记录变得更容易,否则您确实应该重新考虑您的方法.
Unless you're doing this to make it easier to select records randomly, you really should rethink your approach.
这篇关于如何重新排列主键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文