MySQL: Auto increment with even or odd numbers ?

Auto increment with even or odd numbers ?



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

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

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

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;

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

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


Learn More :