Shulou(Shulou.com)06/01 Report--

Batch generation of random test data:

The contents are as follows:

# cat populate.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS populate $$

CREATE PROCEDURE populate (in_db varchar 50), in_table varchar 50, in_rows int, in_debug char 1)

BEGIN

/ *

| |

| | USAGE: call populate ('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE) |

| | EXAMPLE: call populate ('sakila','film',100,'N') |

| Debug-mode will print an SQL that's executed and iterated.

| |

, /

DECLARE col_name VARCHAR (100)

DECLARE col_type VARCHAR (100)

DECLARE col_datatype VARCHAR (100)

DECLARE col_maxlen VARCHAR (100)

DECLARE col_extra VARCHAR (100)

DECLARE col_num_precision VARCHAR (100)

DECLARE col_num_scale VARCHAR (100)

DECLARE func_query VARCHAR (1000)

DECLARE i INT

DECLARE done INT DEFAULT 0

DECLARE cur_datatype cursor FOR

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

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

SET func_query=''

OPEN cur_datatype

Datatype_loop: loop

FETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale

# SELECT CONCAT (col_name, "-", col_type, "-", col_datatype, "-", IFNULL (col_maxlen,'NULL'), "-", IFNULL (col_extra,'NULL')) AS VALS

IF (done = 1) THEN

Leave datatype_loop

END IF

CASE

WHEN col_extra='auto_increment' THEN SET func_query=concat (func_query,'NULL,')

WHEN col_datatype in ('int','bigint') THEN SET func_query=concat (func_query,'get_int (),')

WHEN col_datatype in ('varchar','char') THEN SET func_query=concat (func_query,'get_string (', ifnull (col_maxlen,0),'),)

WHEN col_datatype in ('tinyint',' smallint','year') or col_datatype='mediumint' THEN SET func_query=concat (func_query,'get_tinyint (),')

WHEN col_datatype in ('datetime','timestamp') THEN SET func_query=concat (func_query,'get_datetime (),')

WHEN col_datatype in ('date') THEN SET func_query=concat (func_query,'get_date (),')

WHEN col_datatype in ('float',' decimal') THEN SET func_query=concat (func_query,'get_float (', col_num_precision,',',col_num_scale,'),')

WHEN col_datatype in ('enum','set') THEN SET func_query=concat (func_query,'get_enum ("', col_type,'"),')

WHEN col_datatype in ('GEOMETRY','POINT','LINESTRING','POLYGON','MULTIPOINT','MULTILINESTRING','MULTIPOLYGON','GEOMETRYCOLLECTION') THEN SET func_query=concat (func_query,'NULL,')

ELSE SET func_query=concat (func_query,'get_varchar (', ifnull (col_maxlen,0),'),'

END CASE

End loop datatype_loop

Close cur_datatype

SET func_query=trim (trailing', 'FROM func_query)

SET @ func_query=concat ("INSERT INTO", in_db, ".", in_table, "VALUES (", func_query, ");)

IF in_debug='Y' THEN

Select @ func_query

END IF

SET i=in_rows

Populate: loop

WHILE (I > 0) DO

PREPARE t_stmt FROM @ func_query

EXECUTE t_stmt

SET i=i-1

END WHILE

LEAVE populate

END LOOP populate

SELECT "Kedar Vaijanapurkar" AS "Developed by"

END

$

DELIMITER

/ *

END OF STORED PROCEDURE

* /

/ *

| | Developer: Kedar Vaijanapurkar |

| MySQL set of function to get random values generated for individual data-types.

, /

# # MySQL function to generate random string of specified length

DROP function if exists get_string

Delimiter $$

CREATE FUNCTION get_string (in_strlen int) RETURNS VARCHAR (500) DETERMINISTIC

BEGIN

Set @ var:=''

While (in_strlen > 0) do

Set @ var:=concat (@ var,IFNULL (ELT (1+FLOOR (RAND () * 53),), 'axiomagrical (1+FLOOR (RAND ()) * 53),' axiomagemagrical (1+FLOOR (RAND ()) * 53), 'axiomanagrical (ELT (1+FLOOR (RAND ()) * 53)),' aaxiajiaomi (1+FLOOR (RAND () * 53)), 'aaxiaqiao,' (),', (), 'Achilles recalcitrants', 'Bandeliers', 'Candles', 'Ecos', 'Fulles', 'Gobires', 'Hobbles', 'Jacks', 'Kones', 'Lines','', 'Kedar').

Set in_strlen:=in_strlen-1

End while

RETURN @ var

END $$

Delimiter

# # MySQL function to generate random Enum-ID from specified enum definition

DELIMITER $$

DROP FUNCTION IF EXISTS get_enum $$

CREATE FUNCTION get_enum (col_type varchar (100)) RETURNS VARCHAR (100) DETERMINISTIC

RETURN if ((@ var:=ceil (rand () * 10)) > (length (col_type)-length (replace (col_type,'')) + 1), (length (col_type)-length (replace (col_type,'')) + 1), @ var)

$

DELIMITER

# # MySQL function to generate random float value from specified precision and scale.

DELIMITER $$

DROP FUNCTION IF EXISTS get_float $$

CREATE FUNCTION get_float (in_precision int, in_scale int) RETURNS VARCHAR (100) DETERMINISTIC

RETURN round (rand () * pow (10, (in_precision-in_scale)), in_scale)

$

DELIMITER

# # MySQL function to generate random date (of year 2012).

DELIMITER $$

DROP FUNCTION IF EXISTS get_date $$

CREATE FUNCTION get_date () RETURNS VARCHAR (10) DETERMINISTIC

RETURN DATE (FROM_UNIXTIME (RAND () * (1356892200-1325356200) + 1325356200))

# Below will generate random data for random years

# RETURN DATE (FROM_UNIXTIME (RAND () * (1577817000-946665000) + 1325356200))

$

DELIMITER

# # MySQL function to generate random time.

DELIMITER $$

DROP FUNCTION IF EXISTS get_time $$

CREATE FUNCTION get_time () RETURNS INTEGER DETERMINISTIC

RETURN TIME (FROM_UNIXTIME (RAND () * (1356892200-1325356200) + 1325356200))

$

DELIMITER

# # MySQL function to generate random int.

DELIMITER $$

DROP FUNCTION IF EXISTS get_int $$

CREATE FUNCTION get_int () RETURNS INTEGER DETERMINISTIC

RETURN floor (rand () * 10000000)

$

DELIMITER

# # MySQL function to generate random tinyint.

DELIMITER $$

DROP FUNCTION IF EXISTS get_tinyint $$

CREATE FUNCTION get_tinyint () RETURNS INTEGER DETERMINISTIC

RETURN floor (rand () * 100)

$

DELIMITER

# # MySQL function to generate random varchar column of specified length (alpha-numeric string)

DELIMITER $$

DROP FUNCTION IF EXISTS get_varchar $$

CREATE FUNCTION get_varchar (in_length varchar (500)) RETURNS VARCHAR (500) DETERMINISTIC

RETURN SUBSTRING (MD5 (RAND ()) FROM 1 FOR in_length)

$

DELIMITER

# # MySQL function to generate random datetime value (any datetime of year 2012).

DELIMITER $$

DROP FUNCTION IF EXISTS get_datetime $$

CREATE FUNCTION get_datetime () RETURNS VARCHAR (30) DETERMINISTIC

RETURN FROM_UNIXTIME (ROUND (RAND () * (1356892200-1325356200)) + 1325356200)

$

DELIMITER

How to use it:

Mysql > use DBNAME

-Parameters are: `database- name`, `table- name`, `number-of- records`, `debug- mode`

-Setting `debug- Mode` as `Y` will print all the insert statements that are being executed.

Examples of usage:

Mysql > call populate ('test','test',100,'N')

+-+

| | Developed by |

+-+

| | Kedar Vaijanapurkar |

+-+

1 row in set (2.38 sec)

Query OK, 0 rows affected (2.38 sec)

Stored procedure parameter description:

The first parameter should enter the database name, the second parameter should enter the table name, the third parameter should enter the number of records to insert, and the fourth parameter is whether to turn on debugging.

Mode, if opened, prints out the SQL statement that is being executed, such as:

Mysql > use test

Mysql > call populate ('test','test',100,'Y')

+-- +

| | @ func_query |

+-- +

| | INSERT INTO test.test VALUES (get_int (), get_string (80)); |

+-- +

1 row in set (0.01 sec)

+-+

| | Developed by |

+-+

| | Kedar Vaijanapurkar |

+-+

1 row in set (3.51 sec)

Query OK, 0 rows affected (3.51 sec)

The stored procedure SQL statement is downloaded from:

Https://github.com/kedarvj/mysql-random-data-generator/blob/master/populate.sql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.