本文介绍了自动递增 - 每年自动重置的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
MySQL/InnoDB
MySQL/InnoDB
就我而言，我的收据应按年计算；1/2015、2/2015 ... 556/2015 等等.明年来的时候，柜台要重新从1开始，收据应该算为1/2016, 2/2016 ...
In my case my receipts should be counted on yearly basis; 1/2015, 2/2015 ... 556/2015 and so on. When next year comes, the counter should start from 1 again and receipts should be counted as 1/2016, 2/2016 ...
如何定义每年自动重置的 auto_increment 字段?
How to define auto_increment field which will reset itself on yearly basis?
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 自动递增字段.
NOTE: RCID is standard PK auto incremented field.
推荐答案
在@RickJames 的帮助下，解决方案是:
After help from @RickJames the solution is:
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;
这篇关于自动递增 - 每年自动重置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！