Create MySQL Stored Function Custom Counter Number

 This Stored Function will create number with format (ymdcounter) such this 2010150034.

CREATE FUNCTION `{function_name}`() RETURNS varchar(25) CHARSET utf8mb4

    DETERMINISTIC

BEGIN

    DECLARE rowcount INT;

    DECLARE reval VARCHAR(25);

    

    SELECT COUNT(*) INTO rowcount

    FROM {table_name} where {field} = CURDATE();

    SELECT concat( DATE_FORMAT(CURDATE(),'%y%m%d') , LPAD((rowcount + 1),4,'0')) INTO reval;

RETURN reval;

END

Create MySQL Trigger Counter Number Before Save New Data

 before insert

CREATE `{function_name}` BEFORE INSERT ON `{table}` FOR EACH ROW BEGIN

    DECLARE rowcount INT;

    SELECT COUNT(*) 

    INTO rowcount

    FROM {table} where {field} = CURDATE();

SET NEW.{field_to_be_set} = concat( DATE_FORMAT(CURDATE(),'%y%m%d') , LPAD((rowcount + 1),4,'0'));

   

END;