True Random Database and Table Name Generator - Part 2 of 2

Permalink: http://bit.ly/QuBLVB



Read part 1 for the rationale behind the code.

As discussed in the first part of this blog entry, we'll be utilizing a statement that uses base 36 to generate the random name. We will be adding the $ and _ characters using the ELT function. Here is a true random database and table name generator:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
 ) RETURNS varchar(64) CHARSET utf8
BEGIN
 DECLARE numberOfChars, charDiceRoll TINYINT(2);
 DECLARE charCount TINYINT DEFAULT 0;
 DECLARE randomChar CHAR(1);
 DECLARE randomName VARCHAR(64) DEFAULT '';
 SET numberOfChars = randomRangePicker(1, 64);
 REPEAT
  SET charCount = charCount + 1;
  SET charDiceRoll = randomRangePicker(1, 38);
  IF (charDiceRoll <= 2)
  THEN
   SET randomChar = ELT(charDiceRoll, '$', '_');
  ELSE
   SET charDiceRoll = charDiceRoll - 3;
   SET randomChar = LOWER(
    CONV(
     charDiceRoll,
     10, 36)
   );
  END IF;
  SET randomName = CONCAT(randomName, randomChar);
 UNTIL (charCount = numberOfChars)
 END REPEAT;
 RETURN randomName;
END $$
DELIMITER ;

Total of 38 characters: 36 case-insensitive alphanumeric characters a-z, 0-9, including $ and _. For code portability between Windows and Linux, the random name generated is all in lowercase characters. Each character has a 1/38 chance to be generated.

Note that we utilized the random number range picker function mentioned in a previous blog entry. Here's an explanation of the code:

• The length of the name is random. Anywhere from 1 to 64 characters long can be generated. The maximum number of characters is based on the value of numberOfChars.
• A character will be generated based on the result of a dice roll from 1 to 38. The 1 to 38 range represents each of the 38 characters.
• The name will be created random character by random character by the CONCAT function until the length is equal to numberOfChars.

Here's a version of the above function that doesn't depend on the random number range picker function:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`() RETURNS varchar(64) CHARSET utf8
BEGIN
 DECLARE numberOfChars, charDiceRoll TINYINT(2);
 DECLARE charCount TINYINT DEFAULT 0;
 DECLARE randomChar CHAR(1);
 DECLARE randomName VARCHAR(64) DEFAULT '';
 SET numberOfChars = 1 + FLOOR(RAND() * 64);
 REPEAT
  SET charCount = charCount + 1;
  SET charDiceRoll = 1 + FLOOR(RAND() * 38);
  IF (charDiceRoll <= 2)
  THEN
   SET randomChar = ELT(charDiceRoll, '$', '_');
  ELSE
   SET charDiceRoll = charDiceRoll - 3;
   SET randomChar = LOWER(
     CONV(
       charDiceRoll,
       10, 36)
   );
  END IF;
  SET randomName = CONCAT(randomName, randomChar);
 UNTIL (charCount = numberOfChars)
 END REPEAT;
 RETURN randomName;
END $$
DELIMITER ;

You can check out the function's output by:

SELECT randomNameGenerator();

This function can be modified to become a random string generator of fixed length or even to become a random password generator. Additional characters can be added to the ELT function, and the statement that uses base 36 can be converted to randomly generate uppercase and lowercase characters. If numbers are not desired to be in the random name, simply modify the range for the base 36 statement so that it will not generate 0-9.

Back to part 1.



Updated: 10/19/2012