We 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)