Create Sequence Like Oracle in MySQL

Alvinditya Saputra included in Documentation

2020-12-04 338 words 2 minutes

Contents

Prerequisites

MySQL

Step to create a custom sequence like Oracle in MySQL

Set global log_bin_trust_function_creator to 1

1
SET GLOBAL log_bin_trust_function_creators = 1;

Create a sequence table

1
CREATE TABLE IF NOT EXISTS SEQUENCE (name VARCHAR(255) PRIMARY KEY, value INT UNSIGNED);

Drop function nextval() if exists on your database

1
DROP FUNCTION IF EXISTS nextval;

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
//

Changing back delimiter to semicolon

1
DELIMITER ;

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));

Drop nextval trigger if exists.

1
DROP TRIGGER IF EXISTS nextval;

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);

Let’s try a sample data on HUMAN table

1
INSERT INTO HUMAN (name) VALUES ('Maverick'), ('John Doe'), ('Al Sah-Him');

Inserted data look likes

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));

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);

Insert data into BOOK table

1
INSERT INTO BOOK (name, code) VALUES ('Book 1', 'BK-01'), ('Book 2', 'BK-02'), ('Book 3', 'BK-03');

Show the data

Let’s check generated custom sequence

Thankyou

Open Query - Implementing Sequences using a Stored Function and Triggers