4 Simple Steps to Emulating Real Sequences in MySQL

Submitted by intellicon on Tuesday, July 9, 2013 - 00:24.
If all you want to do is create a unique ID for a record, the auto_increment feature in MySQL will do the job nicely. Sometimes, however, you need more functionality. Unfortunately, unlike Oracle or PostgreSQL, MySQL doesn't have native support for the nextval() function, so creating real sequences is a little more challenging. First, we need to define what we want the nextval() function to achieve. We want to be able to begin the sequence with any number, and auto increment in multiples of any number, not just 1, including negative integers (-1 for example). We would also like the ability to define a maximum or minimum value, and have the sequence reset itself when that parameter is reached. Finally, we have to be able to determine the next number in the sequence. 1. Create the table. First things first. Before we can implement a sequence, we first need to create the table.
  1. CREATE TABLE `sequence` (
  2.     `name` VARCHAR(100) NOT NULL,
  3.     `increment` INT(11) NOT NULL DEFAULT 1,
  4.     `min_value` INT(11) NOT NULL DEFAULT 1,
  5.     `max_value` BIGINT(20) NOT NULL DEFAULT 9223372036854775807,
  6.     `cur_value` BIGINT(20) DEFAULT 1,
  7.     `cycle` BOOLEAN NOT NULL DEFAULT FALSE,
  8.     PRIMARY KEY (`name`)
  9. ) ENGINE=MyISAM;
2. Insert the values. Now we define the parameters of the sequence, and specify its values. In this example, we're cycling down from 100 to 0 with step 2.
  1. INSERT INTO SEQUENCE
  2.     ( name, INCREMENT, min_value, max_value, cur_value )
  3. VALUES
  4.     ('my_sequence', -2, 0, 100, 100, 1);
3. Define nextval(). Finally, we create the nextval() function to determine the next number in the sequence, and to reset the counter when the minimum value is exceeded.
  1. DELIMITER $$
  2. CREATE FUNCTION `nextval` (`seq_name` VARCHAR(100))
  3. RETURNS BIGINT NOT DETERMINISTIC
  4. BEGIN
  5.     DECLARE cur_val BIGINT;
  6.  
  7.     SELECT
  8.         cur_value INTO cur_val
  9.     FROM
  10.         SEQUENCE
  11.     WHERE
  12.         name = seq_name;
  13.  
  14.     IF cur_val IS NOT NULL THEN
  15.         UPDATE
  16.             SEQUENCE
  17.         SET
  18.             cur_value = IF (
  19.                 (cur_value + INCREMENT) > max_value OR (cur_value + INCREMENT) < min_value,
  20.                 IF (
  21.                     cycle = TRUE,
  22.                     IF (
  23.                         (cur_value + INCREMENT) > max_value,
  24.                         min_value,
  25.                         max_value
  26.                     ),
  27.                     NULL
  28.                 ),
  29.                 cur_value + INCREMENT
  30.             )
  31.         WHERE
  32.             name = seq_name;
  33.     END IF;
  34.     RETURN cur_val;
  35. END;
  36. $$
4. Accessing the sequence. For our countdown sequence to be useful, we need to be able to call the function and extract the values from the sequence. Here's how: SELECT NEXTVAL('my_sequence'); So there you have it. Four simple steps to create a real sequence in MySQL for use in your databases. For more tips and tricks to help you make the most of MySQL and other popular databases, visit: http://www.convert-in.com/articles.htm
Tags
MYSQL
sequence
sql code
emulate

Add new comment

About text formats
  • 77 views