Create a Sequence for auto-increment in MySQL,

Create a Sequence for auto-increment in MySQL,

In project applications, the following scenario was used:
An int-type serial number is required in the interface. Due to the multi-threaded mode, if a timestamp is used, there may be duplicates (of course, the probability is very small ).
So I thought of using an independent auto-incrementing sequence to solve this problem.
The current database is mysql.
Because mysql and oracle are not the same and do not support direct sequence, you need to create a table to simulate the sequence function. The reason is that the SQL statement is as follows:
Step 1: Create a -- Sequence 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; 

 
Step 2: Create a function -- get 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 3: Create a function -- Take 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 ; 

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

 
Step 5: test the Function
After completing the preceding four steps, you can use the following data to set the name of the sequence to be created, set the initial value, and obtain the current value and the next value.
 

• Insert into sequence VALUES ('testseq ', 0, 1); ---- Add a sequence name and initial value, and auto increment Amplitude
• Select setval ('testseq ', 10); --- set the initial value of the specified sequence
• Select currval ('testseq '); -- query the current value of the specified sequence
• Select nextval ('testseq '); -- queries the next value of a specified sequence.

 

 
In java code, you can directly create an SQL statement to query the next value, which solves the unique problem of the sequential number.
Paste some code (tested)

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 applications, there is also a way to simulate auto-increment sequence using java code. The specific idea is to create a table for storing sequence, then, you can call the SQL statement in java to query and modify the value of the specified sequence name in the table. In this way, add synchronized. The specific code will not be uploaded here, because it is implemented and has not been tested.

In oracle, sequence provides multiple fields in multiple tables to share a unique value. Mysql has an auto-increment column, which can basically meet the PK requirements. However, the auto-increment column has restrictions:

A. It can only be used for one field in the Table. One column cannot have more than two auto-increment columns at the same time;

B. the auto-increment column must be defined as a key (PK or FK );

C. The auto-increment Column cannot be shared by multiple tables;

D. When the insert statement does not include an auto-increment field or sets its value to NULL, this value is automatically filled in.

If the field sequence is not required to increase sequentially, You can implement the sequence in Mysql. Let's look at the following example:

Drop table if exists sequence; -- creates a sequence TABLE and specifies the seq column as an unsigned big integer. The value of the unsigned value is 0 (default) up to 18446744073709551615 (0 to 2 ^ 64-1 ). Create table sequence (name VARCHAR (50) not null, current_value bigint unsigned not null default 0, increment int not null default 1, primary key (name) -- duplicate seq is not allowed .) ENGINE = InnoDB; DELIMITER/drop function if exists currval/create function currval (seq_name VARCHAR (50) returns bigintbegin declare value BIGINT; SELECT current_value INTO value FROM sequence WHERE upper (name) = upper (seq_name); -- case insensitive. RETURN value; END;/DELIMITER; DELIMITER/drop function if exists nextval/create function nextval (seq_name VARCHAR (50) 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 (50), value BIGINT) returns bigintbegin update sequence SET current_value = value WHERE upper (name) = upper (seq_name ); RETURN currval (seq_name); END;/DELIMITER;

Use sequence in SQL:
Create a sequence and insert a value to the sequence table:

mysql> insert into sequence set name='myseq';

View the current sequence:

mysql> select * from sequence;

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

Obtain the next value of the sequence, which is used for the first time. Therefore, the value is 1:

mysql> select nextval('myseq');

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

Articles you may be interested in:

• Simple tutorial on Using Sequence in MySQL
• How to modify the start value of mysql auto-increment ID
• How does mysql set auto-increment IDs to 0?