php mysql 序号_mysql编号生成_MySQL

1、序列号生成的方法

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_workNo`() RETURNS varchar(45) CHARSET utf8

BEGIN

DECLARE newWorkNo varCHAR (45) ;

DECLARE currentDate varCHAR (15) ;-- 当前日期,有可能包含时分秒

DECLARE maxNo INT DEFAULT 0 ;

DECLARE oldWorkNo VARCHAR (25) DEFAULT '' ;-- 离现在最近的满足条件的订单编号

SELECT SUBSTRING(DATE_FORMAT(NOW(), '%Y%m'),3,6) INTO currentDate ;

SELECT IFNULL(workNo, '') INTO oldWorkNo

FROM operation_work

WHERE SUBSTRING(workNo, 3, 4) = currentDate

ORDER BY id DESC LIMIT 1 ; -- 有多条时只显示离现在最近的一条

IF oldWorkNo != '' THEN

SET maxNo = CONVERT(SUBSTRING(oldWorkNo, -4), DECIMAL) ;-- SUBSTRING(oldOrderNo, -5)：订单编号如果不为‘‘截取订单的最后5位

END IF ;

SELECT

CONCAT('TS', currentDate, LPAD((maxNo + 1), 4, '0')) INTO newWorkNo ;

RETURN newWorkNo;

END$$

DELIMITER ;

2、插入数据时的触发器

USE `it_system`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER `operation_work_BINS` BEFORE INSERT ON `operation_work` FOR EACH ROW

begin SET @workNo = get_workNo();

set NEW.workNo=@workNo;

end

本文原创发布php中文网，转载请注明出处，感谢您的尊重！