clean up table in MySQL which uses auto increment

Question

My table looks like this:

table_id | letters
-------- | -------
     4   |    a
    10   |    b
    24   |    c
    78   |    d
   110   |    e
   234   |    f

table_id uses the option AUTO_INCREMENT. (those values came because of a weird bug in my program ... don't ask :-) )

I want to clean it up with the following result:

table_id | letters
-------- | -------
    1    |    a
    2    |    b
    3    |    c
    4    |    d
    5    |    e
    6    |    f

Is that possible?

And is there a way to clean this automatically up via cronjob or something?

Solution:

The 1st solution of Gordons answer did work properly. But i needed to add some code because the auto_increment didn't want to reset automatically. The final solution is:

SET @rn := 0;

UPDATE t 
SET 
    table_id = (@rn:=@rn + 1)
ORDER BY table_id;

SELECT 
    COUNT(*)
INTO @AutoInc FROM
    t;

SET @s:=CONCAT('ALTER TABLE t AUTO_INCREMENT=', @AutoInc + 1);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(to reset the counter i used this solution)

Deleting rows from tables aren't usually due to bugs. You can certainly copy the data from the table to a new table, then delete the old one and rename the new one. But if your autoincrementing primary keys are used as foreign keys anywhere else you'll wreck your database by doing that. Please [edit] your question to explain more about what you're trying to do. — O. Jones, Jul 30 '17 at 12:42

score 1 · Accepted Answer · answered Jul 30 '17 at 12:53

1

Try doing the following:

set @rn := 0;

update t
    set table_id = (@rn := @rn + 1)
    order by table_id;

If that doesn't work, you can use the truncate-and-reload trick:

create table temp_t as
    select t.*
    from t;

truncate table t;

insert into t(letters)
    select letters
    from temp_t
    order by table_id;

answered Jul 30 '17 at 12:53

Gordon Linoff

1,242,037
58
646
786

The first solution did it properly. Thank you! – GePu Jul 30 '17 at 17:33

score 1 · Answer 2 · answered Jul 30 '17 at 15:35

1

As a side note, and depending on table type (InnoDB, MyISAM...) please note that once you have cleaned up your table, the next inserted row may use (max id+1) as you probably expect, or it may use the auto_increment value that would have been used from the last insert (in your case it would be 235).

If this is the case, use:

ALTER TABLE tbl AUTO_INCREMENT = 7;

(I'm using 7 since your example has 6 lines, adjust accordingly).

answered Jul 30 '17 at 15:35

bobflux

11,123
3
27
27

Thank you for this node! I implemented this into the final solution. – GePu Jul 30 '17 at 17:32

score 0 · Answer 3 · answered Jul 30 '17 at 12:41

0

Here is one way using Self Join

SELECT Count(*) table_id, 
       a.letters
FROM   Yourtable a 
       JOIN Yourtable b 
         ON a.table_id >= b.table_id 
GROUP  BY a.table_id, 
          a.letters

SQL Fiddle Demo

answered Jul 30 '17 at 12:41

Pரதீப்

91,748
19
131
172

clean up table in MySQL which uses auto increment

3 Answers3