Shulou(Shulou.com)06/01 Report--

Since mysql is not the same as oracle and does not support direct sequence, you need to create a table to simulate the function of sequence. The reason for sql statement is as follows:

Step 1: create-- Sequence management 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 with 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 that takes 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 that updates 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 function function

When the above four steps are complete, you can use the following data to set the name of the sequence you want to create, 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, as well as self-increment 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');-query the next value of the specified sequence

In the java code, you can directly create a sql statement to query the next value, which solves the problem of unique serial numbers.

Post part of the code (passed the test)

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 also a way to simulate self-increasing sequence with java code. The specific idea is to create a table that stores sequence, and then call the SQL statement through java to query and modify the value of the specified sequence name in this table. In this way, please add synchronized. The specific code will not be uploaded here, because it has been implemented and has not been tested.

In oracle, sequence provides multiple tables and multiple fields to share a non-repetitive value. There are self-increasing columns in Mysql, which can basically meet the requirements of competition. However, there are restrictions on self-increment columns:

a. Can only be used for one field in a table, and one cannot have more than two self-incrementing columns at the same time.

b. Self-incrementing column must be defined as key (competitive or FK)

c. Self-increasing columns cannot be shared by multiple tables

d. When the insert statement does not include a self-increment field or sets its value to NULL, the value is automatically filled in.

Without requiring the fields to be incremented in order, you can implement the sequence in Mysql. Let's look at the following example:

DROP TABLE IF EXISTS sequence;-create a sequence table that specifies seq as an unsigned large integer that supports unsigned values: 0 (default) 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)-repetitive seq is not allowed. ENGINE=InnoDB; DELIMITER / DROP FUNCTION IF EXISTS currval / CREATE FUNCTION currval (seq_name VARCHAR (50)) RETURNS BIGINT BEGIN 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 BIGINT BEGIN UPDATE sequence SET current_value = value WHERE upper (name) = upper (seq_name); RETURN currval (seq_name); END / DELIMITER

Use sequences in SQL:

Create a sequence and insert values into the sequence table:

Mysql > insert into sequence set name='myseq'

View the currently built sequence:

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, used for the first time, so the value is 1:

Mysql > select nextval ('myseq')

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.