MySQL tip: Update a column with random values

I’m always impressed with what you can do with MySQL (my favorite RDBMS), not long ago I needed to add a column in a table and fill it with some random values. Instead of using a PHP (could be C, Java, perl…) script that goes row by row and calculates a random value, I thought it would be challenging to do it with a single SQL query.

Generate a random character

This query returns a random character from the string chars_str, replace chars_str with a string containing the characters you want to allow:

SELECT SUBSTRING(chars_str , 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1);

--Example

SELECT SUBSTRING('01234567' , 1+ FLOOR(RAND()*LENGTH('01234567')) ,1) AS random_octal;
+--------------+
| random_octal |
+--------------+
| 6            |
+--------------+

Generate a random string of characters

In order to generate a random string we need to generate many random characters, thus we need to repeat the previous query, or we can simply create a table and have the query executed on each rows of that table, MySQL will generate for us one random character per table row and we’ll concatenate them (with GROUP_CONCAT [...] SEPARATOR '' )to produce our final random string:

• This query returns a random string constitud of characters from the string chars_str
• Replace chars_str with a string containing the allowed characters
• The string length is the length of dummy_tbl which corresponds to the number of SELECT you have in the FROM

SELECT GROUP_CONCAT(SUBSTRING(chars_str , 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1) SEPARATOR '') 
FROM (SELECT 1 /* UNION SELECT 2 ... UNION SELECT n */) as dummy_tbl;

-- Example

SELECT GROUP_CONCAT(SUBSTRING('01' , 1+ FLOOR(RAND()*LENGTH('01')) ,1) SEPARATOR '') AS random_binary
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS dummy_tbl;
+---------------+
| random_binary |
+---------------+
| 0111          |
+---------------+

Update a column with random values

Now need to update the column of our database with random values, we will do so by nesting the query that generates random values into a normal UPDATE statement

-- column_name.tbl_name is the column we want to update

UPDATE tbl_name SET column_name = (
  SELECT GROUP_CONCAT(SUBSTRING(chars_str , 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1) SEPARATOR '')
  FROM (SELECT 1 /* UNION SELECT 2 ... UNION SELECT n */) AS dummy_tbl
);

-- Example

UPDATE tickets SET code = (
  SELECT GROUP_CONCAT(SUBSTRING('123abcABC-_$@' , 1+ FLOOR(RAND()*LENGTH('123abcABC-_$@')) ,1) SEPARATOR '')
  FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) as dummy_tbl
);

There you have it, there is probably a better –and more of less complicated– way to do it, you may want to try doing the same thing with procedures, if you do so, please share your results on the comments section.