Auto-increment - automatic reset every year
MySQL / InnoDB
In my case, my receipts must be posted annually; 1/2015, 2/2015 ... 556/2015 and so on. When the next year comes, the counter should start at 1 time and receipts should be counted 1/2016, 2/2016 ...
How do I define an auto_increment field that will reset itself on an annual basis?
RCID | RCNO | RCYEAR | ...
=====+======+========+====
200 | 1 | 2015 |
201 | 2 | 2015 |
... | ... | 2015 |
756 | 556 | 2015 | <- last receipt in 2015
757 | 1 | 2016 | <- yearly counter restarted
NOTE: RCID is a standard PK appended field.
+3
sbrbot
source
to share
2 answers
This is not the correct use AUTO_INCREMENT
. If you want something special, as you describe, do it yourself.
MyISAM has a feature like this (the second column in the PRIMARY KEY might be the same AUTO_INCREMENT). But InnoDB is preferred these days. Simulation method: my blog
0
Rick james
source
to share
After @RickJames help the solution:
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;
+4
sbrbot
source
to share