MySQL adds Sequence Management

In the application of the Sequence management function added to MySQL, a serial number of the int type was required to be sent in the following scenario: because of the multithreading 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 due to mysql

In the application of the Sequence management function added to MySQL, a serial number of the int type was required to be sent in the following scenario: because of the multithreading 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 due to mysql

Added the Sequence management function for 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.