// you’re reading...

MySQL

Use MySQL to generate a tiny url style string

tinyurlWe all appreciate getting small urls and the most famous link generator is http://tinyurl.com/. They do a perfect job of creating shortened URLs using random strings.

The idea is pretty simple. Create a string of N char length and use it as key to reference a value. Perhaps it’s a URL or a document or anything that needs a unique key.

Recently, we needed this at work. After reviewing a few different implementations in a few different programming languages, it seemed obvious that this could be accomplished within MySQL using a FUNCTION. This allows comparisons to be done in the database using SQL, thus avoiding implementations across languages.

SQL

DROP FUNCTION IF EXISTS randomstring;
DELIMITER $$
CREATE FUNCTION randomstring(len TINYINT) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE str VARCHAR(20);
DECLARE charlist VARCHAR(40);
DECLARE itr TINYINT;
DECLARE idx TINYINT;
DECLARE charlistlen TINYINT;
SET charlist        = 'ABCDEFGHJKLMNPQRSTUVWXYZ234567891';
SET charlistlen     = CHAR_LENGTH(charlist) - 1;
SET itr             = 0;
SET idx             = 1;
SET str             = '';
WHILE itr < len DO
SET idx = ROUND((RAND() * charlistlen) + 1);
SET str = CONCAT(str,SUBSTR(charlist,idx,1));
SET itr = itr + 1;
END WHILE;
RETURN str;
END
$$
DELIMITER ;
SELECT randomstring(4);

Results:

mysql> SELECT randomstring(4);
+-----------------+
| randomstring(4) |
+-----------------+
| NQB7            |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT randomstring(5);
+-----------------+
| randomstring(5) |
+-----------------+
| SY8ZY           |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT randomstring(10);
+------------------+
| randomstring(10) |
+------------------+
| N2W8Z5ATVP       |
+------------------+
1 row in set (0.00 sec)

Discussion

3 comments for “Use MySQL to generate a tiny url style string”

  1. An impressive share! I’ve just forwarded this onto a co-worker who had been conducting a little research on this.

    And he actually ordered me dinner due to the fact that I found
    it for him… lol. So allow me to reword this…. Thank YOU for the meal!!
    But yeah, thanks for spending some time to discuss this topic here on your site.

    Posted by customizedshirt | December 9, 2015, 1:34 pm
  2. Super article, merci pour le partage !

    Posted by regime dukan | June 30, 2016, 3:48 pm
  3. I think this is one of the most vital info for me.

    And i’m glad reading your article. But want
    to remark on some general things, The web site style is ideal,
    the articles is really great : D. Good job,
    cheers

    Posted by SEO | July 25, 2016, 7:42 am

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: