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

在谷歌搜索后我发现您可以使用以下查询找到 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

您需要为什么创建间隙?保留身份证?我宁愿不惜一切代价修复"设计并更新其他模块，而不是触及序列.

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 来暗示它.这种方法是一致且可移植的.稍后，您可以通过匹配的序列值更新这些默认行，而不是使用显式序列值强制插入.这需要更多内存但不需要锁.过期行上的垃圾收集可以在这里提供帮助.插入或更新"语句可以重新创建垃圾收集行，但我不会这样做.

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屋！