mysql 自增序列

最新推荐文章于 2024-05-20 18:36:43 发布

yangyinbo

最新推荐文章于 2024-05-20 18:36:43 发布

阅读量1k

点赞数

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 ;  

 mysql> select nextval('MovieSeq'); 

 

+---------------------+  
| 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 ;  

 mysql> select setval('MovieSeq',150); 

 

+------------------------+  
| 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)