MySQL: auto-increment with odd or even numbers?

Question

Is it possible to have an auto-increment field that generates even or odd numbers (skips the opposite)? The reason I'm asking is because I want to use auto-increment between two tables. Other ways to achieve it are welcome.

Thanks in advance!

Answer 1

You can compensate for one field of automatic growth of the table from another, i.e. one table starts identifiers from 1 and the other starts from 1,000,000 (or another value selected 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 select your type of auto-increment column to suit your needs. The BIGINT UNSIGNED range is 0..18446744073709551615, which should cover most cases.

Answer 2

As mentioned by Jürgen, you can do this at the system level instead of the session level by making the following change (or addition) to the my.ini file:

 auto-increment-increment = 2 auto-increment-offset = 1 

or

 auto-increment-increment = 2 auto-increment-offset = 2 

This is mainly used very often when setting up Master-Master replication.

Answer 3

to try

 SET @@auto_increment_increment=2; SET @@auto_increment_offset=2; 

Answer 4

You can use triggers to achieve custom auto-increment functions.

Create a table to store custom auto-increment values ​​and insert one row with the initial values:

 CREATE TABLE autoincrement_id (id_even INT, id_odd INT); INSERT INTO autoincrement_id VALUES (0, 1); 

Create triggers that change the row id value accordingly:

 CREATE TRIGGER set_id_in_sometable_with_odd_id BEFORE INSERT ON `sometable_with_odd_id` FOR EACH ROW BEGIN SET NEW.id = (SELECT id_odd FROM autoincrement_id LIMIT 1); UPDATE autoincrement_id SET id_odd = id_odd + 2; END; CREATE TRIGGER set_id_in_sometable_with_even_id BEFORE INSERT ON `sometable_with_even_id` FOR EACH ROW BEGIN SET NEW.id = (SELECT id_even FROM autoincrement_id LIMIT 1); UPDATE autoincrement_id SET id_even = id_even + 2; END;