MySQL中与Oracle的SEQUENCE.NEXTVAL相当的功能是什么？

Question

MySQL中与Oracle的SEQUENCE.NEXTVAL相当的功能是什么？

22

我需要能够生成并运行一个查询，以返回以下表格中ID的下一个值：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
)

在Oracle中，你可以调用序列的NEXTVAL，它会给你下一个序列值（注意：无需在表上执行插入操作）。

通过搜索，我发现你可以使用以下查询来查找auto_increment的当前值：

SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name='animals';

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

有没有办法修改上面的查询，以便返回的值始终与上一次调用查询时不同？即Auto_increment每次检查时都会增加，当在查询上使用时，它将使用新值。

我正在使用Spring JDBCTemplate，因此如果可以在一个查询中完成就更好了。

- ziggy

7个回答

7

这个带有InnoDB的例子演示了使用交错查询实现自己的计数器的方法： http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html 你需要创建间隙来做什么？为了保留ID？我宁愿不惜一切代价“修复”设计并更新其他模块，而不是去修改序列。

我会通过插入一个默认行（标记为无效）来暗示每个要分配的ID，并获取ID，而不仅仅是显式地递增序列。这种方法是一致且便携的。稍后，您可以通过它们的匹配序列值更新这些默认行，而不是强制使用显式序列值进行插入。这需要更多的内存，但不需要锁。过期行上的垃圾收集可以帮助解决此问题。'插入或更新'语句可以重新创建已被垃圾回收的行，但我不建议这样做。

- Sam

2

不惜一切代价？真的吗？ - lmat - Reinstate Monica

5

您可以添加自己的MySQL函数-如http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/所示-使您能够像这样执行操作：

SELECT nextval('sq_my_sequence') as next_sequence;

- ziesemer

我在谷歌搜索时看到了这个例子。不幸的是，它需要删除ID列上的AUTO_INCREMENT，但目前这是不可能的，因为我将需要更改使用此表的其他模块 - 谢谢。 - ziggy

1

在那个页面的评论中，有人提出了可能存在竞态条件的担忧。使用@Ivan提供的auto_increment方法似乎更容易，其中工作在单个语句中完成，因此是原子性的。您可以将Ivan的方法包装在一个函数中，这将很好，让您获得两种方法的最佳效果。 - Peter Bagnall

你能否更新你的答案，提供实际内容而不是链接？你提供的查询本身是无法工作的。 - jangorecki

3

MySQL使用AUTO_INCREMENT实现自增功能，但以下是与Oracle的SEQUENCE存在差异：

MySQL AUTO_INCREMENT 与 Oracle SEQUENCE 的区别：

AUTO_INCREMENT限于每个表一个列。
AUTO_INCREMENT必须分配给特定的table.column（不允许多表使用）。
AUTO_INCREMENT作为未指定的列或NULL值插入。

如果您想在MySQL中看到SEQUENCE的实现，可以使用SP完成。

请参考下面的链接，解释了你想要的一切。

http://ronaldbradford.com/blog/sequences-in-mysql-2006-01-26/

- user319198

2

看一下MariaDB的SEQUENCE引擎。使用它，您可以轻松生成序列，就像这样：

SELECT seq FROM seq_1_to_5;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

详情请点击这里：https://mariadb.com/kb/en/mariadb/sequence/

使用此功能，您可以做出类似以下的操作：

SELECT min(seq) as nextVal FROM seq_1_to_500 
WHERE seq NOT IN (SELECT ID FROM customers)

- Benvorth

1

有趣的事情，但很可能不是问题的答案。 - hgoebl

1

你想要从那个表中获取下一个值，以便可以插入尚未插入的行，而不会干扰正在使用自增的其他进程吗？

一些选项：

直接插入行以“使用序列”，将它们标记为待处理，然后稍后更新它们。

在事务中插入并中止事务 - 自动编号序列应该被使用并在表中留下“间隙” - 现在这个数字对您是免费的。

对于Oracle，序列完全独立于表，因此进程可以使用序列或不使用（它们还可以为不同的表使用相同的序列）。在这方面，您可以实现一个仅包含序列的表，通过某种函数访问它，并且对于需要依赖自增的其他进程，删除自增并使用触发器，如果没有提供ID，则从相同的序列函数分配。

