sql - MySQL equivalent of Oracle's SEQUENCE.NEXTVAL -

sql - MySQL equivalent of Oracle's SEQUENCE.NEXTVAL -

i need able generate run query homecoming next value of id on next table:

create table animals ( id mediumint not null auto_increment, name char(30) not null, primary key (id) )

in oracle can phone call nextval on sequence , gives next sequence (note: without having insert on table).

after googling around found can find current value of auto_increment using next query:

select auto_increment information_schema.tables table_name='animals';

the problem value increment every time value queried. in oracle, when phone call nextval, value of sequence incremented if don't insert row table.

is there way can modify above query value returned different lastly time query called? i.e. auto_increment incremented every time checked , when used on query utilize new value.

i using spring jdbctemplate if can done in 1 query better.

this illustration innodb demonstrates way implement own counter using interlocked queries:

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

what need create gap for? reserve ids? i'd rather "fix" design @ costs , update other modules instead of touching sequence.

instead of incrementing sequence explicitly, i'd imply inserting default row (marked invalid) each id allocate , homecoming id. approach consistent , portable. later, instead of forcing inserts using explicit sequence value, can update these default rows matching sequence values. requires more memory no locks. garbage collection on expired rows can help here. 'insert or update' statements can recreate garbage collected rows, wouldn't though.

mysql sql database oracle spring