Change the step auto_increment fields increment by

Question

How do I change the amount auto_increment fields in MySQL increment by from the default (1) to n?

Answer 1

If you want to change autoincrement step from 1 to N then there is a solution. It could be done on MySQL server side: look for '--auto-increment-increment' startup option or use following command SET @@auto_increment_increment=2;, but be warned that this is a server wide change (all tables will increment by 2).

Unortodox solutions could that could be considered:

1. Launch two MySQL servers on same machine, with different ports (one with auto_increment_increment=1 other with auto_increment_increment=2)
2. Use some serverside magic (PHP, ASP ,???) combined with turning off tables auto_increment to manually calculate (simple peek at last id and +=2 would be ok) and provide id in INSERT query.

Some official MySQL FAQ

Answer 2

You can change it using ALTER TABLE:

ALTER TABLE table AUTO_INCREMENT = n;

Or if you want to do set it from start:

CREATE TABLE table (...) AUTO_INCREMENT = n;