How do I create a sequence in MySQL?

Solution 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.

Solution 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.

Solution 3:

SEQUENCES like it works on firebird:

-- =======================================================

CREATE TABLE SEQUENCES  
(  
  NM_SEQUENCE VARCHAR(32) NOT NULL UNIQUE,  
  VR_SEQUENCE BIGINT      NOT NULL  
);  

-- =======================================================
-- Creates a sequence sSeqName and set its initial value.
-- =======================================================

DROP PROCEDURE IF EXISTS CreateSequence;  

DELIMITER :)  
CREATE PROCEDURE CreateSequence( sSeqName VARCHAR(32), iSeqValue BIGINT )  
BEGIN  
  IF NOT EXISTS ( SELECT * FROM SEQUENCES WHERE (NM_SEQUENCE = sSeqName) ) THEN  
    INSERT INTO SEQUENCES (NM_SEQUENCE, VR_SEQUENCE)  
    VALUES (sSeqName   , iSeqValue  );  
  END IF;  
END :)  
DELIMITER ;  

-- CALL CreateSequence( 'MySequence', 0 );  

-- =======================================================================
-- Increments the sequence value of sSeqName by iIncrement and returns it.
-- If iIncrement is zero, returns the current value of sSeqName.
-- =======================================================================

DROP FUNCTION IF EXISTS GetSequenceVal;  

DELIMITER :)  
CREATE FUNCTION GetSequenceVal( sSeqName VARCHAR(32), iIncrement INTEGER )  
RETURNS BIGINT  -- iIncrement can be negative  
BEGIN  
  DECLARE iSeqValue BIGINT;  

  SELECT VR_SEQUENCE FROM SEQUENCES  
  WHERE  ( NM_SEQUENCE = sSeqName )  
  INTO   @iSeqValue;  

  IF ( iIncrement <> 0 ) THEN  
    SET @iSeqValue = @iSeqValue + iIncrement;  

    UPDATE SEQUENCES SET VR_SEQUENCE = @iSeqValue  
    WHERE  ( NM_SEQUENCE = sSeqName );  
  END IF;

  RETURN @iSeqValue;
END :)  
DELIMITER ;  

-- SELECT GetSequenceVal('MySequence', 1);  -- Adds 1 to MySequence value and returns it.

-- ===================================================================