> -----Original Message----- > From: Andrew Kreps [mailto:andrew.kreps@xxxxxxxxx] > Sent: Thursday, January 06, 2005 12:30 PM > Subject: Re: MySQL Auto PK > > On Wed, 05 Jan 2005 18:11:23 -0500, John Holmes <holmes072000@xxxxxxxxxxx> wrote: > > OOzy Pal wrote: > > > Is it possible to have mysql at an ID as 20050105-1 as (YYYYMMDD-1), > > > -2, etc. > > > > automatically? No. But you can always just use > > SELECT CONCAT(date_column,'-',pk_column) AS fixed_id ... > > if you _really_ need something like this. Or just join them together in PHP. > > I think the only downside to that solution is that the primary key will continue to increment regardless of the day, and I think the original poster wanted: > > 20050105-1 > 20050105-2 > 20050106-1 > ...etc. > > This would be a great place for a stored procedure, but I don't know if I can recommend running MySQL 5 to you. The most platform-safe way I can think of is to get a count(*) of the number of rows with today's date, add 1 to it, and stick that number on the end of the string you've created to insert into a varchar field. It's an extra query per insert, but it'd do the job. Doing a count and tagging the number on the back could result in race conditions with a much larger window than one would prefer (albeit still small). Wouldn't tagging the time on the end of the date accomplish the overall goal? You can pull them from the database as is (with their full date/time - ordered by such) and then trim the date out (mysql could even do this for you) and tag on "-$rownum". Still, no atomicity for guarantee of uniqueness. The primary key should be used solely for unique identification purposes, how it looks visually should be of little concern/interest - at least, given the data I've seen so far (e.g. it's just based on date/time). This really seems like a perfect candidate for an autoincrement and a separate date / datetime field. Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
