Home>

In project application,There was one of the following scenarios:

The interface requires an int serial number to be sent.Due to the multi-threaded mode,If timestamp is used,There may be duplicates (of course the probability is very small).

So I thought of using an independent incrementing sequence to solve this problem.

The current database is:mysql

Because mysql and oracle are not the same,Does not support direct sequence, so you need to create a table to simulate the function of sequence, the reason 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 two:create-a function that takes 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;

The third step:create-the function to 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-update function of 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

When the above four steps are completed,You can use the following data to set the sequence name that needs to be created, set the initial value, and get the current and next value.

insert into sequence values ​​("testseq", 0, 1);---- Add a sequence name and initial value,And the rate of increase 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 java code, you can directly create a sql statement to query the next value,This solves the only problem with the serial number.

Post 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 the application,There is also a way to use Java code to implement the sequence increment simulation. The specific idea is to create a table that stores the sequence, and then call the SQL statement to query and modify the value of the specified sequence name in this table.Please add synchronized in this way. The specific code is not uploaded here,Because it is implemented,Not tested.

In Oracle, sequence provides multiple tables and multiple fields to share a unique value. There are auto-increment columns in mysql,Can basically meet the requirements of pk. However, there are restrictions on auto-increment columns:

a. Can only be used for one field in a table,One cannot have more than two auto-increment columns at the same time;

b. Auto-increment columns must be defined as keys (pk or fk);

c. Increment columns cannot be shared by multiple tables;

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

Without requiring the fields to be incremented sequentially,You can implement sequences in mysql,Consider the following example:

drop table if exists sequence;
-Create a sequence table and specify the seq column as an unsigned large integer.
Unsigned values ​​are supported:0 (default) to 18446744073709551515 (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)-no duplicate seq is 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;

Using sequences in sql:

Create a sequence,Just insert the value 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)

Get the next value of the sequence,First use,So the value is 1:

mysql>select nextval ("myseq");
+ ------------------ +
| nextval ("myseq") |
+ ------------------ +
| 1 |
+ ------------------ +
1 row in set (0.00 sec)
  • Previous 16 best PHP libraries you must know as a programmer
  • Next Learn PHP design pattern PHP implementation memo pattern (Memento)