Create a tutorial in Mysql that implements the Sequence sequence (_mysql)

In the project application, there was a scenario:
interface requires sending an int type of serial number, due to multithreading mode, if the timestamp, there may be duplication (of course, the probability is very small).
So think of the use of an independent sequence to solve the problem.
The current database is: MySQL
Because MySQL and Oracle are not the same, do not support direct sequence, so you need to create a table to simulate the function of sequence, the following SQL statement:
Step one: Create a--sequence management table

DROP TABLE IF EXISTS sequence; 
CREATE TABLE sequence ( 
     name VARCHAR) NOT NULL, 
     current_value int is not NULL, 
     increment int is 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) 
     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) 
     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; 

Step Fourth: Create--the function to update the current value

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (Seq_name VARCHAR (m), 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 increase
• SELECT setval (' Testseq ', ten);---Set the initial value of the specified sequence
• SELECT currval (' testseq ');--query specifies the current value of sequence
• SELECT nextval (' testseq ');--query specifies the next value of sequence

In Java code, you can directly create SQL statements to query the next value, which solves the problem of the serial number only.
Post some code (tested through)

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 simulate the sequence, the idea is to create a table containing sequence, and then invoke 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 here will not upload, because the implementation, has not been tested.

In Oracle, sequence provides multiple tables and multiple fields to share a single, distinct value. Mysql exists in the self-added column, the basic can meet the requirements of PK. However, there are restrictions on the self-add column:

A. Only one field in the table, one can not exist at the same time more than two self-added columns;

B. Self-added columns must be defined as key (PK or FK);

C. Self-adding columns cannot be shared by multiple tables;

D. When an INSERT statement does not include a self-added field or sets its value to NULL, the value is automatically filled in.

You can implement a sequence in Mysql without requiring a field order increment, and then look at one of the following examples:

DROP TABLE IF EXISTS sequence;
--Build Sequence table, specify SEQ column as unsigned large integer, can support unsigned values: 0 (default) to 18446744073709551615 (0 to 2^64–1). 
     CREATE TABLE sequence (name VARCHAR) NOT NULL, current_value BIGINT UNSIGNED not null DEFAULT 0,
Increment INT not NULL DEFAULT 1, PRIMARY KEY (name)--does not allow the existence of a duplicate seq. 
 
 
) Engine=innodb;
     Delimiter/drop function IF EXISTS currval/create FUNCTION currval (seq_name VARCHAR ()) RETURNS BIGINT BEGIN
     DECLARE value BIGINT; SELECT current_value into value from sequence WHERE upper (name) = Upper (Seq_name);
     --The case does not differentiate.
return value;
End; 
 
 
/DELIMITER; 
     Delimiter/drop function IF EXISTS nextval/create FUNCTION nextval (seq_name VARCHAR ()) RETURNS BIGINT BEGIN
     DECLARE value BIGINT;
     UPDATE sequence SET current_value = current_value + increment WHERE upper (name) = Upper (Seq_name); 
Return Currval (Seq_name);
End; 
 
/DELIMITER; Delimiter/drop FUNCTION IF EXISTS SETval/create FUNCTION Setval (Seq_name VARCHAR (m), value BIGINT) RETURNS BIGINT BEGIN UPDATE sequence SE 
     T current_value = value WHERE upper (name) = Upper (Seq_name); 
Return Currval (Seq_name);
End;
 /DELIMITER;

To use a sequence in SQL:
To create a sequence, insert a value into the sequence table:

mysql> INSERT INTO sequence set name= ' Myseq ';

To view the currently built series:

Mysql> select * from sequence;

+-------+---------------+-----------+
| name | current_value | increment
| +-------+---------------+-----------+
| myseq |       0 |     1 |
+-------+---------------+-----------+
1 row in Set (0.00 sec)

Gets the next value of the sequence, which is used for the first time, so the value is 1:

Mysql> Select Nextval (' Myseq ');

+------------------+
| nextval (' myseq ') |
+------------------+
|        1 |
+------------------+
1 row in Set (0.00 sec)