10

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

Dominic Rodger
  • 97,747
  • 36
  • 197
  • 212
sansknwoledge
  • 4,209
  • 9
  • 38
  • 61
  • 3
    Do you want to change the next value used, or the step being used between values (e.g. step value of 2, use 1, 3, 5, 7 etc)? – Dominic Rodger Nov 06 '09 at 09:08
  • sorry if the question is ambiguous yes correct i want the auto_increment should be performed as stated by you (like the identity(1,3) in sql server which increase the next value by 3) – sansknwoledge Nov 06 '09 at 10:07
  • @sansknwoledge - I've edited the question to hopefully make it ask what you meant! – Dominic Rodger Nov 06 '09 at 10:30

4 Answers4

8

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

mth
  • 490
  • 2
  • 12
  • hi, i checked the faq i am little bit confused [you can set each server to generate auto-increment values that don't conflict with other servers. The --auto-increment-increment value should be greater than the number of servers (from that page)] could any body explain this? – sansknwoledge Nov 06 '09 at 10:11
  • 4
    the idea is this: servers={db1,db2,db3}; db01:{--auto-increment-offset=0;--auto-increment-increment=3};db02:{--auto-increment-offset=1;--auto-increment-increment=3};db03:{--auto-increment-offset=2;--auto-increment-increment=3}; so, db1 only generates IDs where (n % 3) == 0, for db2 (n % 3) == 1, for db3 (n % 3) == 2; i.e. db1:"3,6,9,..." db2:"1,4,7,..." db3:"2,5,8" -> no id conflict – Piskvor left the building Nov 06 '09 at 10:47
  • @Piskvor thanks for the clarification, but if i am having more than one table in my db all with autogenerate columns and i want only one table to increment in steps of say 2 and rest follow 3 what should i do? – sansknwoledge Nov 06 '09 at 11:58
  • 1
    Is there any way to make this increment by step for only a single table. – M.A.K. Ripon Apr 19 '18 at 11:21
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;
Lukáš Lalinský
  • 40,587
  • 6
  • 104
  • 126
  • 2
    I don't *think* that does what he wants (it answers the question he asks in the body of his question, but not the question in the title). The question is definitely ambiguous though. – Dominic Rodger Nov 06 '09 at 09:08
  • 2
    Yep, this doesn't do what he's trying to do (I've now edited the question to hopefully make it clearer). – Dominic Rodger Nov 06 '09 at 10:31
-3

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

Sachin
  • 20,805
  • 32
  • 86
  • 99
-4
alter table <table name>  auto_increment=n

where n is the number you want to start

Treby
  • 1,328
  • 6
  • 18
  • 26