This article describes how to create a Sequence for auto-increment in MySQL. it lists two instances and briefly discusses some restrictions, if you need a friend, you can refer to the project application in the following scenario:
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)
The above is the tutorial _ MySQL content for creating auto-incrementing sequences in MySQL. For more information, see PHP Chinese network (www.php1.cn )!