For testing purposes, it is very useful sometimes to generate some random content in a mysql table. Such table simulates a real table with content generated in long time. For instance, suppose you have a mysql table with comments from users. Each day, users post a number of comments. Thus, i a year or so, there have been accumulated many comments (let’s say, about 100.000 comments). But you would like to test some database queries in a real environment before users can post their comments. So, this post will explain how to generate a big table with comments (automatically generated) using mysql stored routines.
First, I’ve got an English dictionary with words, which I’ve imported into a simple MySQL table with structure described below:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE `dict_en` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `word` VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`), INDEX `word` (`word`) ) COMMENT='English dictionary' COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DEFAULT; |
Second, I’ve created a simple table that will host so-called articles (randomly created):
1 2 3 4 5 6 7 8 9 | CREATE TABLE `articles` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `content` MEDIUMTEXT NULL, PRIMARY KEY (`id`) ) COMMENT='articole' COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DEFAULT; |
Now, you should import a simple text file with dictionary data, each word on a single line. This could be done with LOAD DATA INFILE command like this:
1 2 3 4 | LOAD DATA INFILE 'my_dictionary_file.txt' INTO TABLE dict_en FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n" (`word`); |
At this moment, we use the dictionary table to generate some variable-width random generated text that simulated a comment. For this example, let’s generate a comment with a random number of words between 1 and 1000, words separated by SPACE. This can be accomplished with MySQL function described below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE FUNCTION `create_content_by_wordlist`() RETURNS mediumtext LANGUAGE SQL DETERMINISTIC COMMENT 'Creates a text with random words from dictionary table' BEGIN DECLARE retVal MEDIUMTEXT DEFAULT ''; DECLARE iter INT(10) DEFAULT 0; DECLARE randLength INT(10) DEFAULT 0; SET randLength = FLOOR(1000 * rand()); SET iter=0; SET retVal = ''; SELECT COUNT(`id`) INTO @cntWL FROM dict_en; WHILE (iter<randLength) DO SET @curId = FLOOR(@cntWL * RAND()); SELECT word INTO @wrd FROM dict_en WHERE `id`=@curId LIMIT 1; SET retVal = CONCAT(retVal,' ', @wrd); SET iter = iter+1; END WHILE; RETURN retVal; END |
Now it’s time to fill the comments table, which is named “articles” for simplicity. Next stored routine starts filling that table with content. It accepts a single parameter: number of comments (or articles):
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE PROCEDURE `create_articles`(IN `_records` INT) LANGUAGE SQL NOT DETERMINISTIC COMMENT 'Fill articles table' BEGIN DECLARE contor INT(10) DEFAULT 0; SET contor = 0; # TRUNCATE TABLE articles; WHILE (contor<_records) DO INSERT INTO articles SET `content`=create_content_by_wordlist(); SET contor = contor+1; END WHILE; END |
All prepared now! So, let’s put those stored routines to work. All you have to do is just create table for dictionary, fill it with data (I’ve used a text file with 350k english words). Finally, put mysql to hard work: run create_articles() procedure:
1 | CALL create_articles(10000); |
Be aware, a greater number of articles make articles table bigger. In my situation, for 250k articles randomly generated, the articles table has about 5G in size. It is a good situation for testing large MySQL tables. You could use indexes, partitions or other engines to test different study cases.
Loading ...
