如何在 MySQL 中创建序列?

2021-11-20 00:00:00 sql sequence mysql

我正在尝试在 MySQL 中创建一个序列(作为一个整体，我对 SQL 非常陌生).我正在使用以下代码，但它会导致错误:

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:

CREATE SEQUENCE ORDID INCREMENT BY 1 START WITH 622;

ORDID 指的是我正在使用的表中的一个字段.如何正确创建序列?

ORDID refers to a field in a table I'm using. How do I create the sequence properly?

据称，MySQL 不使用序列.我现在正在使用以下代码，但这也会导致错误.我该如何修复它们?

Allegedly, MySQL doesn't use sequences. I'm now using the following code, but this is causing errors too. How do I fix them?

CREATE TABLE ORD (
ORDID NUMERIC(4) NOT NULL AUTO_INCREMENT START WITH 622,
//Rest of table code

我想我找到了解决办法.对于 phpMyAdmin(我正在使用)，您可以使用以下代码.

I think I found a fix. For phpMyAdmin (which I was using) you can use the following code.

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

推荐答案

查看 这篇文章.我相信它应该可以帮助你得到你想要的.如果您的表已经存在，并且其中已经有数据，那么您得到的错误可能是由于 auto_increment 试图为其他记录分配一个已经存在的值.

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.

简而言之，正如其他人在评论中已经提到的那样，在 Oracle 中考虑和处理的序列在 MySQL 中不存在.但是，您可能可以使用 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.

如果没有关于具体错误的额外细节，很难提供更具体的帮助.

Without additional details on the specific error, it is difficult to provide more specific help.

更新

CREATE TABLE ORD (
  ORDID INT NOT NULL AUTO_INCREMENT,
  //Rest of table code
  PRIMARY KEY (ordid)
)
AUTO_INCREMENT = 622;

这个链接也有助于描述 auto_increment 的用法.设置 AUTO_INCREMENT 值似乎是一个 表选项，而不是专门指定为列属性.

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.

此外，根据上面的链接之一，您还可以通过更改表来设置自动增量起始值.

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;

更新 2这是使用自动增量的工作 sqlfiddle 示例的链接.
我希望这些信息有帮助.

UPDATE 2 Here is a link to a working sqlfiddle example, using auto increment.
I hope this info helps.

相关文章