Introduction to creating a sequence and creating a custom function method under MySQL

DB2 database-based applications and data are migrated to MySQL during the work process, and in the original application, a large number of sequence are used, considering minimizing code changes and deciding to retain sequence usage in the migrated application. This requires finding alternative sequence solutions in MySQL.

The method for creating a sequence in DB2 is as follows:

 drop   SEQUENCE Trz_member. Seq_trz_member_no;  create   SEQUENCE Trz_member. Seq_trz_member_no  as  bigint  Span style= "color: #000000;" >increment  by  1  start  with  10000000000  maxvalue  99999999999  no Cyclecache  20  order ; 

MySQL self-growth differs from Oracle (DB2) sequence:

Self-growth can only be used in one of the fields in the table;

The self-growth can only be assigned to a fixed field of fixed tables and cannot be shared by multiple tables;

Self-growth automatically fills in a field with an unspecified or null value.

There are a few things you need to do to replace the sequence feature in MySQL:

1: Establish sequence table, store multiple sequence information;

2: Complete the definition of the custom function, in the program through the function to complete the generated sequence acquisition;

Next, we will introduce two kinds of generation methods, one is non-concurrency, one is concurrency, the former is not able to handle the problems in concurrent access, the latter can be processed, the first step is the same in both ways, that is to create a sequence table:

Non-concurrent mode:

One: Create the sequence table:

DROP TABLE    IF EXISTSsequence;CREATE TABLESequence (nameVARCHAR( -) not NULL, Current_valueBIGINT  not NULL, IncrementINT  not NULL DEFAULT 1,        PRIMARY KEY(name)) ENGINE=InnoDB;

The table is used to store multiple sequence information, each sequence a sequence, and the effect is equivalent to sequence in the DB2 described earlier. If you need to replace Seq_trz_member_no in DB2 (as previously defined), insert the INSERT statement that inserts the table sequence in MySQL as follows:

INSERT  into VALUES ('seq_trz_member_no',10000000000,1);

Two: Create a MySQL custom function to get the current sequence value:

1: First of all, it is clear that the custom function is a supplement to the library provided by MySQL, to complete the custom function, the new MySQL function must be created via MySQL commond line type command lines, and cannot be created through the graphical database operation software provided by the third party;

2: First provide an example, well have a general understanding:

DELIMITER $$DROP FUNCTION IF EXISTSCurrval; CREATE FUNCTIONCurrval (seq_nameVARCHAR( -))  RETURNS BIGINT  BEGIN    DECLAREC_valueBIGINT DEFAULT  0; SETC_value= 0; SELECTCurrent_value intoC_value fromsequenceWHEREName=Seq_name; RETURNC_value; END$ $DELIMITER;

The function's function is to return the current value of the specified sequence.

The first line of code (DELIMITER $$ ) defines a closing identifier, because MySQL defaults to a semicolon as the Terminator of the SQL statement, and a semicolon is used inside the function body, so it conflicts with the default SQL Terminator. Therefore, you need to define an additional symbol as the Terminator of the SQL. If this definition is not added, the following error will be reported:

Error code: 1064 You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' end ' on line 1

The second line (DROP FUNCTION IF EXISTS currval; is to delete a class with the same name, or if a function with the same name already exists, the following error is reported:

Error code: 1304 FUNCTION Currval already exists

Third, the fourth line defines the function name and function return value;

The function body must be defined in the middle of begin and end.

The following results are performed with MySQL Commond line as follows:

Verify the effect with the following statement:

This function only completes the function of getting the current sequence value, and it also needs to define a function to complete the ability to get the next sequence value, as shown in the following code:

DROP FUNCTION IF EXISTSNextval; DELIMITER $$CREATE FUNCTIONNextval (seq_nameVARCHAR( -))  RETURNS BIGINT  CONTAINSSQLBEGIN     UPDATEsequenceSETCurrent_value=Current_value+IncrementWHEREName=Seq_name; RETURNCurrval (seq_name); END$$ DELIMITER;

The function is used to get the next sequence value, and the result after executing in MySQL Commond line is as follows:

Verify that the function is valid by using the following statement:

SELECT Nextval ('seq_trz_member_no');

At this point, the sequence generation method of non-concurrency is finished. To make sequence generation capable of handling concurrency, you need to make fewer changes.

Concurrency Generation Mode:

The concurrency generation mode of sequence and non-concurrent generation need to establish sequence table, as follows:

One: Create the sequence table:

DROP TABLE    IF EXISTSsequence;CREATE TABLESequence (nameVARCHAR( -) not NULL, Current_valueBIGINT  not NULL, IncrementINT  not NULL DEFAULT 1,        PRIMARY KEY(name)) ENGINE=InnoDB;

Insert a defined sequence:

INSERT  into VALUES ('seq_trz_member_no',10000000000,1);

Two: Custom Function implementation:

DROP FUNCTION IF EXISTSseq; DELIMITER $$CREATE FUNCTIONSeq (seq_nameChar( -))returns BIGINTBEGIN UPDATESequenceSETCurrent_value=LAST_INSERT_ID (Current_value+IncrementWHEREName=Seq_name;RETURNlast_insert_id ();END$ $DELIMITER;

The function internally invokes the LAST_INSERT_ID () function provided internally by MySQL to complete concurrency control.

For more information about last_insert_id, please refer to:
Http://it.100xuexi.com/view/otdetail/20120619/73a6cc8f-36b8-4b70-8904-57c18d3ab385.html

Part of the code quoted in this article: http://meetrice.iteye.com/blog/89426

Http://www.blogjava.net/Skynet/archive/2011/03/23/301847.html

Introduction to creating a sequence and creating a custom function method under MySQL