Temporarily changing the auto increment increment in MySQL

It's possible to temporarily change MySQL's auto increment increment value to something other than the system setting. This might be useful if (like me) you have two MySQL databases set up in a master-master relationship with an increment of 2, but need to increment a table in 1s.

"SELECT @@auto_increment_increment" to see what the current value is.

"SET @@auto_increment_increment = X" where X is the number to increment to set it.

It will affect all auto increment values on all tables on all databases for the duration of your connection; it will not affect other connections.

Longer answer / case study

By default the auto increment value on a field in MySQL is 1, but it can easily be set to something else with the auto_increment_increment setting.

I run a web server with a backup server set up identically; both have MySQL servers running on them, running in a master-master replication relationship. The auto_increment_increment value of each is 2; the auto_increment_offset of the primary server is 1 and the backup 2. This means that inserts into the primary server will be 1, 3, 5, 7 and so on; and on the backup server 2, 4, 6, 8 and so on.

On one particular website, there is an orders table in the database for e-commerce orders, which needs to have the primary key on the table increment in 1s, not 2s as would happen by default with my set up.

The simplest way I found to do this is to keep a record of what the current increment value is, change it to 1, and the restore it back to what it was previously.

To get the current value:
SELECT @@auto_increment_increment

To set the value, to e.g. 1 & 5:
SET @@auto_increment_increment = 1
SET @@auto_increment_increment = 5

A pseudo code example of changing the value before saving to the orders table, and then restoring the value afterwards, is as follows:

$previousIncrement = selectOneValue("SELECT @@auto_increment_increment");
runQuery("SET @@auto_increment_increment = 2");
runQuery("INSERT INTO table (...) VALUES (...)");
runQuery("SET @@auto_increment_increment = $previousIncrement");

Related posts:

• How to default a column as a UUID/GUID in MySQL
• PHP PDO class to set the MySQL auto_increment_increment
• Auto increment a value on the fly with MySQL
• Reset the auto increment value for a MySQL table