SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_lumns WHERE table_name=in_table AND table_schema=in_db ĭECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 įETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale | Debug-mode will print an SQL that's executed and iterated. | EXAMPLE: call populate('sakila','film',100,'N') | USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE) The MySQL Stored procedure that populates MySQL tables with dummy data:ĬREATE PROCEDURE populate(in_db varchar(50), in_table varchar(50), in_rows int, in_debug char(1)) # MySQL function to generate random datetime value (any datetime of year 2012).ĬREATE FUNCTION get_datetime() RETURNS VARCHAR(30) DETERMINISTIC RETURN SUBSTRING(MD5(RAND()) FROM 1 FOR in_length)
# MySQL function to generate random varchar column of specified length(alpha-numeric string).ĬREATE FUNCTION get_varchar(in_length varchar(500)) RETURNS VARCHAR(500) DETERMINISTIC # MySQL function to generate random tinyint.ĬREATE FUNCTION get_tinyint() RETURNS INTEGER DETERMINISTIC # MySQL function to generate random int.ĬREATE FUNCTION get_int() RETURNS INTEGER DETERMINISTIC # MySQL function to generate random time.ĬREATE FUNCTION get_time() RETURNS INTEGER DETERMINISTIC #ěelow will generate random data for random years # MySQL function to generate random date (of year 2012).ĬREATE FUNCTION get_date() RETURNS VARCHAR(10) DETERMINISTIC RETURN round(rand()*pow(10,(in_precision-in_scale)),in_scale) # MySQL function to generate random float value from specified precision and scale.ĬREATE FUNCTION get_float(in_precision int, in_scale int) RETURNS VARCHAR(100) DETERMINISTIC # MySQL function to generate random Enum-ID from specified enum definitionĬREATE FUNCTION get_enum(col_type varchar(100)) RETURNS VARCHAR(100) DETERMINISTIC
#Dummy data generator download
Download sql for generating random data for foreign-key dependent child tables: populate_fk.sql Download the sql code: Generate dummy data for MySQL.Ģ.
#Dummy data generator free
Why can’t MySQL generate data for it’s own table when MySQL better knows the table than anyone else !! 🙂īelow are a set of functions and a stored procedure that will make our life easy and of-course it’s free ).ġ. People can manage these things with a simple perl / shell script with loops but again that always need your time.
I hate generating dummy data, yes I do and I assume you do too! I think that’s the major reason I wrote these MySQL functions and procedures for Generating dummy test data. At-times you’ll write scripts to generate data but those will be table specific. There are tools that will generate random data for you but they’re not free. At times you’ll find yourself responsible for generating test data for newly created tables for testing or sampling purpose.
0 kommentar(er)
