Example code for implementing auto-increment sequence in mysql

1. Create a sequence table

CREATE TABLE `sequence` (
 `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Sequence name',
 `current_value` int(11) NOT NULL COMMENT 'Current value of the sequence',
 `increment` int(11) NOT NULL DEFAULT '1' COMMENT 'Sequence auto-increment',
 PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. Create – Function to get current value

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
   RETURNS INTEGER
   LANGUAGE SQL 
   DETERMINISTIC 
   CONTAINS SQL 
   SQL SECURITY DEFINER 
   COMMENT ''
BEGIN
   DECLARE value INTEGER; 
   SET value = 0; 
   SELECT current_value INTO value 
     FROM sequence
     WHERE name = seq_name; 
   RETURN value; 
END
$ 
DELIMITER ;

3. Create – function to get the next value

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
   RETURNS INTEGER 
   LANGUAGE SQL 
   DETERMINISTIC 
   CONTAINS SQL 
   SQL SECURITY DEFINER 
   COMMENT '' 
BEGIN 
   UPDATE sequence 
     SET current_value = current_value + increment 
     WHERE name = seq_name; 
   RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

4. Create – Function to update current value

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
   RETURNS INTEGER 
   LANGUAGE SQL 
   DETERMINISTIC 
   CONTAINS SQL 
   SQL SECURITY DEFINER 
   COMMENT '' 
BEGIN 
   UPDATE sequence 
     SET current_value = value 
     WHERE name = seq_name; 
   RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

5. Test adding instance to execute SQL

INSERT INTO sequence VALUES ('testSeq', 0, 1);-- Add a sequence name and initial value, as well as the auto-increment rateSELECT SETVAL('testSeq', 10);-- Set the initial value of the specified sequenceSELECT CURRVAL('testSeq');-- Query the current value of the specified sequenceSELECT NEXTVAL('testSeq');-- Query the next value of the specified sequence

This is the end of this article about the sample code for implementing auto-increment sequence in MySQL. For more information about auto-increment sequence in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

How to modify the starting value of mysql auto-increment ID
How to set mysql auto-increment id back to 0
How to reset the initial value of AUTO_INCREMENT column in mysql
3 Java methods to get the auto-increment ID value of the last inserted MySQL record
Related settings and issues of the auto_increment function in MySQL
Introduction to using MySQL rownumber SQL to generate self-incrementing sequence numbers
You may not know these things about Mysql auto-increment id
Solution to the problem of self-increment ID in MySQL table
Troubleshooting and solutions for MySQL auto-increment ID oversize problem
Example of changing the auto-increment primary key type from int to char in mysql
Using Java's MyBatis framework to get the auto-increment primary key when inserting records in MySQL

<<: Understanding the CSS transform-origin property

>>: A detailed introduction to the Linux directory structure