-- 实现方式
DROP FUNCTION IF EXISTS `rand_string`;
SET NAMES utf8;
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN
DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i+1;
END WHILE;
RETURN return_str;
END;
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_data`;
CREATE PROCEDURE `add_data`(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE user_id INT DEFAULT 0;
DECLARE pid INT DEFAULT 0;
DECLARE eid INT DEFAULT 0;
DECLARE toid INT DEFAULT 0;
DECLARE mark INT DEFAULT 0;
DECLARE fromwhere INT DEFAULT 0;
DECLARE name VARCHAR(20) DEFAULT '';
DECLARE mobilePhone VARCHAR(20) DEFAULT '';
DECLARE company VARCHAR(50) DEFAULT '';
DECLARE ctime INT DEFAULT 0;
DECLARE id INT DEFAULT 1;
-- 插入语句的前半部分
set @pre_sql = "INSERT INTO `jl_records_10013524` (`id`, `userId`, `eid`, `pid`, `toid`, `mark`, `fromwhere`, `name`, `mobilePhone`, `company`, `ctime`) VALUES ";
set @exec_sql = @pre_sql;
WHILE i < n DO
SET user_id = FLOOR(200000 + RAND() * (400000 - 200000));
SET pid = FLOOR(1 + RAND() * 100000);
SET eid = 10013524;
SET toid = FLOOR(20000000 + RAND() * (40000000 - 20000000));
SET mark = FLOOR(0 + RAND()*10);
SET fromwhere = FLOOR(0 + RAND()*5);
SET name = rand_string(7);
SET mobilePhone = FLOOR(13000000000 + RAND() * (20000000000 - 13000000000));
SET company = rand_string(10);
SET ctime = FLOOR(1600000000 + RAND() * (1700000000 - 1600000000));
SET id = i;
set @exec_sql = concat(@exec_sql,"(NULL,", user_id, ",", eid, ",", pid, ",", toid, ",", mark, ",", fromwhere, ",'", name, "','", mobilePhone, "','", company, "',", ctime, "),");
-- INSERT INTO `jl_records_10013524` (`id`, `userId`, `eid`, `pid`, `toid`, `mark`, `fromwhere`, `name`, `mobilePhone`, `company`, `ctime`) VALUES (NULL, user_id, eid, pid, toid, mark, fromwhere, name, mobilePhone, company, ctime);
SET i = i + 1;
END WHILE;
-- 出除sql最后一个逗号
set @exec_sql = substring(@exec_sql, 1, char_length(@exec_sql)-1);
-- 预处理并执行sql
prepare stmt from @exec_sql;
execute stmt;
deallocate prepare stmt;
END;
CALL add_data(10000);
发表评论: