MySQL相当于Oracle的SEQUENCE.NEXTVAL [英] MySQL equivalent of Oracle's SEQUENCE.NEXTVAL

问题描述

我需要能够生成一个查询，它将返回下表中的下一个ID值：

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)
)

在Oracle中，您可以在序列上调用NEXTVAL，它将提供下一个序列（注意：无需在表上执行插入操作）。

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).

在googling之后，我发现可以使用以下查询找到auto_increment的当前值：

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';

问题是，我希望每次查询值时增加该值。在Oracle中，当调用nextval时，即使不向表中插入行，序列的值也会增加。

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.

有没有办法我可以修改上面的查询，使返回的值总是不同于最后一次调用查询的时间？即Auto_increment在每次检查时递增，并且当在查询上使用时，它将使用新值。

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.

我使用Spring JDBCTemplate，所以如果可以在一个查询中做的更好。

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

推荐答案

InnoDB的示例演示了使用互锁查询实现自己的计数器的方法：

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

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

您需要为什么创建差距？要保留ID？
我宁愿修复设计的所有成本和更新其他模块，而不是触及一个序列。

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.

而不是只是明确增加序列，我暗示它通过插入一个默认行（标记为无效）为每个id分配和返回id。这种方法是一致和便携的。
稍后，不是强制使用显式序列值的插入，而是可以使用匹配的序列值更新这些默认行。
这需要更多的内存，但没有锁。过期行上的垃圾收集可以帮助这里。 'insert或update'语句可以重新创建垃圾收集行，我不会这样做。

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.

这篇关于MySQL相当于Oracle的SEQUENCE.NEXTVAL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！