mysql番号生成
1、シリアル番号の生成方法
2、データ挿入時のトリガー
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