-1

I have MySQL Table with columns id, name, someInt, otherInt. After many deletes I have big holes of the id's of adjacent rows. How could I rewrite my db to fix this?

Example:
From

id      name       someInt    otherInt
1       Michel     7          9
2       John       12         3
5       Peter      5          6
9       George     20         21

To

id      name       someInt    otherInt
1       Michel     7          9
2       John       12         3
3       Peter      5          6
4       George     20         21
Petar Toshev
  • 67
  • 1
  • 1
  • 9
  • 3
    It all depends on your schema. If you use the id's of this table as foreign keys, then it's not so simple to do, but anyways why would you want to do that? – Derenir Mar 16 '16 at 15:33
  • Possible duplicate of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key) – Derenir Mar 16 '16 at 15:36
  • 1
    Do you have a really strong reason for doing so? – PM 77-1 Mar 16 '16 at 15:43
  • 3
    There *is* no good reason for wanting to do this. – Strawberry Mar 16 '16 at 15:51
  • 1
    Whatever you do to cure the OCD problem with so-called "gaps" will only produce bad things. You don't have to do this, there's a reason why there are gaps. – Mjh Mar 16 '16 at 15:53

1 Answers1

-1

one is this. It also set the AUTO_INCREMENT to the next free Nr.

SET @num := 0;
UPDATE tblName SET id = @num := (@num+1);
ALTER TABLE tblName AUTO_INCREMENT = 1;
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • I didn't dv, but if I had it would be because you're making a bad situation worse. There is no sensible reason for doing this. – Strawberry Mar 17 '16 at 11:56
  • not correct !!!!!, i have seen so much time systems where the AUTO_INCREMENT Value overrun and you cant change this, because the software handle the fieldtype self, and there is also no reason to do it not – Bernd Buffen Mar 17 '16 at 12:05
  • Perhaps we can agree to disagree. – Strawberry Mar 17 '16 at 12:11
  • I downvoted you because you poured oil on fire. Now there's going to be thousands of people reading this and "fixing" their MySQL installation so they don't get gaps in auto_increment. That's simply stupid, the OP has no clue there is NO problem here and you just helped make bad situation worse. It's nothing against you personally, the whole "problem" and answer are simply bad and should never be used and hunting reputation at any cost won't get anyone anywhere. – Mjh Mar 17 '16 at 12:15
  • Also, if you saw systems where "overrun" occurred, you'd know that the fix is allowing for MORE numbers and not RESETTING the auto_increment to start from the beginning. The reason not to do this is rebalancing the b-tree holding primary key values. Records will get reordered physically on the disk. For databases that have a billion records, imagine what happens when MySQL has to start rebalancing the data structure. Also, gaps in auto_increment are related to atomicity, consistency, isolation, durability and most of all - concurrency. – Mjh Mar 17 '16 at 12:19
  • @Mjh - I am sure that you need this if you made ​​enough experience with mysql – Bernd Buffen Mar 17 '16 at 12:21
  • There are many programms that delete (not truncate) data from tables and rebuild them. everytime they do that they increments this value and so you can get a overrun. and DBA where handle tables with a few billion records should knows what the doing – Bernd Buffen Mar 17 '16 at 12:26
  • i never has said that this is the best DB design, but when you have this reasons this can help you – Bernd Buffen Mar 17 '16 at 12:27
  • I've been working with MySQL for 16 years now. I have **never** needed this, I never **will** need this, no one needs that and I have worked with databases that are large (large means starting at terabyte). Gaps in auto_increment have their reason. You can't fix it, nothing is broken in the first place. Forcing MySQL not to produce gaps isn't a fix, it's introducing something that will make it work bad, slow and it will corrupt itself. I'm not pulling this out of my ass, I'm mentioning facts. – Mjh Mar 17 '16 at 12:33