Add a date prefix on mysql id

Question

I have an ID field that i would like it to have the following format: YYMMDDXXXXXXXX where YY is the year, MM the month, DD the day and XXXXXXXX is the actual ID number with leading zeroes.Also the ID number should reset to 0 when the day changes.

First, is this a not so good practise/format for an ID field thats also going to be a primary key?

Implementation:

I'm inserting data to my database threw php, i came up with 2 ways to achieve the said format.

• Inserting it manual with the php insert query(no AUTO_INCREMENT on the field), i select the MAX(id), split it(date-id), check if the day has changed and then either just increment it by one or replace it by newdate+0s. The problem with this way is that i have to do an extra SELECT and check/change the string, SELECT may return the same MAX(id) if i have more than 20 requests/sec and also date('Ymd') causes the apache to crash for more than 60 requests/sec.
• Adding AUTO_INCREMENT on the ID field and set the AUTO_INCREMENT on YYMMDD00000000 when the day changes, then letting the auto_increment do the job. I tried ALTER TABLE foo AUTO_INCREMENT=(SELECT CURDATE()*100000000) but as i expected CURDATE() can't be used like this.

Any ideas and/or help on my tries would be great, thanks for your time.

Answer 1

The sane way:

1. Tell your team manager that this design is massively flawed.

2. Present the following schema instead:

   CREATE TABLE foo (
     id INTEGER AUTO_INCREMENT,
     date DATETIME DEFAULT=NOW(),
     PRIMARY KEY (id),
     INDEX (date)
   );
   

3. If necessary generate a similarly-styled ID with the query:

   SELECT CAST(DATE_FORMAT(date,'%Y%m%d') AS UNSIGNED)*100000000 + id AS 'weird_id'
   

   Though this will go off the rails if your IDs become > 10,000,000, though my testing shows that using a BIGINT for this sheme should be able to handle one or two more zeroes. You can also get around the integer type limitations by selecting this ID as a string, eg:

   SELECT CONCAT(DATE_FORMAT(date, '%Y%m%d'), LPAD(id,10,'0')) AS 'weird_id'
   

   Where 10 in the LPAD() can be as many digits as you want.

The not-sane, face-saving, future-nightmare-causing way:

You almost had it. CURDATE() returns a string, and ALTER TABLE foo AUTO_INCREMENT= expects an integer and nothing but an integer. No functions, variables, etc. The following will work:

bash:

mysql -u root -e "ALTER TABLE dbname.foo AUTO_INCREMENT = $(date +%Y%m%d0000000000);"

PHP:

$query = sprintf("ALTER TABLE dbname.foo AUTO_INCREMENT = %s;", str_pad(date("Ymd", time()), 18, '0'));

This will break:

• Whenever crond hiccups
• Probably during daylight savings time changes
• Randomly and for no apparent reason
• After midnight on December 31, 9999

Do yourself, and whoever inherits this project from you, a favor and keep a separate DATETIME column in this table so that meaningful, not-broken info can still be gleaned from it in the future.