如何在MySQL中创建序列?

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

我不知道为什么它更喜欢这个，但是如果有人需要帮助，你就可以了。:)

4 个解决方案

#1

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 link is 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 2 Here is a link to a working sqlfiddle example, using auto increment.
I hope this info helps.

更新2是一个使用自动增量的工作sqlfiddle示例的链接。我希望这个信息能有所帮助。

#2

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:

如果将expr作为一个参数传递给LAST_INSERT_ID()，则该函数的值将被函数返回，并被记住为LAST_INSERT_ID()返回的下一个值。这可以用来模拟序列:

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

UPDATE语句将增加序列计数器，并使下一个调用LAST_INSERT_ID()返回更新后的值。SELECT语句检索该值。还可以使用mysql_insert_id() C API函数来获取值。参见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.

您可以在不调用LAST_INSERT_ID()的情况下生成序列，但是这样使用函数的效用是，在服务器中维护ID值作为最后一个自动生成的值。它是多用户安全的，因为多个客户机可以发出UPDATE语句，并使用SELECT语句(或mysql_insert_id())获得它们自己的序列值，而不影响或受到生成它们自己序列值的其他客户机的影响。

#3

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存储大量的哑数据。否则，在mysql重新启动时，它将获得最大的现有行，并从该点继续增加，如文档http://dev.mysql.com/doc/refman/5.0/en/innodb-auto- incre-handling.html。

#4

If You need sth different than AUTO_INCREMENT you can still use triggers.

如果您需要与AUTO_INCREMENT不同的东西，您仍然可以使用触发器。

#1