mysql insert select in same statement

Question

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.

Answer 1

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.