===============
1 建立 Employee 表
DROP TABLE IF EXISTS employee; CREATE TABLE IF NOT EXISTS employee ( id INT PRIMARY KEY, name VARCHAR(40), dept_id INT );
2 建立 Department 表
DROP TABLE IF EXISTS department; CREATE TABLE IF NOT EXISTS department ( id INT PRIMARY KEY, name VARCHAR(40) );
3 建立一个产生随机 N 位字符串的函数
delimiter $$ DROP FUNCTION IF EXISTS rand_string; CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'qwertyuiopasdfghjklzxcvbnm'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i<n DO SET return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52), 1)); SET i = i + 1; END WHILE; RETURN return_str; END $$
4 建立一个产生 [1001, 1100] 数字的函数
delimiter $$ DROP FUNCTION IF EXISTS rand_num; CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = floor(1000 + floor(rand() * 100) + 1); RETURN i; END $$
5 相看函数是否创建成功
SHOW FUNCTION STATUS;
结果如下图:
6 建立批量插入 Employee 表的存储过程
delimiter $$ DROP PROCEDURE IF EXISTS insert_employee; CREATE PROCEDURE insert_employee(IN START INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; /*把autocommit设置成0*/ SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO employee(id, name, dept_id) VALUES((START + i), rand_string(30), rand_num()); UNTIL i = max_num END REPEAT; COMMIT; END $$
7 建立批量插入 Department 表的存储过程
delimiter $$ DROP PROCEDURE insert_dept; CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO department(id, name) VALUES((START + i), rand_string(10)); UNTIL i = max_num END REPEAT; COMMIT; END $$
8 调用存储过程来向表中批量插入数据
CALL insert_employee(100000000, 500);
说明:向 Employee 表中插入 500 条数据,id 范围是:[100000000+1, 100000000+500]
CALL insert_dept(1000, 100)
说明:向 Department 表中插入 100 条数据,id 范围是:[1000+1, 1000+100]
PS:
- 如果想向 Employee 表中插入更多数据,修改上面的 500 参数即可
- 小心修改上面的 100 这个参数,因为在 rand_num 函数中也把 100 写死了,如果要修改,一定要两边同步修改