How do I create a sequence in MySQL?

Question

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

Answer 1

This is a solution suggested by the MySQl manual:

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.

Answer 2

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.