mysql自增序列nextval并发_mysql 自增序列(转)

1:原理是在建立一个触发器TRIGGERtri_NewBH 在table插入时执行序列计算

mysql>CREATETABLEtb(BHCHAR(16),contentVARCHAR(20),`date`DATETIME,valINT);

Query OK,0rows affected (0.05sec)

mysql>mysql>mysql>DELIMITER $$

mysql>DROPTRIGGERIFEXISTStri_NewBH $$

Query OK,0rows affected,1warning (0.00sec)

mysql>mysql>CREATETRIGGERtri_NewBH BEFOREINSERTONtb->FOREACH ROW->BEGIN->DECLAREdtCHAR(8);->DECLAREbh_idCHAR(16);->DECLAREnumberINT;->DECLAREnew_bhVARCHAR(16);->->SETdt=DATE_FORMAT(CURDATE(),'%Y%m%d');->->SELECT->MAX(BH)INTObh_id->FROMtb->WHEREBHLIKECONCAT(dt,'%');->->IFbh_id=''ORbh_idISNULLTHEN->SETnew_bh=CONCAT(dt,'00000001');->ELSE->SETnumber=RIGHT(bh_id,8)+1;->SETnew_bh=RIGHT(CONCAT('00000000',number),8);->SETnew_bh=CONCAT(dt,new_bh);->ENDIF;->->SETNEW.BH=new_bh;->END$$

Query OK,0rows affected (0.09sec)

mysql>mysql>DELIMITER ;

mysql>INSERTINTOtb(content,`date`,val)VALUES('LiangCK','2009-05-11',20);

Query OK,1row affected (0.00sec)

mysql>INSERTINTOtb(content,`date`,val)VALUES('LiangCK','2009-05-11',20);

Query OK,1row affected (0.01sec)

mysql>INSERTINTOtb(content,`date`,val)VALUES('LiangCK',&#