Align auto increment index to next thousand

Question

I am using MySQL.

When applying a big change to a production database, I would like to change the next auto-increment index of all my tables to align them to the next available thousand.

In other words, for each table, make the next new row in that table bear an ID that is 1000-aligned.

That would make it easier for humans to detect pre- and post-migration records without impairing DB functionality.

Is there a quick automated way to do that? More importantly, is there an important drawback I missed?

Answer 1

If using mariadb-10.3 you can use sequences with a sequence defined to a 1000 increments that can be used for columns (ref).

MySQL and MariaDB have an auto_increment_increment global system variable that globally controls the amount that an AUTO_INCREMENT column will increment. It is global in the sense it will affect all tables. You'll need to alter the table in question to have the next AUTO_INCREMENT value at a 1000 offset. Its designed around a multi-master replication purpose so you can't use it with bi-directional (or galera cluster) replication configurations.

In general an AI value is meant to be an internal detail rather than something for humans to consider so another differentator might be better. Its hard to read your business case.