34,576
社区成员
发帖
与我相关
我的任务
分享
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)
CREATE TABLE animals
(
id mediumint not null auto_increment,
name char(30) not null,
primary key (id)
);
--MSSQL的:
--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN
--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/
IDENTITY(INT,1,1) --第1个1是基数,第二个数是增量
IDENTITY(INT,1,1)
??