MySQL – How to convert an existing int column with duplicates to auto-increment

auto-incrementduplicationMySQL

After adding some thousand records in a table, I noticed that the "Primary" and "Auto-Increment" properties of the main column are removed. Now instead of auto-incremented numbers, most of them (but not all) are just "0".

Any idea how to fix the issue? All "0" values should be updated to: Max(int) of column + 1.

Best Answer

Let's assume I have the following table (id is the row that we want to be autoinc, and name, all other columns):

MariaDB [test]> CREATE TABLE without_auto_inc (id int, name text);
Query OK, 0 rows affected (0.013 sec)

Let's add some sample data, with some 0s:

MariaDB [test]> insert into without_auto_inc VALUES (1, 'a'), (0, 'b'), (3, 'c'), (0, 'd'), (0, 'e'), (5, 'f'), (0, 'g'), (100, 'h');                                             Query OK, 8 rows affected (0.001 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM without_auto_inc;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    0 | b    |
|    3 | c    |
|    0 | d    |
|    0 | e    |
|    5 | f    |
|    0 | g    |
|  100 | h    |
+------+------+
8 rows in set (0.001 sec)

We can simply get the max id, reinsert (duplicate) the rows with id = 0 but with an variable incrementing its value for each selected row, and then delete the 0 rows:

MariaDB [test]> BEGIN;
SELECT max(id) INTO @id FROM without_auto_inc; 
INSERT INTO without_auto_inc SELECT @id := @id + 1 as id, name FROM without_auto_inc WHERE id = 0;
DELETE FROM without_auto_inc WHERE id = 0;
COMMIT;
ALTER TABLE without_auto_inc MODIFY id int primary key auto_increment;
Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.034 sec)              
Records: 8  Duplicates: 0  Warnings: 0

You can add an ORDER BY TO the select to create the auto-inc in the order you prefer. You can also do the insert from a file if you prefer there is never duplicate rows. The exact method will depend on your constrains- make sure you are not inserting new values while it runs, or you may have undesired results/data loss (e.g. consider blocking the table for new writes). Sadly, most versions of MySQL don't allow transactional ALTERS (as in part of a larger transaction), so it will force an autocommit before running.

This is the state of the table after running:

MariaDB [test]> SELECT * FROM without_auto_inc;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   3 | c    |
|   5 | f    |
| 100 | h    |
| 101 | b    |
| 102 | d    |
| 103 | e    |
| 104 | g    |
+-----+------+
8 rows in set (0.001 sec)
Related Question