如何在 MySQL 中创建序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26578313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a sequence in MySQL?
提问by Ben
I'm trying to create a sequence in MySQL (I'm very new to SQL as a whole). I'm using the following code, but it causes an error:
我正在尝试在 MySQL 中创建一个序列（作为一个整体，我对 SQL 非常陌生）。我正在使用以下代码，但它会导致错误：
CREATE SEQUENCE ORDID INCREMENT BY 1 START WITH 622;
ORDID refers to a field in a table I'm using. How do I create the sequence properly?
ORDID 指的是我正在使用的表中的一个字段。如何正确创建序列？
Edit:
编辑：
Allegedly, MySQL doesn't use sequences. I'm now using the following code, but this is causing errors too. How do I fix them?
据称，MySQL 不使用序列。我现在正在使用以下代码，但这也会导致错误。我该如何修复它们？
CREATE TABLE ORD (
ORDID NUMERIC(4) NOT NULL AUTO_INCREMENT START WITH 622,
//Rest of table code
Edit:
编辑：
I think I found a fix. For phpMyAdmin (which I was using) you can use the following code.
我想我找到了解决办法。对于 phpMyAdmin（我正在使用），您可以使用以下代码。
ALTER TABLE ORD AUTO_INCREMENT = 622;
I have no idea why it prefers this, but if anyone else needs help with this then here you go. :)
我不知道为什么它更喜欢这个，但如果其他人需要这方面的帮助，那么你就去吧。:)
回答by pupitetris
This is a solution suggested by the MySQl manual:
这是MySQl 手册建议的解决方案：
If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:
Create a table to hold the sequence counter and initialize it:
mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0);Use the table to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> SELECT LAST_INSERT_ID();The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 23.8.7.37, “mysql_insert_id()”.
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.
如果 expr 作为 LAST_INSERT_ID() 的参数给出，则该参数的值由函数返回，并作为 LAST_INSERT_ID() 返回的下一个值被记住。这可用于模拟序列：
创建一个表来保存序列计数器并初始化它：
mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0);使用该表生成如下序列号：
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> SELECT LAST_INSERT_ID();UPDATE 语句增加序列计数器并导致对 LAST_INSERT_ID() 的下一次调用返回更新后的值。SELECT 语句检索该值。mysql_insert_id() C API 函数也可用于获取该值。见第 23.8.7.37 节，“mysql_insert_id()”。
您可以在不调用 LAST_INSERT_ID() 的情况下生成序列，但是以这种方式使用该函数的效用是 ID 值在服务器中作为最后自动生成的值保留在服务器中。它是多用户安全的，因为多个客户端可以发出 UPDATE 语句并使用 SELECT 语句（或 mysql_insert_id()）获取自己的序列值，而不会影响其他生成自己序列值的客户端或受其影响。
回答by Daileyo
Check out this article. I believe it should help you get what you are wanting. If your table already exists, and it has data in it already, the error you are getting may be due to the auto_increment trying to assign a value that already exists for other records.
看看这篇文章。我相信它应该可以帮助你得到你想要的。如果您的表已经存在，并且其中已经有数据，那么您得到的错误可能是由于 auto_increment 试图为其他记录分配一个已经存在的值。
In short, as others have already mentioned in comments, sequences, as they are thought of and handled in Oracle, do not exist in MySQL. However, you can likely use auto_increment to accomplish what you want.
简而言之，正如其他人在评论中已经提到的那样，在 Oracle 中考虑和处理的序列在 MySQL 中不存在。但是，您可能可以使用 auto_increment 来完成您想要的。
Without additional details on the specific error, it is difficult to provide more specific help.
如果没有关于具体错误的额外细节，很难提供更具体的帮助。
UPDATE
更新
CREATE TABLE ORD (
ORDID INT NOT NULL AUTO_INCREMENT,
//Rest of table code
PRIMARY KEY (ordid)
)
AUTO_INCREMENT = 622;
This linkis also helpful for describing usage of auto_increment. Setting the AUTO_INCREMENT value appears to be a table option, and not something that is specified as a column attribute specifically.
此链接也有助于描述 auto_increment 的用法。设置 AUTO_INCREMENT 值似乎是一个表选项，而不是专门指定为列属性的内容。
Also, per one of the links from above, you can alternatively set the auto increment start value via an alter to your table.
此外，根据上面的链接之一，您还可以通过更改表来设置自动增量起始值。
ALTER TABLE ORD AUTO_INCREMENT = 622;
UPDATE 2Here is a link to a working sqlfiddle example, using auto increment.
I hope this info helps.
更新 2这是一个使用自动增量的工作 sqlfiddle 示例的链接。
我希望这些信息有帮助。
回答by JNZ
By creating the increment table you should be aware not to delete inserted rows. reason for this is to avoid storing large dumb data in db with ID-s in it. Otherwise in case of mysql restart it would get max existing row and continue increment from that point as mention in documentation http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html
通过创建增量表，您应该注意不要删除插入的行。这样做的原因是为了避免在 db 中存储大量的哑数据，其中包含 ID-s。否则，在 mysql 重启的情况下，它将获得最大现有行并从该点继续递增，如文档http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html 中所述
回答by Antonov Gazolov
SEQUENCES like it works on firebird:
SEQUENCES like it works on firebird:
-- =======================================================
-- ================================================ ========
CREATE TABLE SEQUENCES
(
NM_SEQUENCE VARCHAR(32) NOT NULL UNIQUE,
VR_SEQUENCE BIGINT NOT NULL
);
-- =======================================================
-- Creates a sequence sSeqName and set its initial value.
-- =======================================================
-- ================================================ =======
-创建一个序列sSeqName并设定其初始值。
-- ================================================ ========
DROP PROCEDURE IF EXISTS CreateSequence;
DELIMITER :)
CREATE PROCEDURE CreateSequence( sSeqName VARCHAR(32), iSeqValue BIGINT )
BEGIN
IF NOT EXISTS ( SELECT * FROM SEQUENCES WHERE (NM_SEQUENCE = sSeqName) ) THEN
INSERT INTO SEQUENCES (NM_SEQUENCE, VR_SEQUENCE)
VALUES (sSeqName , iSeqValue );
END IF;
END :)
DELIMITER ;
-- CALL CreateSequence( 'MySequence', 0 );
-- =======================================================================
-- Increments the sequence value of sSeqName by iIncrement and returns it.
-- If iIncrement is zero, returns the current value of sSeqName.
-- =======================================================================
-- ================================================ ========================
-- 将 sSeqName 的序列值增加 iIncrement 并返回。
-- 如果 iIncrement 为零，则返回 sSeqName 的当前值。
-- ================================================ ========================
DROP FUNCTION IF EXISTS GetSequenceVal;
DELIMITER :)
CREATE FUNCTION GetSequenceVal( sSeqName VARCHAR(32), iIncrement INTEGER )
RETURNS BIGINT -- iIncrement can be negative
BEGIN
DECLARE iSeqValue BIGINT;
SELECT VR_SEQUENCE FROM SEQUENCES
WHERE ( NM_SEQUENCE = sSeqName )
INTO @iSeqValue;
IF ( iIncrement <> 0 ) THEN
SET @iSeqValue = @iSeqValue + iIncrement;
UPDATE SEQUENCES SET VR_SEQUENCE = @iSeqValue
WHERE ( NM_SEQUENCE = sSeqName );
END IF;
RETURN @iSeqValue;
END :)
DELIMITER ;
-- SELECT GetSequenceVal('MySequence', 1); -- Adds 1 to MySequence value and returns it.
-- ===================================================================
-- ================================================ ====================
回答by Marek Lisiecki
If You need sth different than AUTO_INCREMENT you can still use triggers.
如果您需要与 AUTO_INCREMENT 不同的东西，您仍然可以使用触发器。
