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

User · Answer

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.

User · Answer

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

User · Answer

By creating the increment table you should be aware not to delete inserted rows  reason for this is to avoid storing large dumb data in db with ID-s in it  Otherwise in case of mysql restart it would get max existing row and continue increment from that point as mention in documentation http   dev mysql com doc refman 5 0 en innodb-auto-increment-handling html

User · Answer

If You need sth different than AUTO INCREMENT you can still use triggers

User · Answer

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  lt  gt  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    --

[mysql] How do I create a sequence in MySQL?

Examples related to mysql

Examples related to sql

Examples related to sequence