MySQL implementation sequence (Sequence) effect and how this strategy is used in MyBatis

Objective:

In Oracle, a sequence (Sequence) is generally used to process primary key fields, and there is no sequence in MySQL, but MySQL provides self-growth (increment) for similar purposes, but only self-increment, and cannot set step size, start index, loop, etc. The most important thing is that a table can only set one field to use self-increment, but sometimes we need two or two more fields to implement self-increment (single-table multi-field self-increment), MySQL itself is not implemented, but we can use to create a sequence table, use the function to get the value of the sequence

implementation of a sequence in MySQL

(1) Create a new test table:

DROP TABLE IF EXISTS ' article '; CREATE TABLE ' article ' (' id ' int (one) not null, ' name ' varchar () default NULL, ' author ' varchar () default NULL, P Rimary KEY (' id ')) engine=innodb DEFAULT Charset=utf8;--------------------------------Records of article------------- -----------------INSERT into ' article ' VALUES (' 1 ', ' Program ape self-cultivation ', ' Zifangsky ');

Note that this does not set the primary key auto-increment

(2) Add the sequence table and add the test data:

drop table if exists  ' sequence '; create table  ' sequence '   (   ' Seq_name '  varchar ()  NOT NULL,    ' Current_val '  int (one)  NOT NULL,   ' Increment_val '  int (one)  NOT  null default  ' 1 ',  primary key  (' Seq_name '))  engine=innodb default  CHARSET=utf8;-- ------------------------------ records of board_event_success--  ----------------------------insert into  ' sequence '  VALUES  (' seq_article ',  ' 0 ',   ' 1 '); 

• The "Seq_name" field represents the sequence of a table, which is best associated with the name of the table, and one data per table

• The "Current_val" field represents the current maximum ID (primary key ID) of the table corresponding to the "Seq_name" field

• The "Increment_val" field represents the step in which the primary key ID of the table corresponding to the "Seq_name" field grows (that is, each increment + 1, +2, etc.)

(3) Create a function to get the current maximum ID of a table:

drop function if exists  ' currval ';D elimiter ;; create function  ' Currval ' (V_seq_name varchar)  returns int (one) begin          declare value integer;            set value = 0;            select current_val into value from sequence where seq_ name = v_seq_name;    return value; end;;D elimiter ; 

As can be seen from the code, the logic of this function is simple, according to the "seq_name" query "sequence" the table "Current_val" The value of this field

Test:

Mysql> Select Currval (' seq_article ');

Obviously, the output is now 0.

(4) Create a function to get the next ID of a table:

DROP FUNCTION IF EXISTS ' nextval ';D elimiter;; CREATE FUNCTION ' Nextval ' (v_seq_name VARCHAR) RETURNS int (one) begin update sequence Set current_val = Current_val +    Increment_val where seq_name = V_seq_name; Return Currval (v_seq_name); end;;D Elimiter;

The logic of this function is also very simple, first update the sequence value of a table in the "sequence" table, and then query the sequence value again as the primary key ID when inserting data

Test:

Mysql> Select Nextval (' seq_article ');

Obviously, the result of executing this function is 1, and the corresponding record in the table "sequence" is increased by 1.

two sequence usage in MyBatis

When you use a plug-in to generate a Mapper.xml file for a table, you need to modify its build strategy, specifically to modify the Generatorconfig.xml file and to modify the base data generation strategy for a table:

<table tablename= "article" domainobjectname= "article" enablecountbyexample= "false" enabledeletebyexample= "false "Enableselectbyexample=" false "enableupdatebyexample=" false "><property name=" Ignorequalifiersatruntime " Value= "false"/><generatedkey column= "id" sqlstatement= "select Nextval (' seq_article ');" /></table>

Then run the plug-in, and finally we can see the resulting insert method:

<insert id= "Insert" parametertype= "cn.zifangsky.model.Article" > <selectkey resulttype= "Java.lang.Integer"    keyproperty= "id" order= "before" > select Nextval (' seq_article '); </selectKey> INSERT into article (ID, name, author) values (#{id,jdbctype=integer}, #{name,jdbctype=varc HAR}, #{author,jdbctype=varchar}) </insert>

In other words, when inserting data using MyBatis, it first detects the primary key ID of the data to be inserted through the Nextval function, and then inserts the data.

Note: The use of plug-ins for mybatis automatically generate basic files can be consulted in this article: https://www.zifangsky.cn/431.html

Reference article:

• http://blog.csdn.net/sinat_19351993/article/details/47169789

This article is from "Zifangsky's personal blog" blog, make sure to keep this source http://983836259.blog.51cto.com/7311475/1889590

MySQL implementation sequence (Sequence) effect and how this strategy is used in MyBatis