[SOLVED] How to increment id without auto increment?

How to increment id without auto increment?

I have a table with id column as a number which have meanings. Different types of accounts start from different ranges. E.g Organisation 10000 <-> 100000, users 1000000 <-> 1kk. How can i properly increment ids on insert (with possible concurrency problem)?

Solution

If you were doing this in Oracle's table server, you would use different SEQUENCE objects for each type of account.

The MariaDB fork of MySQL has a similar kind of SEQUENCE object, as does PostgreSQL. So if you were using MariaDB you would do something like this.

CREATE SEQUENCE IF NOT EXISTS org_account_id MINVALUE=10000 MAXVALUE=999999;
CREATE SEQUENCE IF NOT EXISTS user_account_id MINVALUE=1000000;

Then to use a sequence in place of autoincrement you'll do something like this.

INSERT INTO tbl (id, col1, col2) 
         VALUES (NEXTVAL(user_account_id), something, something);

In MySQL you can emulate sequence objects with dummy tables containing autoincrement ids. It's a kludge. Create the following table (one for each sequence).

CREATE TABLE user_account_id (
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
);
ALTER TABLE user_account_id AUTO_INCREMENT=1000000;

Then issue these queries one after the other to insert a row with a unique user id.

INSERT INTO user_account_id () VALUES (); 
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SET @id:=LAST_INSERT_ID();
INSERT INTO tbl (id, col1, col2) 
         VALUES (@id, something, something);

After your insert into the dummy table, LAST_INSERT_ID() returns a unique id. The DELETE query merely keeps this dummy table from taking up too much space.