{"id":1790,"date":"2012-07-05T07:51:20","date_gmt":"2012-07-05T07:51:20","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=1790"},"modified":"2026-03-02T05:00:39","modified_gmt":"2026-03-02T05:00:39","slug":"generate-random-test-data-for-mysql-using-routines","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/generate-random-test-data-for-mysql-using-routines","title":{"rendered":"How To Generate Random test Data In MySQL"},"content":{"rendered":"\n<p>Are you tired of manually generating test data for your MySQL tables? If you&#8217;re looking for random data generator, look no further! Introducing the <strong>MySQL Random Data Generator<\/strong>, a powerful tool designed to effortlessly create random test data for your tables. There are tools that will generate random data for you but they&#8217;re not free, though random data generator is free and open source. Importantly it remains in your control. <\/p>\n\n\n\n<p><strong>NEW<\/strong>: I have created another alternative for you to generate random test data for MySQL. <a href=\"https:\/\/nitty-witty.com\/mysql-random-data-generator\" target=\"_blank\" rel=\"noopener\" title=\"\">https:\/\/nitty-witty.com\/mysql-random-data-generator<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Why Use MySQL Random Data Generator<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Table-Aware:<\/strong> My tool leverages MySQL&#8217;s deep understanding of its own tables to automatically generate data based on table definitions.<\/li>\n\n\n\n<li><strong>Effortless:<\/strong> Say goodbye to time-consuming perl or shell scripts with loops. Let MySQL handle the data generation efficiently.<\/li>\n\n\n\n<li><strong>Free and Open Source:<\/strong> Download the tool from my GitHub repository for free. I encourage you to report bugs and contribute to improvements.<\/li>\n<\/ul>\n\n\n\n<p>I hate generating dummy data, yes I do and I assume you do too! I think that&#8217;s the major reason I wrote these MySQL functions and procedures for Generating dummy test data.<br>I wrote combination of MySQL store routines and procedures that will generate the random test data for you without needing to specify table information. The tool will automatically generate the random data based on the table definition. You can download the data generator tool for MySQL from Github for free. Please consider reporting bugs and improvements.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Download: <a href=\"https:\/\/github.com\/kedarvj\/mysql-random-data-generator\" target=\"_blank\" rel=\"nofollow noopener\">MySQL  Random Data Generator<\/a>.<\/h3>\n\n\n\n<!--more Continue Reading...-->\n\n\n\n<p><br>People can manage these things with a simple perl \/ shell script with loops but again that always need your time.<br>Why can&#8217;t MySQL generate data for it&#8217;s own table when MySQL better knows the table than anyone else !! <\/p>\n\n\n\n<p>Below are a set of functions and a stored procedure that will make our life easy and of-course it&#8217;s free.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Download Random Data Generator Tool For MySQL:<\/strong><\/h2>\n\n\n\n<p>1. Download the SQL code: <a href=\"https:\/\/raw.githubusercontent.com\/kedarvj\/mysql-random-data-generator\/master\/populate.sql\" target=\"_blank\" rel=\"noopener\"><strong>Generate dummy data for MySQL<\/strong><\/a>.<br>2. Download SQL for generating random data for foreign-key dependent child tables: <a href=\"https:\/\/raw.githubusercontent.com\/kedarvj\/mysql-random-data-generator\/master\/populate_fk.sql\">populate_fk.sql<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>How to install and generate dummy data:<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code><code>mysql -uUSER -pPASSWORD DATABASE &lt; populate_dummy_data.txt<\/code><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>How generate test data<\/strong><\/h2>\n\n\n\n<p>To generate test data of 1000 rows for sakila.film table execute following SQL command: <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>call populate('sakila','film',1000,'N');<\/code><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>MySQL set of functions to get random values generated for individual data-types.<\/strong><\/h2>\n\n\n\n<p>Important Note: <em>This is very first version (2012) of the script consider getting the latest from github.<\/em><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">## MySQL function to generate random string of specified length\nDROP function if exists get_string;\ndelimiter $\nCREATE FUNCTION get_string(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC\nBEGIN \nset @var:='';\nwhile(in_strlen&gt;0) do\nset @var:=concat(@var,IFNULL(ELT(1+FLOOR(RAND() * 53), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),'Kedar'));\nset in_strlen:=in_strlen-1;\nend while;\t\nRETURN @var;\nEND $\ndelimiter ;\n\n\n## MySQL function to generate random Enum-ID from specified enum definition\nDELIMITER $\nDROP FUNCTION IF EXISTS get_enum $\nCREATE FUNCTION get_enum(col_type varchar(100)) RETURNS VARCHAR(100) DETERMINISTIC\n\tRETURN if((@var:=ceil(rand()*10)) &gt; (length(col_type)-length(replace(col_type,',',''))+1),(length(col_type)-length(replace(col_type,',',''))+1),@var);\n$\nDELIMITER ;\n\n\n## MySQL function to generate random float value from specified precision and scale.\nDELIMITER $\nDROP FUNCTION IF EXISTS get_float $\nCREATE FUNCTION get_float(in_precision int, in_scale int) RETURNS VARCHAR(100) DETERMINISTIC\n\tRETURN round(rand()*pow(10,(in_precision-in_scale)),in_scale) \n$\nDELIMITER ;\n\n\n\n## MySQL function to generate random date (of year 2012).\nDELIMITER $\nDROP FUNCTION IF EXISTS get_date $\nCREATE FUNCTION get_date() RETURNS VARCHAR(10) DETERMINISTIC\n\tRETURN DATE(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))\n#\tBelow will generate random data for random years\n#\tRETURN DATE(FROM_UNIXTIME(RAND() * (1577817000 - 946665000) + 1325356200))\n$\nDELIMITER ;\n\n\n## MySQL function to generate random time.\nDELIMITER $\nDROP FUNCTION IF EXISTS get_time $\nCREATE FUNCTION get_time() RETURNS INTEGER DETERMINISTIC\n\tRETURN TIME(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))\n$\nDELIMITER ;\n\n## MySQL function to generate random int.\nDELIMITER $\nDROP FUNCTION IF EXISTS get_int $\nCREATE FUNCTION get_int() RETURNS INTEGER DETERMINISTIC\n\tRETURN floor(rand()*10000000) \n$\nDELIMITER ;\n\n## MySQL function to generate random tinyint.\nDELIMITER $\nDROP FUNCTION IF EXISTS get_tinyint $\nCREATE FUNCTION get_tinyint() RETURNS INTEGER DETERMINISTIC\n\tRETURN floor(rand()*100) \n$\nDELIMITER ;\n\n## MySQL function to generate random varchar column of specified length(alpha-numeric string).\nDELIMITER $\nDROP FUNCTION IF EXISTS get_varchar $\nCREATE FUNCTION get_varchar(in_length varchar(500)) RETURNS VARCHAR(500) DETERMINISTIC\n\tRETURN SUBSTRING(MD5(RAND()) FROM 1 FOR in_length)\n$\nDELIMITER ;\n\n## MySQL function to generate random datetime value (any datetime of year 2012).\nDELIMITER $\nDROP FUNCTION IF EXISTS get_datetime $\nCREATE FUNCTION get_datetime() RETURNS VARCHAR(30) DETERMINISTIC\n\tRETURN FROM_UNIXTIME(ROUND(RAND() * (1356892200 - 1325356200)) + 1325356200)\n$\nDELIMITER ;<\/pre>\n\n\n\n<p><strong>The MySQL Stored procedure that populates MySQL tables with dummy data<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DELIMITER $\n\nDROP PROCEDURE IF EXISTS populate $\nCREATE PROCEDURE populate(in_db varchar(50), in_table varchar(50), in_rows int, in_debug char(1)) \nBEGIN\n\/*\n|\n| Developer: Kedar Vaijanapurkar\n| USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE);\n| EXAMPLE: call populate('sakila','film',100,'N');\n| Debug-mode will print an SQL that's executed and iterated.\n|\n*\/\n\nDECLARE col_name VARCHAR(100);\nDECLARE col_type VARCHAR(100); \nDECLARE col_datatype VARCHAR(100);\nDECLARE col_maxlen VARCHAR(100); \nDECLARE col_extra VARCHAR(100);\nDECLARE col_num_precision VARCHAR(100);\nDECLARE col_num_scale VARCHAR(100);\nDECLARE func_query VARCHAR(1000);\nDECLARE i INT;\n\nDECLARE done INT DEFAULT 0;\nDECLARE cur_datatype cursor FOR\n SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_schema.columns WHERE table_name=in_table AND table_schema=in_db;\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n\n\nSET func_query='';\nOPEN cur_datatype;\ndatatype_loop: loop\n\nFETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale;\n#SELECT CONCAT(col_name,\"-\", col_type,\"-\", col_datatype,\"-\", IFNULL(col_maxlen,'NULL'),\"-\", IFNULL(col_extra,'NULL')) AS VALS;\n  IF (done = 1) THEN\n    leave datatype_loop;\n  END IF;\n\nCASE \nWHEN col_extra='auto_increment' THEN SET func_query=concat(func_query,'NULL, ');\nWHEN col_datatype in ('int','bigint') THEN SET func_query=concat(func_query,'get_int(), ');\nWHEN col_datatype in ('varchar','char') THEN SET func_query=concat(func_query,'get_string(',ifnull(col_maxlen,0),'), ');\nWHEN col_datatype in ('tinyint', 'smallint','year') or col_datatype='mediumint' THEN SET func_query=concat(func_query,'get_tinyint(), ');\nWHEN col_datatype in ('datetime','timestamp') THEN SET func_query=concat(func_query,'get_datetime(), ');\nWHEN col_datatype in ('float', 'decimal') THEN SET func_query=concat(func_query,'get_float(',col_num_precision,',',col_num_scale,'), ');\nWHEN col_datatype in ('enum','set') THEN SET func_query=concat(func_query,'get_enum(\"',col_type,'\"), ');\nWHEN col_datatype in ('GEOMETRY','POINT','LINESTRING','POLYGON','MULTIPOINT','MULTILINESTRING','MULTIPOLYGON','GEOMETRYCOLLECTION') THEN SET func_query=concat(func_query,'NULL, ');\nELSE SET func_query=concat(func_query,'get_varchar(',ifnull(col_maxlen,0),'), ');\nEND CASE;\n\n\nend loop  datatype_loop;\nclose cur_datatype;\n\nSET func_query=trim(trailing ', ' FROM func_query);\nSET @func_query=concat(\"INSERT INTO \", in_db,\".\",in_table,\" VALUES (\",func_query,\");\");\n\tIF in_debug='Y' THEN\n\t\tselect @func_query;\n\tEND IF;\nSET i=in_rows;\npopulate :loop\n\tWHILE (i&gt;0) DO\n\t  PREPARE t_stmt FROM @func_query;\n\t  EXECUTE t_stmt;\n\tSET i=i-1;\nEND WHILE;\nLEAVE populate;\nEND LOOP populate;\nSELECT \"Kedar Vaijanapurkar\" AS \"Developed by\";\nEND\n$\nDELIMITER ;<\/pre>\n\n\n\n<p>Let me know if you like it and share if you find it useful. Please report any bug.<br><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>In the realm of MySQL database management, the process of generating test data has long been a tedious endeavor. My MySQL Random Data Generator seeks to revolutionize this experience by offering a free, efficient, and automated solution. Consider star-ing and forking the repository if you&#8217;re keep to contribute.<\/p>\n","protected":false},"excerpt":{"rendered":"Are you tired of manually generating test data for your MySQL tables? If you&#8217;re looking for random data generator, look no further! Introducing the MySQL Random Data Generator, a powerful&hellip;\n","protected":false},"author":1,"featured_media":3223,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[556,557,337,333,353,334,427,423,335,336,555],"class_list":{"0":"post-1790","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-mysql","8":"category-mysql-scripts-mysql","9":"tag-data-generator","10":"tag-dummy-data-generator","11":"tag-free","12":"tag-generate-dummy-data","13":"tag-generate-random-data","14":"tag-generate-test-data","15":"tag-mysql","16":"tag-mysql-5-7","17":"tag-random-data","18":"tag-routine","19":"tag-test-data-generator"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1790"}],"version-history":[{"count":32,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1790\/revisions"}],"predecessor-version":[{"id":3556,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1790\/revisions\/3556"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3223"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1790"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}