About generating serial numbers in MySQL

Original:

http://xm-koma.iteye.com/blog/1961793

Http://www.3lian.com/edu/2013/08-30/93449.html

MySQL emulates the sequence of Oracle

First step: Create a--sequence management table

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

Step Two: Create-a function that takes the current value

DROP FUNCTION IF EXISTSCurrval;delimiter $CREATE FUNCTIONCurrval (seq_nameVARCHAR( -))RETURNS INTEGERLANGUAGE sqldeterministicCONTAINSsqlsql SECURITY definercomment"'BEGINDECLAREValueINTEGER;SETValue= 0;SELECTCurrent_value intovalue fromsequenceWHEREName=Seq_name;RETURNvalue;END$DELIMITER;

Step three: Create--a function to remove a value

DROP FUNCTION IF EXISTSNextval;delimiter $CREATE FUNCTIONNextval (seq_nameVARCHAR( -))RETURNS INTEGERLANGUAGE sqldeterministicCONTAINSsqlsql SECURITY definercomment"'BEGINUPDATEsequenceSETCurrent_value=Current_value+IncrementWHEREName=Seq_name;RETURNCurrval (seq_name);END$DELIMITER;

Fourth step: Create-a function to update the current value

DROP FUNCTION IF EXISTSSetval;delimiter $CREATE FUNCTIONSetval (seq_nameVARCHAR( -), ValueINTEGER)RETURNS INTEGERLANGUAGE sqldeterministicCONTAINSsqlsql SECURITY definercomment"'BEGINUPDATEsequenceSETCurrent_value=valueWHEREName=Seq_name;RETURNCurrval (seq_name);END$DELIMITER;

Fifth Step: Test function function

When the above four steps are complete, you can set the sequence name you want to create with the following data and set the initial value and get the current and next values.

INSERT  intoSequenceVALUES('Testseq',0,1);----Add a sequence name and initial value, and self-incrementSELECTSetval ('Testseq',Ten);---Sets the initial value of the specified sequenceSELECTCurrval ('Testseq');--queries the current value of the specified sequenceSELECTNextval ('Testseq');--queries the next value of the specified sequence

MySQL timed execution script (scheduled Task) instance

See if event is turned on

The code is as follows

 like ' %sche% ';

Opening an event plan

The code is as follows

Set = 1;

Create a stored procedure test

The code is as follows

CREATE PROCEDURE Test () BEGIN Update SET = WHERE =  - ; END;

Create Event E_test

The code is as follows

Create if  not exists e_test  on  - Second  on Completion Preservedo call Test ();

The stored procedure test is executed every 30 seconds, and the current time is updated to the Endtime field of the id=14 record in the Examinfo table.

Close Event Task

The code is as follows

Alter  on completion PRESERVE DISABLE;

Account Opening Event Task

The code is as follows

　Alter  on completion PRESERVE ENABLE;

The above tests were successful and the test environment was MySQL 5.4.2-beta-community MySQL Community server (GPL)

The above related content is the MySQL timing implementation of the introduction, hope you can have some gains.

MySQL scheduled task disappears after restart

We just need to modify a configuration to

The Event_scheduler is set to off in MySQL config. Go to MySQL and change the configuration to on then it's done.

About generating serial numbers in MySQL