How to increment id without auto increment?

Question

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)?

Answer 1

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.

Answer 2

I recommend that you use a normal sequence-based bigint column. Then, on SELECT, add the base for the appropriate account type to the column.

Answer 3

PreparedStatement ps = con.prepareStatement("insert into emp(emp_code,emp_name,join_date,designation,birth_date,gender,mobile) values((select max(emp_code)+1 from emp),?,?,?,?,?,?)")

This query will definitely help..