Created
October 31, 2019 15:39
-
-
Save 10gic/9c485d3b1280959fb2af42511fe11936 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Following sql will fill 1000000 rows in table tb1 | |
| -- Example of generated data: | |
| -- mysql> select * from tb1 limit 10; | |
| -- +----+-------+-------+-----------------------+----------------------------------+ | |
| -- | id | score | name | email | hash | | |
| -- +----+-------+-------+-----------------------+----------------------------------+ | |
| -- | 1 | 3 | Jack | [email protected] | ba85d9424281f259f9cb2f40e63b5044 | | |
| -- | 2 | 1 | Bob | [email protected] | 21205c231fa846d35b0e776c40bd3a02 | | |
| -- | 3 | 3 | Jerry | [email protected] | 72f0e82a16b28da2953026230940eac9 | | |
| -- | 4 | 2 | Bob | [email protected] | 59e131ee35fd619243ee2e68daa8f6c3 | | |
| -- | 5 | 2 | Alice | [email protected] | 5be0f6ab5508a16953429936b3518e6f | | |
| -- | 6 | 1 | James | [email protected] | 6be71b3c100fe1b9271e83db385b38a0 | | |
| -- | 7 | 3 | James | [email protected] | 16f1f004699fc6e4e317fc88a82acd03 | | |
| -- | 8 | 2 | Jack | [email protected] | 97593756769df157a3a6e4c8ea190a1b | | |
| -- | 9 | 3 | Jack | [email protected] | 01735283112604267064ad90364f9244 | | |
| -- | 10 | 3 | Jack | [email protected] | 552b9033bd3a09640974078d6dadf346 | | |
| -- +----+-------+-------+-----------------------+----------------------------------+ | |
| -- 10 rows in set (0.00 sec) | |
| CREATE TABLE tb1 ( | |
| id int NOT NULL PRIMARY KEY AUTO_INCREMENT, | |
| score int, | |
| name varchar(20), | |
| email varchar(40), | |
| hash char(32), | |
| key(score) | |
| ); | |
| create table names(id int auto_increment primary key, name varchar(20)); | |
| insert into names (name) values ('Alice'); | |
| insert into names (name) values ('Jerry'); | |
| insert into names (name) values ('James'); | |
| insert into names (name) values ('Jack'); | |
| insert into names (name) values ('Bob'); | |
| DROP PROCEDURE autoGenerate ; | |
| DELIMITER $$ | |
| CREATE PROCEDURE autoGenerate (rows int) | |
| BEGIN | |
| DECLARE i INT DEFAULT 0 ; | |
| DECLARE r1 FLOAT; | |
| DECLARE r2 FLOAT; | |
| DECLARE r3 FLOAT; | |
| DECLARE r4 FLOAT; | |
| SET autocommit = 0; | |
| WHILE | |
| (i < rows) DO | |
| SET r1 = rand(); | |
| SET r2 = rand(); | |
| SET r3 = rand(); | |
| SET r4 = rand(); | |
| INSERT INTO tb1(score, name, email, hash) | |
| SELECT | |
| FLOOR(1 + r1 * 3), -- generate a random integer between 1 and 3 | |
| (select name from names where id = FLOOR(1 + r2 * 5)), -- generate a random name from table names | |
| concat(substring(MD5(r3), 1, FLOOR(4 + r3 * 10)), "@xxx.com"), | |
| MD5(r4) | |
| FROM dual; | |
| SET i = i + 1 ; | |
| IF i%3000=0 THEN -- batch commit | |
| COMMIT; | |
| END IF; | |
| END WHILE ; | |
| SET autocommit =1; | |
| COMMIT; | |
| END $$ | |
| DELIMITER ; | |
| CALL autoGenerate(1000000); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment