How to realize sequence function in mysql

This article will explain in detail how to achieve sequence function in mysql, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Implementing sequence function with mysql

1. Establish sequence record sheet

CREATE TABLE `seq_ varchar (50) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `max_ value` int (11) NOT NULL, `max_ value` int (11) NOT NULL, `current_ value` int (11) NOT NULL, `increment_ value`int (11) NOT NULL DEFAULT'1, PRIMARY KEY (`seq_ name`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

two。 Establish sequence basic function

DELIMITER $$CREATE DEFINER= `root` @ `% `FUNCTION` _ nextval` (name varchar (50)) RETURNS int (11) begin declare _ cur int;declare _ maxvalue int;-receive maximum declare _ increment int;-receive growth steps set _ increment = (select increment_value from sys_sequence where seq_name = name); set _ maxvalue = (select max_value from sys_sequence where seq_name = name); set _ cur = (select current_value from sys_sequence where seq_name = name) Update sys_sequence-- update the current value set current_value = _ cur + increment_value where seq_name = name; if (_ cur + _ increment > = _ maxvalue) then-determine whether it reaches the maximum value update sys_sequence set current_value = min_value where seq_name = name; end if;return _ cur; end$$DELIMITER

3. Insert the sequence you want to create

INSERT INTO `mydb`.`sys _ sequence` (`current_ name`, `min_ value`, `max_ value`, `current_ value`) VALUES ('seq_name1', 1, 99999999, 1, 1)

4. Use sequence

Select _ nextval ('seq_name1'); this is all about how to implement the sequence function in mysql. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.