Shulou(Shulou.com)05/31 Report--

MySQL database how to achieve the sequence of self-increasing function, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

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 'The current value of the sequence', `increment` INT ( 11 ) NOT NULL DEFAULT '1' COMMENT 'self increment of sequence', PRIMARY KEY ( `name` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8 COLATE = utf8_bin;

2. Create a function that takes the 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 a function that takes 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 a function that updates the 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. Testing

INSERT INTO sequence VALUES ('hwb_Seq', 0, 1);--Add a sequence name and initial value, and increment SELECT SETVAL ('hwb_Seq', 10);--Set the initial value of the specified sequence SELECT CURRVAL ('hwb_Seq');--Query the current value of the specified sequence SELECT NEXTVAL ('hwb_Seq');--Query the next value of the specified sequence After reading the above, do you know how to implement sequence increment in MySQL database? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.