CODEWITHSUNDEEP
mysqlpostgresqldatabase-sequence
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

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

Upvotes: 1

Views: 4067

Answers (3)

ASHWINI SHRIRANG
ASHWINI SHRIRANG

Reputation: 1

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..

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

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.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246013

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.

Upvotes: 1

Related Questions