Client wants a field in the mysql DB to be composed of a prefix, the year, and a counter that resets each year. PREFIX 2010 0001 ... PREFIX 2010 0734, then PREFIX 2011 0001. Are there any mysql tricks to make that happen or do I keep track of the largest number used for each year. Any thoughts appreciated.
+1
A:
MySQL can't create complex fields like that, but you could create a view that would simulate it, based on something like:
SELECT CONCAT('PREFIX', YEAR(NOW()), MOD(id, 10000)), ...
As long as there aren't more than 10,000 of these in a year, it'd be relatively safe as a "public" primary key. And on the plus side, if the id field is a normal auto_increment int in the background, you'd have a real primary key to work from.
Marc B
2010-09-09 00:01:49
@Marc - how does the id part get reset to '0001' at the start of the new year?
martin clayton
2010-09-09 00:06:04
... also - what about leading zeroes for the id part? The first value produced by the sample code would be 'PREFIX20101' I think.
martin clayton
2010-09-09 00:12:56
True enough. Some magic based on REPEAT(), LENGTH() etc.. could handle the padding/formatting. The yearly reset would be harder. Possibly a server-side variable with a yearly event trigger to update with a proper offset/modulo... It'd need some experimentation, but the basics of this answer are most likely the best solution.
Marc B
2010-09-09 01:01:47