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

This article introduces the relevant knowledge of "how to add Sequence management function to MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-- Sequence management table

DROP TABLE IF EXISTS sequence

CREATE TABLE sequence (

Name VARCHAR (50) NOT NULL

Current_value INT NOT NULL

Increment INT NOT NULL DEFAULT 1

PRIMARY KEY (name)

) ENGINE=InnoDB

-- A function with 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

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

-- functions that update 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

/ *

-- testing

INSERT INTO sequence VALUES ('TestSeq', 0,1)

SELECT SETVAL ('TestSeq', 10)

SELECT CURRVAL ('TestSeq')

SELECT NEXTVAL ('TestSeq')

, /

This is the end of the content of "how to add Sequence management function to MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.