MySQL ids by proper series

We are building an invoice management app and need help/suggestions with the following...

1. Invoice should star开发者_如何学运维t with 1 and according to series like 1 2 3 4 5 6 7 8 not 1 3 4 7
2. It should start with 1 again each financial year for example it should start with 1 again in 2012
3. how to show all invoices older than febuary 2011 and newer than march 2010 means financial year

We want your suggestions to achieve this. thanks.

you need compound primary key. eg:

year int
invoice_id int

1. The incrementing ID is trivial in MySQL. A simple auto_Increment primary key column does the trick.
2. Having the number wrap back to 1 each year can't be done with vanilla SQL without triggers and/or client-side involvement.
3. The fiscal year business is more of a client-side issue. Simply store a "created on" timestamp with each record and then decide on the filtering rules in the client, then you simply do select ... where date_of_record between $start_of_period and $end_of_period.

Create the primary key as in heximal's answer. Then use this to insert a new id:

insert into my_table (id, year)
   values (
      coalesce (
      (
         select new_id = max(id) + 1
         from my_table
         where year = @year
      ), 1)
      , @year
   )