"Go" MySQL Add sequence management function (simulate create sequence)

1. Oracel can support sequence directly, but MySQL does not support sequence, so we will create sequence in MySQL by simulating the sequence method.
Methods for simulating sequence:
Project scenario:
Project application, there was one of the following scenarios:

The interface requires sending a serial number of type int, because of the multithreading pattern, there may be duplicates (of course, a small probability) if timestamps are used.

So I thought of using an independent self-sequence to solve the problem.

The current database is: MySQL

Because MySQL is not the same as Oracle, it does not support direct sequence, so you need to create a table to emulate the functionality of sequence, and the SQL statements are as follows:
First step: Create a--sequence management table
DROP TABLE IF EXISTS sequence;
CREATE TABLE Sequence (
Name VARCHAR (not NULL),
Current_value INT not NULL,
Increment INT not NULL DEFAULT 1,
PRIMARY KEY (name)
) Engine=innodb;

Step Two: 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;

Step three: Create--a function to remove a 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;

Fourth step: Create-a function to update the current value
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION Setval (Seq_name VARCHAR (), 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;

Fifth Step: Test function function
When the above four steps are complete, you can set the sequence name you want to create with the following data and set the initial value and get the current and next values.



INSERT into sequence values (' Testseq ', 0, 1),----add a sequence name and initial value, and self-increment
SELECT setval (' Testseq ', ten);---Set the initial value of the specified sequence
SELECT currval (' testseq ');--Query the current value of the specified sequence
SELECT nextval (' testseq ');--Query the next value of the specified sequence





In Java code, SQL statements can be created directly to query the next value, which solves the problem of serial number only.

Post part of the code (tested pass)
public void Testgetsequence () {
Connection conn = jdbcutils.getconnection (URL, userName, password);
String sql = "Select Currval (' Testseq ');";
PreparedStatement ptmt = null;
ResultSet rs = null;
try {
ptmt = conn.preparestatement (sql);
rs = Ptmt.executequery ();
int count = 0;
while (Rs.next ()) {
Count = Rs.getint (1);
}
System.out.println (count);
} catch (SQLException e) {
E.printstacktrace ();
} finally {
Jdbcutils.close (RS, PTMT, conn);
}
}

PS: In the application, there is a way to use Java code to achieve the simulation of self-sequence, the specific idea is to create a table to store sequence, and then call SQL statements through Java to query and modify the value of the specified sequence name in this table, This way please add synchronized. The specific code is not uploaded here, because it has been implemented, has not been tested.

"Go" MySQL Add sequence management function (simulate create sequence)