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

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;

Answer 2

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 3

alter table <table name>  auto_increment=n

where n is the number you want to start

Answer 4

You can also use ALTER SEQUENCE sequence_name INCREMENT BY N where N is the new incremnent value.

Answer 5

thanks to all, @molevna , so i think this feature is not currently available in mysql. adjusting autoincrement in serverwide is not feasible as per your warning, so i am going back the old way of hardcoding in my ui. thanks to all once again.