The method of realizing self-increasing sequence by mysql
This article mainly shows you the "mysql to achieve self-increasing sequence method", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql to achieve self-increasing sequence method" this article.
1. Create sequence table CREATE TABLE `sequence` (`name` varchar (50) COLLATE utf8_bin NOT NULL COMMENT 'sequence name', `current_ value` int (11) NOT NULL COMMENT 'sequence current value', `increment `int (11) NOT NULL DEFAULT'1' COMMENT 'sequence self-increment', PRIMARY KEY (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;2. Create-the function 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-the function 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-update the function 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 add instance execution sqlINSERT INTO sequence VALUES ('testSeq', 0,1);-- add a sequence name and initial value, as well as self-increment SELECT SETVAL (' testSeq', 10);-- set the initial value of the specified sequence ('testSeq');-- query the current value of the specified sequence SELECT NEXTVAL (' testSeq') Query the next value above the specified sequence is all the contents of the article "mysql method for implementing self-increasing sequences". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
