MySQL number generation

Tag: MySQL function data number trigger

1, the method of serial number generation

DELIMITER $ $CREATE definer= ' root ' @ ' localhost ' FUNCTION ' get_workno ' () RETURNS varchar CHARSET utf8begindeclare Newworkno varchar;D eclare currentdate varchar (15);--current date, may contain time division seconds     DECLARE maxno INT DEFAULT 0;D eclare oldwor KNo VARCHAR (+) DEFAULT ';--the order number of the nearest satisfying condition is     SELECT SUBSTRING (Date_format (now (), '%y%m '), 3,6) into currentdate; SE Lect ifnull (Workno, ") into Oldworkno from       operation_work       WHERE SUBSTRING (Workno, 3, 4) = currentdate          ORDER by ID DESC LIMIT 1; --There are multiple bars that only show the nearest     if oldworkno! = "then     SET maxno = CONVERT (SUBSTRING (Oldworkno,-4), DECIMAL);--SUBSTRING (OL Dorderno,-5): Order number if not for the last 5 bits of the Intercept order     End if; SELECT     CONCAT (' TS ', currentdate,  lpad ((maxno + 1), 4, ' 0 ')) into Newworkno; RETURN Newworkno; end$ $DELIMITER;

2. Trigger when inserting data

Use ' It_system ';D elimiter $ $CREATE definer= ' root ' @ ' localhost ' TRIGGER ' operation_work_bins ' before INSERT on ' operation _work ' For each rowbegin SET @workNo = Get_workno ();           Set [email protected];end

MySQL number generation