MySQL auto-defined sequence number: Used to do "Order No."

Auto-Define Sequence number: Used to do "Order No."

There will always be such a demand, but you may not have encountered it. Let me give an example: if the order number is generated in the form "date + serial number"

(Whether the demand is stupid or not), like this: 2015052200001234, how to deal with it?

If there is a serial number, it is better to solve. Start with 00000001, go to 99999999, then reset to a sequence, OK.

--Defining the sequence table

DROP TABLE IF EXISTS sequence;

CREATE TABLE Sequence (

Name VARCHAR (not NULL),

Current_value INT not NULL,

Increment INT not NULL DEFAULT 1,

PRIMARY KEY (name)

) Engine=innodb;

--Get the current serial number

DROP FUNCTION IF EXISTS currval;

DELIMITER $

CREATE FUNCTION Currval (seq_name VARCHAR (50))

RETURNS INTEGER

CONTAINS SQL

BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT Current_value into value

From sequence

WHERE name = Seq_name;

RETURN value;

end$

DELIMITER;

--Get the next serial number

DROP FUNCTION IF EXISTS nextval;

DELIMITER $

CREATE FUNCTION Nextval (seq_name VARCHAR (50))

RETURNS INTEGER

CONTAINS SQL

BEGIN

UPDATE sequence SET current_value = current_value + increment

WHERE name = Seq_name;

RETURN Currval (seq_name);

end$

DELIMITER;

--Reset serial number

DROP FUNCTION IF EXISTS setval;

DELIMITER $

CREATE FUNCTION Setval (Seq_name VARCHAR (), value INTEGER)

RETURNS INTEGER

CONTAINS SQL

BEGIN

UPDATE sequence SET Current_value = value

WHERE name = Seq_name;

RETURN Currval (seq_name);

end$

DELIMITER;

--Initialize data

INSERT into Sequence VALUES (' SAMPLE ', 1, 1);

--Test

SELECT currval (' SAMPLE ');

SELECT nextval (' SAMPLE ');

SELECT nextval (' SAMPLE ');

SELECT setval (' SAMPLE ', 150);

SELECT currval (' SAMPLE ');

SELECT nextval (' SAMPLE ');

SELECT nextval (' SAMPLE ');

MySQL auto-defined sequence number: Used to do "Order No."