-----------------------------------------------建表-------------------------------------------------------

CREATE TABLE orders (
    sn_day DECIMAL (32) PRIMARY KEY,
    sn_hours DECIMAL (32),
    sn_minutes DECIMAL (32),
    remark VARCHAR (100)
);

---------------------------------------------------触发器----------------------------------------------------

DELIMITER $$
CREATE TRIGGER sn_by_day BEFORE INSERT ON orders FOR EACH ROW
BEGIN
    DECLARE
        n INT;
 
SELECT
    IFNULL(MAX(RIGHT(sn_day, 10)), 0) INTO n
FROM
    orders
WHERE
    MID(sn_day, 1, 8) = DATE_FORMAT(NOW(), '%Y%m%d');
 
SET NEW.sn_day = CONCAT(
    DATE_FORMAT(NOW(), '%Y%m%d'),
    RIGHT (10000000001 + n, 10)
);
 
END $$
DELIMITER ;

#插入一条数据
INSERT orders (remark) VALUES (CONCAT('demo',DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s')));

--------------------------------------------------注意：------------------------------------------------

在数据库建表完成后，记得将非空的那个取消，设置为可以设置为空