Jump to content

MySQL Autonumber To Start at 1 again


9911782

Recommended Posts

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.

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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.