Geek asks and answers

Mysql insert select in the same expression

I want to simulate an auto_increment script during insertion without a field having the auto_increment property. Here is the script for the sql statement:

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

Basically, I want the insertion and selection to be done with the same , so I can guarantee that the vid value is unique.

+3
source share
3 answers

If you want the auto-increment value to be divided between transactions with guaranteed uniqueness, you must have a locked singleton visible for all transactions that retain the last unique value.

In MyISAMit is stored in the table metadata in InnoDBa special memory object filled MAX(id)in the first insert after the server starts.

, (, ), , , .

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

You can do all this in a transaction to ensure that no one else gets access to the table:

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

More articles:


All Articles