Question 270891 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL(MySQL): Fix ID column & AUTO INCREMENT

I’ve a table with ID column, auto increment. Because of the bad migration a while a go, and I only noticed this now, form entries were all stored as ID 0. So now I have two problems: around 1000 entries with 0 as ID as well as column definition and AUTO INCREMENT value.

Latter is an easy fix, and I’m not concerned about that. But, is there a SQL command that I can use to programmatically fix IDs so that they’re set just as if the AUTO INCREMENT was working properly the whole time.

The last proper id is 2540. After that entry, all the entries have id 0. There’s 3200 rows in the table. So, all in all, I want to fix the table ID column so that all the IDs 0 are changed from 2541 to 3200. NOTE: I can’t afford to change the old working IDs.

Tags:

2

Answers


  1. 0

    In case if you can reset ALL id values you can simply drop the id column and re-create it with right configuration:

    alter table t drop column id;

alter table t add column id int primary key auto_increment first;

    test solution here

    Another way – create procedure that fix table rows one by one:

    delimiter $$
CREATE PROCEDURE fix_id()
BEGIN
  DECLARE _v INT;
  DECLARE _id INT;
  
  SELECT count(*) into _v FROM t WHERE id = 0;
  WHILE _v > 0 DO
    SELECT max(id) + 1 INTO _id FROM t;
    
    UPDATE t SET id = _id WHERE id = 0 LIMIT 1;
    
    SELECT count(*) INTO _v FROM t WHERE id = 0;
  END WHILE;
END;

delimiter ;

CALL fix_id();

select * from t;

    test here

    Don’t forget to set column auto_incremet and primary key after update:

    ALTER TABLE t MODIFY COLUMN id INT auto_increment primary key;
    Login or Signup to reply.
  2. 0

    Here’s a demo:

    mysql> select * from mytable order by created_at;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 2539 | 2023-10-23 09:58:00 |
| 2540 | 2023-10-23 09:59:00 |
|    0 | 2023-10-23 10:00:00 |
|    0 | 2023-10-23 10:01:00 |
|    0 | 2023-10-23 10:02:00 |
|    0 | 2023-10-23 10:03:00 |
|    0 | 2023-10-23 10:04:00 |
|    0 | 2023-10-23 10:05:00 |
+------+---------------------+
8 rows in set (0.00 sec)

mysql> set @id = 2540;
Query OK, 0 rows affected (0.00 sec)

mysql> update mytable set id = (@id := @id + 1) where id = 0 order by created_at;
Query OK, 6 rows affected, 1 warning (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 1

    The use of user-defined variables in this way is discouraged and gives a warning, but at least in MySQL 8.0 it still works (I tested the above on MySQL 8.1.0).

    Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: ‘SET variable=expression, …’, or ‘SELECT expression(s) INTO variables(s)’.

    You can then verify that the rows have been updated the way you want.

    mysql> select * from mytable order by created_at;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 2539 | 2023-10-23 09:58:00 |
| 2540 | 2023-10-23 09:59:00 |
| 2541 | 2023-10-23 10:00:00 |
| 2542 | 2023-10-23 10:01:00 |
| 2543 | 2023-10-23 10:02:00 |
| 2544 | 2023-10-23 10:03:00 |
| 2545 | 2023-10-23 10:04:00 |
| 2546 | 2023-10-23 10:05:00 |
+------+---------------------+

    You should change id to be a primary key, then it won’t allow duplicates.

    ALTER TABLE mytable ADD PRIMARY KEY (id);

    You should also make sure your sql_mode option does NOT include NO_AUTO_VALUE_ON_ZERO. If you insert a 0 value to an auto-increment column, it should generate a new incremented value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search