Mysql ID generation

Mysql ID generation

1. Serial Number Generation Method

 

DELIMITER $ create definer = 'root' @ 'localhost' FUNCTION 'get _ workno' () RETURNS varchar (45) CHARSET utf8BEGINDECLARE newWorkNo varCHAR (45 ); DECLARE currentDate varCHAR (15); -- current date, which may contain hour, minute, and second DECLARE maxNo int default 0; DECLARE oldWorkNo VARCHAR (25) DEFAULT ''; -- the order number nearest to the current one that meets the conditions: select substring (DATE_FORMAT (NOW (), '% Y % m'),) INTO currentDate; select ifnull (workNo ,'') INTO oldWorkNo FROM operati On_work where substring (workNo, 3, 4) = currentDate order by id desc limit 1; -- IF there are multiple entries, only the latest one IF oldWorkNo is displayed! = ''Then SET maxNo = CONVERT (SUBSTRING (oldWorkNo,-4), DECIMAL); -- SUBSTRING (oldOrderNo,-5 ): IF the order number is not '', the last five end if of the Order is truncated; select concat ('ts', currentDate, LPAD (maxNo + 1), 4, '0 ')) INTO newWorkNo; RETURN newWorkNo; END $ DELIMITER;

2. Trigger for data insertion

 

 

USE `it_system`;DELIMITER $$CREATE DEFINER=`root`@`localhost` TRIGGER `operation_work_BINS` BEFORE INSERT ON `operation_work` FOR EACH ROWbegin SET @workNo = get_workNo();           set NEW.workNo=@workNo;end