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

In MySQL, you use a column with the `AUTO_INCREMENT` attribute. — Barmar, Oct 26 '14 at 21:47
Adding "AUTO_INCREMENT" to the ORDID field only caused another error. There's an "INCREMENT" function, though. Would that work? — Ben, Oct 26 '14 at 21:56
"caused another error" --- did you not provide the message with purpose? — zerkms, Oct 26 '14 at 22:09
possible duplicate of [How to generate a dynamic sequence table in MySQL?](http://stackoverflow.com/questions/9058816/how-to-generate-a-dynamic-sequence-table-in-mysql) — Jocelyn, Oct 27 '14 at 18:19
MariaDB 10.3 will support sequences (https://mariadb.com/kb/en/library/sequences/) — Jared Beck, Dec 27 '17 at 19:43

score 46 · Answer 1 · edited Jun 20 '20 at 09:12

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.

This is best "sequence" solution so far, wrap into stored procedure: ```DELIMITER $$ CREATE PROCEDURE seq_next_id() BEGIN START TRANSACTION; UPDATE sequence SET id=LAST_INSERT_ID(id + 1); SELECT LAST_INSERT_ID() AS number; COMMIT; END $$ DELIMITER ;``` — Dai, Oct 22 '20 at 03:23

Daileyo · Accepted Answer · 2014-10-27T18:15:48.647

32

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.

edited Oct 27 '14 at 18:15

answered Oct 26 '14 at 22:12

Daileyo

710
5
14

Thanks for the help, but now MySQL claims that this code contains an "Incorrect column specifier". – Ben Oct 26 '14 at 22:23
I'm using a large sequence of code to create the tables from scratch each time (if something goes wrong I delete the tables and start over). MySQL never had a problem with "numeric(4)" before. – Ben Oct 26 '14 at 22:31
Disregard my prior comment. I have deleted it, as it is incorrect. – Daileyo Oct 26 '14 at 22:37
Extra bit of information: I'm using the MySQL servers on "phpMyAdmin". Not sure if that helps, but I feel like it's having an issue with the AUTO_INCREMENT command. – Ben Oct 26 '14 at 22:46
There is another link here on Stack Overflow that discusses the use of AUTO_INCREMENT in phpMyAdmin here: http://stackoverflow.com/questions/5665571/auto-increment-in-phpmyadmin/18942684#18942684 – Daileyo Oct 26 '14 at 22:51

Antonov Gazolov · Answer 3 · 2019-10-19T18:38:26.450

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.

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

Hi Antonov, is the sequence value unique with concurrent GetSequenceVal calls like Firebird? — Steve Kramer, Sep 30 '22 at 22:06

score 3 · Answer 4 · answered Mar 31 '16 at 10:48

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

score 1 · Answer 5 · answered Apr 25 '18 at 07:22

1

If You need sth different than AUTO_INCREMENT you can still use triggers.

answered Apr 25 '18 at 07:22

Marek Lisiecki

498
6
10

score 0 · Answer 6 · answered Aug 12 '22 at 00:30

I found that this works for me. Tell MYSQL that you are changing the column, but keeping the same name:

alter table ORD change ORDID ORDID int(10) unsigned auto_increment;

This tells the ORDID column in the ORD table to start auto incrementing. Then issue the following command:

ALTER TABLE ORD AUTO_INCREMENT = 85;

to start the next row at 85, for example.

How do I create a sequence in MySQL?

6 Answers6

Linked

Related