MySQL Autonumber To Start at 1 again

[9911782]

Hi

I need help please...

 

I've created a MySQL database. Capturing the data per financial year. Every year when the date is 1st April 2008, I want the autonumber to start at 1 again. How do i do that in MySQL.

 

Thank you

S

[trq]

There is no logical reason for wanting to do this, and it will break your database as auto incrementing field need to be a unique key.

 

If you are relying on these id's for sorting or something simular you are doing things wrong.

 

Explain why you think you need to do this.

[luca200]

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

[aschk]

Wow, i never knew about the PRIMARY KEY grouping option with an auto_increment value.

The real question is how is your table structured? Do you have 1 entry per day? Multiple per day?

You could possibly INSERT based on days from the 1st April...

 

[aschk]

The following will give you the day number from the 1st of April (1st being 1, 2nd being 2 etc)

SELECT
     CASE
      WHEN DAYOFYEAR(NOW()) >= DAYOFYEAR(CONCAT(YEAR(NOW()),'-04-01')) THEN
        DAYOFYEAR(NOW()) - DAYOFYEAR(CONCAT(YEAR(NOW()),'-04-01')) + 1
      WHEN DAYOFYEAR(NOW()) < DAYOFYEAR(CONCAT(YEAR(NOW()),'-04-01')) THEN
        DAYOFYEAR(CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)),'-12-31'))
       - DAYOFYEAR(CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)),'-04-01'))
       + DAYOFYEAR(NOW())
       + 1
     END;

 

Note: the above is safe when dealing with leap years.

[Barand]

CREATE TABLE (
   finyear INT NOT NULL,
   id INT NOT NULL AUTO_INCREMENT,
   transaction_date DATE,
   PRIMARY KEY (finyear, id)
)

 

When apr 1st is reached set finyear to the new year value when inserting new records