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

Question

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.

Answer 1

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)

• Kardo: Worked perfectly! Thanks a million for your great & detailed answer. You're the man!