CODEWITHSUNDEEP
javamysqlprimary-keygenerator
Joël Craenhals
Joël Craenhals

Reputation: 475

MySQL custom primary key generator

I have written an invoice module for our reservation system.

So when I create a new invoice, I automatically generate a primary key through MySQL.

However for the moment this is just a simple integer counting up.

The problem is that we are obligated to have an invoice number in the form of "#year#id" where #year is e.g. 2013 and #id is an id that start every year back at 1.

So e.g. 20131, 20132, 20133 and in 2014 it wil be 20141, 20142.

How can I fix this with a custom primary key generator?

Upvotes: 2

Views: 3507

Answers (3)

Joop Eggen
Joop Eggen

Reputation: 109547

A warning against naive year/week like 2012/01. YEAR(NOW()) being wrong.

Better use YEARWEEK(NOW()). Gives a number like 201201

The following shows that year is not necessarily year-of-week-number:

mysql> SELECT YEARWEEK('1987-01-01');
       -> 198652

For the rest having year and week in one single int, is in fact simpler.

int year = yearweek / 100;
int week = yearweek % 100;

Upvotes: 0

araknoid
araknoid

Reputation: 3125

You should use a procedure or a trigger. The particular query that builds your newInvoiceId should be something like this:

SELECT CONCAT(YEAR(now()),count(*)+1) as newInvoiceId
FROM table
WHERE InvoiceId like CONCAT(YEAR(now()),'%');

The only part where i'm not 100% confident is the CONCAT(YEAR(now()),'%')

Upvotes: 2

Micha van Eijk
Micha van Eijk

Reputation: 138

You can make two fields in your, year and id.

Make one primary key on both fields, giving id the auto_increment option. For each unique value of year, id will be counting up. For example:

2012    1
2012    2
2012    3
2013    1

And you can concat them when selecting: SELECT CONCAT(year,id) AS primary FROM table

Inserting will be:

INSERT INTO table SET year = YEAR(NOW())

you don't have to specify id.

Upvotes: 2

Related Questions