How to set a variable if a row already exists?
I have a table like so:
What I want to do is to try inserting a new row with a value into the lURL field, but if the record already exists, then set a variable @sURL to the value of the sURL column for the record in question.
CREATE TABLE `shortURL` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sURL` VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`lURL` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lURL` (`lURL`),
KEY `sURL` (`sURL`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Currently I'm doing this like so:
Is there a better way of setting the @sURL to the value of sURL if the record already exists?
The full procedure looks like this:
CREATE FUNCTION makesURL (lURL CHAR(255))
RETURNS CHAR(5) DETERMINISTIC
#First try AND INSERT the long URL
INSERT INTO shortURL VALUES(NULL, '', lURL)
#If the long URL already EXISTS, THEN SET @sURL TO the short URL VALUE
ON DUPLICATE KEY UPDATE
id = IF( (@sURL := sURL), id, id);
#If the record didn't already exist, so we've just inserted a NEW record, CREATE the short URL AND UPDATE the record
IF @sURL IS NULL THEN
SET @sURL = strFromNum(LAST_INSERT_ID());
UPDATE shortURL SET shortURL.sURL = @sURL
WHERE shortURL.id = LAST_INSERT_ID()
P.S. Most records will be new, so trying to insert the record first rather than trying to select it first is much faster.