How to auto increment by 2 for a particular table in mysql

4
votes

I have 2 tables order_retailer and order_customer

they both have auto incremented primary key order_id

To keep an order id unique in the whole system I want order_retailer to have id as even numbers only and for order_customer the ids will be odd. For this I have to set the autoincrement's increment value to 2. is it possible to set it table wise in mysql.

I don't want a php solution. please let me know if there is a command/query to set the auto increment shift to 2 for a perticular table. Also I don't want to change the server variables auto_increment_increment or auto_increment_offset or any other server variable

2
mysql
Why must the ids be unique in mysql? Just add a unique key when referencing them eg: 'RET' . $order_id; or 'CUST' . $order_id; ? – Mike Graf

2 Answers

12
votes

You can offset one table's auto increment field from the other, i.e. one table starts ids from 1 while the other starts from 1000000 (or some other value chosen depending on your usage pattern).

CREATE TABLE table1 (id BIGINT UNSIGNED AUTO_INCREMENT);
CREATE TABLE table2 (id BIGINT UNSIGNED AUTO_INCREMENT) AUTO_INCREMENT = 1000000;

You can also choose your autoincrement column type according to your needs. BIGINT UNSIGNED's range is 0..18446744073709551615, which should cover most cases.

OR

try

SET @@auto_increment_increment=2;
SET @@auto_increment_offset=2;
6
votes

No it is not possible to set it table wise.

Mysql has 2 variable auto_increment_increment and auto_increment_offset and values of both these variables are 1 by default. If you change any of the value it will have global effect.

To solve your problem create a stored procedure which will according to needs of having even id.

Refer this link http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment

Hope this helps