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.