Clever Geek Handbook

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


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


source


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


source






More articles:


All Articles