forked from kedarvj/mysql-random-data-generator
-
Notifications
You must be signed in to change notification settings - Fork 0
/
populate.sql
191 lines (160 loc) · 6.77 KB
/
populate.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
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
/*
|
| Developer: Kedar Vaijanapurkar
| 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.
| The data is being loaded in bulk of 500 rows which is hardcoded for now.
|
*/
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 batch_size 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 ('double','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,");");
SET @func_query=concat("INSERT IGNORE INTO ", in_db,".",in_table," VALUES (",func_query,")");
SET batch_size = 500;
while batch_size > 0 DO
set batch_size = batch_size - 1;
set @func_query = CONCAT( @func_query , " ,(",func_query,")" );
END WHILE;
set @func_query = CONCAT( @func_query , ";" );
IF in_debug='Y' THEN
select @func_query;
END IF;
SET i=in_rows;
SET batch_size=500;
populate :loop
WHILE (i>batch_size) DO
PREPARE t_stmt FROM @func_query;
EXECUTE t_stmt;
SET i = i - batch_size;
END WHILE;
SET @func_query=concat("INSERT INTO ", in_db,".",in_table," VALUES (",func_query,");");
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:='';
IF (in_strlen>500) THEN SET in_strlen=500; END IF;
while(in_strlen>0) do
set @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'));
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 ;