- Cade Roux

0

1.) create table query
2.) Insert query
3.) Update query
4.) Select query for that table e.g. SELECT next_val FROM hib_sequence;
5.) Before each select update first, like this you can achieve similar

-- CREATE TABLE hib_sequence (next_val INT NOT NULL);
-- UPDATE hib_sequence SET next_val=LAST_INSERT_ID(next_val+1);
-- select last_insert_id();
-- INSERT INTO hib_sequence VALUES (0);

-- select next_val from hib_sequence;

- Sumit Anand

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ivan M. · Accepted Answer

http://docs.oracle.com/cd/E17952_01/refman-5.5-en/example-auto-increment.html

3.6.9. 使用AUTO_INCREMENT

AUTO_INCREMENT属性可用于为新行生成唯一标识：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

未指定AUTO_INCREMENT列的值，因此MySQL会自动分配序列号。您也可以显式将NULL或0分配给该列，以生成序列号。

使用LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数检索最近的AUTO_INCREMENT值。这些函数是连接特定的，因此它们的返回值不受同时执行插入操作的另一个连接的影响。

为AUTO_INCREMENT列使用足够大的最小整数数据类型，以容纳所需的最大序列值。当该列达到数据类型的上限时，下一个尝试生成序列号将失败。如果可能，请使用UNSIGNED属性以允许更大的范围。例如，如果使用TINYINT，则最大允许的序列号为127。对于TINYINT UNSIGNED，最大值为255。有关所有整数类型范围的信息，请参见第11.2.1节“整数类型(精确值) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT”。

对于多行插入，LAST_INSERT_ID()和mysql_insert_id()实际上返回插入行中第一个的AUTO_INCREMENT键。这使得可以在复制设置中正确重现多行插入。

如果AUTO_INCREMENT列是多个索引的一部分，则MySQL使用以AUTO_INCREMENT列开头的索引生成序列值（如果存在）。例如，如果animals表包含PRIMARY KEY(grp,id)和INDEX(id)索引，则MySQL将忽略PRIMARY KEY以生成序列值。因此，该表将包含单个序列，而不是每个grp值的序列。

要以1之外的AUTO_INCREMENT值开始，请使用CREATE TABLE或ALTER TABLE设置该值，例如：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB注意事项

对于InnoDB表，在INSERT语句序列的中间修改包含自增值的列时要小心。例如，如果使用UPDATE语句在自增列中放置一个新的、较大的值，后续的INSERT可能会遇到“重复条目”错误。是否存在自增值的测试发生在执行DELETE后跟更多的INSERT语句，或者在提交事务之后，但不是在UPDATE语句之后。

MyISAM注意事项

对于MyISAM表，您可以在多列索引中指定AUTO_INCREMENT在次要列上。在这种情况下，AUTO_INCREMENT列的生成值计算为MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。当您想将数据放入有序组中时，这非常有用。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

在这种情况下（当AUTO_INCREMENT列是多列索引的一部分时），如果删除任何组中具有最大AUTO_INCREMENT值的行，则会重用AUTO_INCREMENT值。即使对于通常不重用AUTO_INCREMENT值的MyISAM表也是如此。

进一步阅读：

有关AUTO_INCREMENT的更多信息，请参见此处：

如何将AUTO_INCREMENT属性分配给列：第13.1.17节“CREATE TABLE Syntax”和第13.1.7节“ALTER TABLE Syntax”。

根据NO_AUTO_VALUE_ON_ZERO SQL模式，AUTO_INCREMENT的行为如何变化：第5.1.7节“服务器SQL模式”。

如何使用LAST_INSERT_ID（）函数查找包含最新AUTO_INCREMENT值的行：第12.14节“信息函数”。

设置要使用的AUTO_INCREMENT值：第5.1.4节“服务器系统变量”。

与AUTO_INCREMENT相关的服务器系统变量（auto_increment_increment和auto_increment_offset）可用于复制：第5.1.4节“服务器系统变量”。 http://search.oracle.com/search/search?q=auto_increment&group=Documentation&x=0&y=0