Prerequisites

Step to create a custom sequence like Oracle in MySQL

  1. Set global log_bin_trust_function_creator to 1
1
SET GLOBAL log_bin_trust_function_creators = 1;
  1. Create a sequence table
1
CREATE TABLE IF NOT EXISTS SEQUENCE (name VARCHAR(255) PRIMARY KEY, value INT UNSIGNED);
  1. Drop function nextval() if exists on your database
1
DROP FUNCTION IF EXISTS nextval;
  1. Create a custom sequence call nextval('sequence_name'), and will returns the next value. If name of sequence does not exists, it will created automatically by initial value 1.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DELIMITER //

CREATE FUNCTION nextval (sequence_name VARCHAR(255))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO SEQUENCE VALUES (sequence_name, LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE value=LAST_INSERT_ID(value+1);
RETURN LAST_INSERT_ID();
END
//
  1. Changing back delimiter to semicolon
1
DELIMITER ;
  1. Create table to test a custom sequence, default id defined by zero (0)
1
2
3
CREATE TABLE IF NOT EXISTS HUMAN
(id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
name VARCHAR(50));
  1. Drop nextval trigger if exists.
1
DROP TRIGGER IF EXISTS nextval;
  1. Create a custom trigger for nextval() function The trigger only generated a new id if 0 is inserted. So, if you create a new table and field id with default value by zero (0) that makes it implicit.
1
2
CREATE TRIGGER nextval_human BEFORE INSERT ON HUMAN
FOR EACH ROW SET new.id=IF(new.id=0,nextval('ID_HUMAN_SEQ'),new.id);
  1. Let’s try a sample data on HUMAN table
1
INSERT INTO HUMAN (name) VALUES ('Maverick'), ('John Doe'), ('Al Sah-Him');
  1. Inserted data look likes

HUMAN Data

  1. Let’s try another table BOOK to test a sequence
1
2
3
CREATE TABLE IF NOT EXISTS BOOK
(id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
name VARCHAR(50), code VARCHAR(50));
  1. Create ID_BOOK_SEQ trigger to a new sequence
1
2
CREATE TRIGGER nextval_book BEFORE INSERT ON BOOK
FOR EACH ROW SET new.id=IF(new.id=0,nextval('ID_BOOK_SEQ'),new.id);
  1. Insert data into BOOK table
1
INSERT INTO BOOK (name, code) VALUES ('Book 1', 'BK-01'), ('Book 2', 'BK-02'), ('Book 3', 'BK-03');
  1. Show the data

BOOK Data

  1. Let’s check generated custom sequence

Custom Sequence

Thankyou

Open Query - Implementing Sequences using a Stored Function and Triggers