CODEWITHSUNDEEP
mysqlsql
Ken
Ken

Reputation: 2959

mysql insert select in same statement

I want to simulate an auto_increment scenario during an insert without the field having the auto_increment property. Here is the scenario sql statement:

insert into acct set id=(select @vid:=max(id)+1); select @vid;

Basically, I want the insert and select done at the same time so I can guarantee the value of vid is unique.

Upvotes: 3

Views: 1753

Answers (3)

Quassnoi
Quassnoi

Reputation: 425803

If you want the autoincremented value to be shared across transactions with guaranteed uniqueness, you should have a lockable singleton visible to all transactions which would hold the last unique value.

In MyISAM, it is stored in the table's metadata, in InnoDB, in a special memory object populated with MAX(id) on the first insert after the server startup.

Of course you can make your own (say a dedicated table with a single record), but, honestly, I don't see any benefits over the build-in functionality in such a solution.

Upvotes: 1

krtek
krtek

Reputation: 26617

You can do all this in a transaction to guarantee no one else will access the table :

START TRANSACTION;
insert into acct set id=(select @vid:=max(id)+1);
select @vid;
COMMIT;

Upvotes: 0

bensiu
bensiu

Reputation: 25604

insert into acct values ( (select max(id)+1 from acct), ... rest_of_vars );

Upvotes: 0

Related Questions