如何在MySQL中创建序列? [英] How do I create a sequence in MySQL?

问题描述

我正在尝试在MySQL中创建一个序列(从整体上来说，我对SQL还是很陌生的).我正在使用以下代码，但这会导致错误:

CREATE SEQUENCE ORDID INCREMENT BY 1 START WITH 622;

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

据称，MySQL不使用序列.我现在正在使用以下代码，但这也会引起错误.我该如何解决?

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

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

ALTER TABLE ORD AUTO_INCREMENT = 622;

我不知道为什么会选择它，但是如果其他任何人需要帮助，那么您就可以开始. :)

解决方案

查看本文.我相信它应该可以帮助您获得所需的东西.如果您的表已经存在，并且其中已经有数据，则出现的错误可能是由于auto_increment试图为其他记录分配一个已经存在的值.

简而言之，正如其他人在评论中已经提到的那样，在Oracle中考虑和处理的序列在MySQL中不存在.但是，您可能可以使用auto_increment完成所需的操作.

没有关于特定错误的其他详细信息，很难提供更具体的帮助.

更新

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

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

此外，您也可以通过上方的一个链接，通过更改表来设置自动增量开始值.

ALTER TABLE ORD AUTO_INCREMENT = 622;

更新2 这是使用自动递增功能链接到有效的sqlfiddle示例的链接.
希望该信息对您有所帮助.

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 refers to a field in a table I'm using. How do I create the sequence properly?

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?

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.

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

解决方案

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.

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.

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.

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.

这篇关于如何在MySQL中创建序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！