Auto-increment -每年自动重置

Question

MySQL/InnoDB

在我的情况下，我的收入应该按年计算；1/2015，2/2015 ... 556/2015等等。当明年到来时，计数器应该再次从1开始，收据应该计算为1/2016，2/2016……

如何定义每年自动重置的auto_increment字段？

RCID | RCNO | RCYEAR | ...
=====+======+========+====
 200 |    1 |   2015 |
 201 |    2 |   2015 |
 ... |  ... |   2015 |     
 756 |  556 |   2015 |     <- last receipt in 2015
 757 |    1 |   2016 |     <- yearly counter restarted

注意: RCID是标准的PK自动递增字段。

Answer 1

在@RickJames的帮助下，解决方案是：

CREATE TRIGGER ReceiptNumber BEFORE INSERT ON receipts FOR EACH ROW
BEGIN
  SET NEW.rcyear=YEAR(NOW());
  SET NEW.rcno=(SELECT IFNULL(MAX(rcno),0)+1 FROM receipts WHERE rcyear=YEAR(NOW()));
END;