MySQL 相当于 Oracle 的 SEQUENCE.NEXTVAL

Question

提问by ziggy

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

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

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

在 Oracle 中，您可以对序列调用 NEXTVAL，它会为您提供下一个序列（注意：无需在表上进行插入）。

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

谷歌搜索后，我发现您可以使用以下查询找到 auto_increment 的当前值：

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.

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

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.

有什么方法可以修改上述查询，以便返回的值始终与上次调用查询时不同？即 Auto_increment 每次被检查时都会增加，当用于查询时，它将使用一个新值。

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

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

Answer 1

采纳答案by Sam

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

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

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.

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

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.

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

Answer 2

回答by Ivan M.

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

3.6.9. Using AUTO_INCREMENT

3.6.9. 使用 AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

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

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

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

没有为 AUTO_INCREMENT 列指定值，因此 MySQL 自动分配序列号。您还可以为列显式分配 NULL 或 0 以生成序列号。

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

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

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255. See Section 11.2.1, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.

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

Note For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

注意对于多行插入，LAST_INSERT_ID() 和 mysql_insert_id() 实际上从插入的第一行返回 AUTO_INCREMENT 键。这使多行插入能够在复制设置中的其他服务器上正确复制。

If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

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

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:

要从 1 以外的 AUTO_INCREMENT 值开始，请使用 CREATE TABLE 或 ALTER TABLE 设置该值，如下所示：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100; InnoDB Notes

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100; InnoDB 注释

For InnoDB tables, be careful if you modify the column containing the auto-increment value in the middle of a sequence of INSERT statements. For example, if you use an UPDATE statement to put a new, larger value in the auto-increment column, a subsequent INSERT could encounter a “Duplicate entry” error. The test whether an auto-increment value is already present occurs if you do a DELETE followed by more INSERT statements, or when you COMMIT the transaction, but not after an UPDATE statement.

对于 InnoDB 表，如果在 INSERT 语句序列的中间修改包含自动增量值的列，请小心。例如，如果您使用 UPDATE 语句在自动增量列中放置一个新的更大的值，则后续的 INSERT 可能会遇到“重复条目”错误。如果您执行 DELETE 后跟更多 INSERT 语句，或者当您 COMMIT 事务时，而不是在 UPDATE 语句之后，则测试是否已经存在自动增量值。

MyISAM Notes

MyISAM 笔记

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

对于 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 |
+--------+----+---------+

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

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

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

回答by ziesemer

You could add your own MySQL function - as demonstrated at http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/- allowing you to do something like this:

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

SELECT nextval('sq_my_sequence') as next_sequence;

Answer 4

回答by ziesemer

MySQL uses AUTO_INCREMENTwhich serves the purposes. But there are below differences:

MySQL 使用AUTO_INCREMENT它来达到目的。但有以下区别：

MySQL AUTO_INCREMENTto Oracle SEQUENCEDifferences

MySQLAUTO_INCREMENT到 Oracle 的SEQUENCE差异

AUTO_INCREMENT is limited to one column per table
AUTO_INCREMENT must be assigned to a specific table.column (not allowing multi table use)
AUTO_INCREMENT is INSERTed as a not specified column, or a value of NULL

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

if you would like to see a SEQUENCE implementation with MySQL , can do with SP.

如果您想查看 MySQL 的 SEQUENCE 实现，可以使用 SP。

Refer below link explained everything you want.

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

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

Answer 5

回答by Cade Roux

You want the next value on THAT table so that you can make rows which aren't yet inserted without disturbing other processes which are using the auto-increment?

您想要那个表上的下一个值，以便您可以创建尚未插入的行，而不会干扰使用自动增量的其他进程？

Some options:

一些选项：

Go ahead and just insert the rows to "use up the sequence", mark them as pending and then update them later.

继续，只需插入行以“用完序列”，将它们标记为待处理，然后稍后更新它们。

Insert in a transaction and abort the transaction - the auto-number sequence should get used up and make a "gap" in the table normally - that number is now free for you to use.

插入交易并中止交易 - 自动编号序列应该会用完并正常地在表中产生“间隙” - 该号码现在可供您免费使用。

With Oracle, the sequence is completely independent of table, so processes can use the sequence or not (and they can also use the same sequence for different tables). In that vein, you could implement a sequence-only table which you access through some kind of function, and for the other processes which need to rely on the auto-increment, remove the auto-increment and use a trigger which assigns from the same sequence function if no id is provided.

在 Oracle 中，序列完全独立于表，因此进程可以使用或不使用序列（并且它们也可以对不同的表使用相同的序列）。在这种情况下，您可以实现一个仅序列表，您可以通过某种函数访问该表，并且对于需要依赖自动增量的其他进程，删除自动增量并使用从相同位置分配的触发器如果未提供 id，则使用序列函数。

Answer 6

回答by Benvorth

Have a look at MariaDBs SEQUENCEEngine. With it you can generate sequences as easy as

看看 MariaDBsSEQUENCE引擎。有了它，您可以轻松地生成序列

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

Details here: https://mariadb.com/kb/en/mariadb/sequence/

详情请见：https://mariadb.com/kb/en/mariadb/sequence/

With this you should be able to do something like

有了这个，你应该能够做类似的事情

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

Answer 7

回答by Sumit

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;

MySQL 相当于 Oracle 的 SEQUENCE.NEXTVAL

提问by ziggy

采纳答案by Sam

回答by Ivan M.

回答by ziesemer

回答by ziesemer

回答by Cade Roux

回答by Benvorth

回答by Sumit

相关推荐

最近更新

标签

MySQL 相当于 Oracle 的 SEQUENCE.NEXTVAL

提问by ziggy

采纳答案by Sam

回答by Ivan M.

回答by ziesemer

回答by ziesemer

回答by Cade Roux

回答by Benvorth

回答by Sumit

相关推荐

ios 闭包不能隐式捕获变异的 self 参数

ios 在 swift 3 中使用对象执行选择器

ios 对成员 Swift 3 的模棱两可的引用

ios Swift：将参数传递给选择器

相关推荐

最近更新

标签