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. UNION SELECT n */) AS dummy_tblįROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) as dummy_tbl column_name.tbl_name is the column we want to updateįROM (SELECT 1 /* UNION SELECT 2. 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 SELECT GROUP_CONCAT(SUBSTRING('01', 1+ FLOOR(RAND()*LENGTH('01')) ,1) SEPARATOR '') AS random_binaryįROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS dummy_tbl SELECT GROUP_CONCAT(SUBSTRING(chars_str, 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1) SEPARATOR '')įROM (SELECT 1 /* UNION SELECT 2. The string length is the length of dummy_tbl which corresponds to the number of SELECT you have in the FROM.Replace chars_str with a string containing the allowed characters.This query returns a random string constitud of characters from the string chars_str.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: +-+ Generate a random string of characters Download SQL for generating random data for foreign-key dependent child tables: populatefk.sql How to install and generate dummy data: mysql -uUSER -pPASSWORD DATABASE < populatedummydata. 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) Download the SQL code: Generate dummy data for MySQL. 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. 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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |