用存储过程+触发器实现序列

In MySQL 5.0.10 we now have the functionality to create an automatic sequence generator using a trigger that calls a stored function. You could even have a global sequence if you wish, that is, a sequence that is used by multiple tables.

/* For this example, we'll put the sequences table in the test database. */

<!-- lang: sql -->
    USE test;
/* Create a sequence table */
CREATE TABLE IF NOT EXISTS sequences(
    name CHAR(20) PRIMARY KEY,
    val INT UNSIGNED
);

DROP FUNCTION IF EXISTS nextval;

DELIMITER //

/* The actual sequence function. Call nextval('seqname'), and it returns the next value. */
/* If the named sequence does not yet exist, it is created with initial value 1. */

CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
RETURN LAST_INSERT_ID();
END
//

DELIMITER ;
/* Let's now use a sequence in a test table... */
CREATE TABLE IF NOT EXISTS data (
    id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
    info VARCHAR(50));

DROP TRIGGER nextval;
/* The trigger only generates a new id if 0 is inserted. */
/* The default value of id is also 0 (see the create table statement) so that makes it implicit. */
CREATE TRIGGER nextval BEFORE INSERT ON data
FOR EACH ROW SET new.id=IF(new.id=0,nextval('data'),new.id);

TRUNCATE TABLE data;
INSERT INTO data (info) VALUES ('bla');
INSERT INTO data (info) VALUES ('foo'),('bar');
SELECT * FROM data;

+----+------+ | id | info | +----+------+ | 1 | bla | | 2 | foo | | 3 | bar | +----+------+ Pretty neat, eh? The sequences table and the nextval() function can, as you can see, handle multiple sequences. Your trigger just identifies the one it wants. In our example, we simply have a sequence name that is the same as our table name.

转载于:https://my.oschina.net/bluven/blog/217885