mysql 自增序列(转)
1:原理是在建立一个触发器TRIGGER tri_NewBH 在table插入时执行序列计算
mysql> CREATE TABLE tb(BH CHAR(16),content VARCHAR(20),`date` DATETIME,val INT); Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> DELIMITER $$ mysql> DROP TRIGGER IF EXISTS tri_NewBH $$ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb -> FOR EACH ROW -> BEGIN -> DECLARE dt CHAR(8); -> DECLARE bh_id CHAR(16); -> DECLARE number INT; -> DECLARE new_bh VARCHAR(16); -> -> SET dt = DATE_FORMAT(CURDATE(),'%Y%m%d'); -> -> SELECT -> MAX(BH) INTO bh_id -> FROM tb -> WHERE BH LIKE CONCAT(dt,'%'); -> -> IF bh_id = '' OR bh_id IS NULL THEN -> SET new_bh = CONCAT(dt,'00000001'); -> ELSE -> SET number = RIGHT(bh_id,8) + 1; -> SET new_bh = RIGHT(CONCAT('00000000',number),8); -> SET new_bh=CONCAT(dt,new_bh); -> END IF; -> -> SET NEW.BH = new_bh; -> END$$ Query OK, 0 rows affected (0.09 sec) mysql> mysql> DELIMITER ; mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb; +------------------+---------+---------------------+------+ | BH | content | date | val | +------------------+---------+---------------------+------+ | 2009051100000001 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2009051100000002 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2009051100000003 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2009051100000004 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2011051200000001 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2011051200000002 | LiangCK | 2009-05-11 00:00:00 | 20 | | 2011051200000003 | LiangCK | 2009-05-11 00:00:00 | 20 | +------------------+---------+---------------------+------+ 7 rows in set (0.00 sec)
2.下面就是另外一个的实现方案:
原理是创建一个专门记录序列的表sequence,记录有当前序列号,序列的间隔如+1
DROP TABLE IF EXISTS sequence;/*创建记录当前序列的表*/ CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB; INSERT INTO sequence VALUES ('MovieSeq',3,5); DROP FUNCTION IF EXISTS currval; DELIMITER $/*创建一个获取当前序列的function*/ 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 ;
测试一下结果：
mysql> SELECT currval('MovieSeq'); +---------------------+ | currval('MovieSeq') | +---------------------+ | 3 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT currval('x'); +--------------+ | currval('x') | +--------------+ | 0 | +--------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------+ | Level | Code | Message | +---------+------+------------------+ | Warning | 1329 | No data to FETCH | +---------+------+------------------+ 1 row in set (0.00 sec)
nextval
//获取下一个数值..先在sequence里面调用update当前最大数值+1然后再调用currval获得当前数值
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 ;
+---------------------+ | nextval('MovieSeq') | +---------------------+ | 15 | +---------------------+ 1 row in set (0.09 sec) mysql> select nextval('MovieSeq'); +---------------------+ | nextval('MovieSeq') | +---------------------+ | 20 | +---------------------+ 1 row in set (0.01 sec) mysql> select nextval('MovieSeq'); +---------------------+ | nextval('MovieSeq') | +---------------------+ | 25 | +---------------------+ 1 row in set (0.00 sec)
setval
DROP FUNCTION IF EXISTS setval; DELIMITER $ CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER CONTAINS SQL BEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END$ DELIMITER ;
+------------------------+ | setval('MovieSeq',150) | +------------------------+ | 150 | +------------------------+ 1 row in set (0.06 sec) mysql> select curval('MovieSeq'); +---------------------+ | currval('MovieSeq') | +---------------------+ | 150 | +---------------------+ 1 row in set (0.00 sec) mysql> select nextval('MovieSeq'); +---------------------+ | nextval('MovieSeq') | +---------------------+ | 155 | +---------------------+ 1 row in set (0.00 sec)
