在 PHP MySQL 中自动生成账单编号

您可以在数据库中使用一个标准的自动递增id列、一个时间戳和一个触发器来完成此操作.

作为演示，以下是表格mytable:

CREATE TABLE mytable (
  id int not null primary key auto_increment,
  created timestamp not null default CURRENT_TIMESTAMP,
  bilno char(11),
  something varchar(100),
  otherthing varchar(100)
);

现在，我们设置了一个触发器，每当我们插入一行时都会触发:

DELIMITER //
CREATE TRIGGER mytable_ins BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN

    DECLARE minId INT default 0;

    SET @myId := (SELECT AUTO_INCREMENT
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = 'mytable'
        AND TABLE_SCHEMA = DATABASE());

    SET @minId := (SELECT MIN(id)
        FROM mytable
        WHERE YEAR(created) = YEAR(NEW.created)
        AND MONTH(created) = MONTH(NEW.created));

    IF @minId IS NULL THEN
        SET @bilId = 1;
    ELSE
        SET @bilId = 1 + @myId - @minId;
    END IF;

    SET NEW.bilno = CONCAT('B',
        '-', DATE_FORMAT(NEW.created, "%m%y"),
        '-', LPAD(@bilId, 4, '0')
    );

END;//
DELIMITER ;

由于在触发BEFORE INSERT触发器时，自动递增列的值不可用，因此我们将从INFORMATION_SCHEMA数据库中获取该值.(重要提示:因此，此解决方案仅适用于一次插入一行)

然后，我们将插入的行的id与本月已插入的最低id进行比较.如果没有先前的id，则我们是第一个，因此将bil编号指定为‘1’.如果存在先前的id，则将这两个id相减以得到bil编号.

最后，对结果进行适当的格式化，包括月份和日期.

要进行测试，请创建一些历史数据:

INSERT INTO mytable (something) VALUES ('a');
INSERT INTO mytable (something) VALUES ('b');
INSERT INTO mytable (something) VALUES ('c');
UPDATE mytable SET
    bilno = REPLACE(bilno, DATE_FORMAT(created, "%m%y"), '0823'),
    created = '2023-08-04 18:43:11';

并创建一些新数据:

INSERT INTO mytable (something) VALUES ('d');
INSERT INTO mytable (something) VALUES ('e');

判断结果:

SELECT * FROM mytable;

+----+---------------------+-------------+-----------+------------+
| id | created             | bilno       | something | otherthing |
+----+---------------------+-------------+-----------+------------+
|  1 | 2023-08-04 18:43:11 | B-0823-0001 | a         | NULL       |
|  2 | 2023-08-04 18:43:11 | B-0823-0002 | b         | NULL       |
|  3 | 2023-08-04 18:43:11 | B-0823-0003 | c         | NULL       |
|  4 | 2023-09-05 19:17:13 | B-0923-0001 | d         | NULL       |
|  5 | 2023-09-05 19:17:13 | B-0923-0002 | e         | NULL       |
+----+---------------------+-------------+-----------+------------+