oracle nextval mysql_MySQL相当于Oracle的SEQUENCE.NEXTVAL

I need to be able to generate run a query that will return the next value of ID on the following table:

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

)

In Oracle you can call NEXTVAL on a sequence and it gives you the next sequence (note: without having to do an insert on the table).

After googling around I found that you can find the current value of auto_increment by using the following query:

SELECT Auto_increment

FROM information_schema.tables

WHERE table_name='animals';

The problem is I would like the value to be increment every time the value is queried. In Oracle, when you call nextval, the value of the sequence is incremented even if you don't insert a row into a table.

Is there any way I can modify the above query so that the value returned will always be different from the last time the query was called? i.e. Auto_increment is incremented every time it is checked and when used on a query it would use a new value.

I am using Spring JDBCTemplate so if it can be done in one query the better.

解决方案

This example with InnoDB demonstrates a way to implement your own counter using interlocked queries:

What do you need to create a gap for? To reserve IDs?

I'd rather "fix" the design at all costs and update the other modules instead of touching a sequence.

Instead of just incrementing the sequence explicitly, I'd imply it by inserting a default row (marked invalid) for each id to allocate and return the id. This approach is consistent and portable.

Later, instead of forcing inserts using an explicit sequence value, you can update these default rows by their matching sequence values.

This requires more memory but no locks. Garbage collection on expired rows can help here. 'insert or update' statements can recreate garbage collected rows, I wouldn't do this though.