January 7th, 2014, 04:51 PM
Generating a formatted autoincrement?
A user requirement for one of my tables is to have a unique sessionID composed of a three letter string, followed by an auto incrementing integer, followed by the year.
eg RAM-1-2013 then RAM-2-2013 then RAM-3-2013 etc.
I've looked at similar threads and have an idea this can be done with triggers and an auto incrementing column somewhere else but am not sure how to do it
Then there is a further requirement. The incrementing integer must reset to 1 at the first insert of a new year.
So the first insert in 2014 would generate RAM-1-2014 etc.
I've been thinking, playing, experimenting and worrying about race conditions for over a week now.
Can anyone offer me a way to achive this behaviour (with innoDB tables)?
The three character string will probably be taken from another table via a simple 'select prefix from area_codes where area_name = xxx'
January 8th, 2014, 08:59 AM
Generating a formatted autoincrement
Finally found a way using a separate table that just holds the formatedSessionIDs and a couple of other useful fields and using a stored procedure to generate and store a pseudo formatted autoincremented value that can then be used as the primary key elsewhere.
Originally Posted by dochsm
Here is the code in case anyone else wants something similar.
you can see it here http://sqlfiddle.com/#!2/d232c/8
The table DDL
The code for the stored procedure
CREATE TABLE `SessionIDs` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`DateOfSession` date DEFAULT NULL,
`FormattedSessionID` varchar(45) DEFAULT NULL,
`Area` varchar(45) DEFAULT NULL,
`SequenceNumber` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
and its called with something like
CREATE PROCEDURE GenerateFormattedSessionID (TheArea varchar(45), TheSessionDate Date, out NewSessionID int)
-- generates a formatted SessionID of the form XXX-n-YYYY where XXX is the first three letters of the area
-- n is a sequence number for that Area that resets each year and YYY is the Year
-- it outputs the session ID as a single integer for use as a PK in the sessions table or to look up the formatted session id
DECLARE HighestYear varchar(4);
DECLARE ThisSessionYear varchar(4);
DECLARE HighestSequenceNumber INT;
DECLARE NewFromattedSessionID VARCHAR(45);
SET ThisSessionYear = year(TheSessionDate);
SET HighestYear = (select max(year(DateOfSession) )from SessionIDs where Area = TheArea);
if (HighestYear is null) THEN -- no entries at all yet for this Area
SET HighestSequenceNumber = 1; -- init sequence to 1
IF HighestYear < ThisSessionYear THEN -- first insert of a new year
set HighestSequenceNumber = 1; -- init sequence back to 1
ELSE -- simply add one to existing highest sequence number for this team and this year
SET HighestSequenceNumber = (select max(SequenceNumber)
where Area = TheArea and year(DateOfSession) = year(TheSessionDate) ) + 1;
-- now we have the correct sequence number, get the first three letters from the Area and create the formatted sessionID
SET NewFromattedSessionID = CONCAT(UPPER(LEFT(TheArea,3)), '-', CAST(HighestSequenceNumber as CHAR(10)), '-',ThisSessionYear);
-- now insert the new record
INSERT into SessionIDs
SET NewSessionID = LAST_INSERT_ID(); -- send out the last record id to use as the PK in the Sessions table
call StoreNewSession('London', '2014-02-07',@ThePK);