[RESOLVED] MySQL: Composite key with Date and Int

[PhilgB]

I'm trying to create a human readable ID using the date the record was entered and an auto incrementing integer. This also helps to limit the size of the ID value. I have tested this in MySql and everything seems to work. I would just like to get a few comments on how reliable this is and if anyone has any better ideas.

Ex - Adding three records yesterday would result in:
2008-04-03-001
2008-04-03-002
2008-04-03-003

And adding two today:
2008-04-04-001
2008-04-04-002

Thanks!

[PhilgB]

Just to be clear:


CREATE TABLE IF NOT EXISTS `testing` (
`date` date NOT NULL,
`id` tinyint(3) unsigned zerofill NOT NULL auto_increment,
`subject` varchar(255) collate latin1_general_ci NOT NULL,
`body` text collate latin1_general_ci NOT NULL,
PRIMARY KEY (`date`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

[chazzy]

Phil, you don't want to use auto increment in that case. you need to create some kind of synchronized repository where you can pull out the next id based on a date because don't forget, a lot of our apps need to support many users hitting them at once. Sound ok?

[PhilgB]

Thanks Chazzy.

I've never had a problem with auto increment in the past; Are you suggesting that it could produce duplicate IDs?

[chazzy]

no, not at all. in fact, it looks like you want duplicates (ie the 1/2/3/etc, after the date), which isn't how auto increments work.

there are a few things you could try, such as a trigger, that might help you get closer to what you're trying to do.

[PhilgB]

Well I've already created the table and keys are generated as expected. I'm just wondering if there are any caveats or long term problems with this approach.

There are duplicates in the date field but the ID field is incremented when the date is the same.

Thanks.

[chazzy]

I'm a little confused - are you saying that the id starts over w/ each day? How did you define the table?

[PhilgB]

My second post show the exact definition.

If I enter two records with '2008-04-06' for the date it will generate:
2008-04-06-001
2008-04-06-002

If I enter another with '2008-04-07' for the date, the ID will start at 1 again:
2008-04-07-001

If I again enter another with the first date 2008-04-06 it increments accordingly:
2008-04-06-003

[chazzy]

ah sorry. i forget some of the nuances of mysql.

someone called this a bug (http://bugs.mysql.com/bug.php?id=10184)

mysql reference manual says it's how it should behave (http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html)

Sorry for the confusion!

[PhilgB]

Thank you Chazzy!

the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1

Not good...

What are some common techniques for generating composite keys such as this?

I really have no experience beyond using an auto incrementing field and would prefer to have the database do the work. Are there any system functions for reliably generating a sequence within MySql using a Trigger as you mentioned?

Was your first suggestion to track the last used ID for a given date? I can see that approach causing certain conflicts as well; unless I'm missing something.

[PhilgB]

Thanks again Chazzy.

I ended up making a table to track the sequences:

CREATE TABLE IF NOT EXISTS 'refs' (
'date' date NOT NULL,
'seq' smallint(4) unsigned zerofill NOT NULL,
PRIMARY KEY (`date`)
)


And a function to increment based on the date and return the new reference:

CREATE FUNCTION 'new_ref'(p_date DATE) RETURNS char(15)
BEGIN
DECLARE s INT(4) UNSIGNED ZEROFILL;
SET s = COALESCE((SELECT seq FROM refs WHERE date=p_date LIMIT 1),0)+1;
INSERT INTO refs VALUES (p_date, s) ON DUPLICATE KEY UPDATE date=VALUES(date), seq=VALUES(seq);
RETURN CONCAT(p_date,'-',s);
END


Then a trigger to fill the field automatically:

CREATE TRIGGER 'per_insert' BEFORE INSERT ON 'per'
FOR EACH ROW BEGIN
SET new.ref = new_key(new.date);
END


It all seems to work pretty well. Is this what you had in mind in the beginning?

Have a good one.