mysql存储过程参数测试,Mysql生成百万测试数据存储过程编写

一、编写工具函数：

# 生成n个随机数字

DELIMITER $$

CREATE FUNCTION randNum(n int) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str varchar(20) DEFAULT '0123456789';

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()*10 ),1));

SET i = i +1;

END WHILE;

RETURN return_str;

END $$

DELIMITER;

# 生成随机手机号码

# 定义常用的手机头 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157

# SET starts = 1+floor(rand()*15)*4; 截取字符串的开始是从 1、5、9、13 ...开始的。floor(rand()*15)的取值范围是0~14

# SET head = substring(bodys,starts,3);在字符串bodys中从starts位置截取三位

DELIMITER $$

CREATE FUNCTION generatePhone() RETURNS varchar(20)

BEGIN

DECLARE head char(3);

DECLARE phone varchar(20);

DECLARE bodys varchar(100) default "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";

DECLARE starts int;

SET starts = 1+floor(rand()*15)*4;

SET head = trim(substring(bodys,starts,3));

SET phone = trim(concat(head,randNum(8)));

RETURN phone;

END $$

DELIMITER ;

# 创建随机字符串和随机时间的函数

DELIMITER $$

CREATE FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4

DETERMINISTIC

BEGIN

DECLARE chars_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(chars_str, FLOOR(1 + RAND() * 62), 1));

SET i = i + 1;

END WHILE;

RETURN return_str;

END$$

DELIMITER;

二、创键普通表和内存表

# 创建普通表

CREATE TABLE `test_user` (

`id` int(11) NOT NULL AUTO_INCREMENT comment '主键id',

`user_id` varchar(36) NOT NULL comment '用户id',

`user_name` varchar(30) NOT NULL comment '用户名称',

`phone` varchar(20) NOT NULL comment '手机号码',

`lan_id` int(9) NOT NULL comment '本地网',

`region_id` int(9) NOT NULL comment '区域',

`create_time` datetime NOT NULL comment '创建时间',

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 创建内存表

CREATE TABLE `test_user_memory` (

`id` int(11) NOT NULL AUTO_INCREMENT comment '主键id',

`user_id` varchar(36) NOT NULL comment '用户id',

`user_name` varchar(30) NOT NULL comment '用户名称',

`phone` varchar(20) NOT NULL comment '手机号码',

`lan_id` int(9) NOT NULL comment '本地网',

`region_id` int(9) NOT NULL comment '区域',

`create_time` datetime NOT NULL comment '创建时间',

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

三、创建插入内存表数据的存储过程

# 创建插入内存表数据存储过程 入参n是多少就插入多少条数据

DELIMITER $$

CREATE PROCEDURE `add_test_user_memory`(IN n int)

BEGIN

DECLARE i INT DEFAULT 1;

WHILE (i <= n) DO

INSERT INTO test_user_memory (user_id, user_name, phone, lan_id,region_id, create_time) VALUES (uuid(), randStr(20), generatePhone(), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());

SET i = i + 1;

END WHILE;

END $$

DELIMITER ;

四、创建内存表数据插入普通表的存储过程

# 循环从内存表获取数据插入普通表

# 参数描述 n表示循环调用几次；count表示每次插入内存表和普通表的数据量

DELIMITER $$

CREATE PROCEDURE `add_test_user_memory_to_outside`(IN n int, IN count int)

BEGIN

DECLARE i INT DEFAULT 1;

WHILE (i <= n) DO

#先调用存储过程往内存表插入一万条数据，然后再把内存表的一万条数据插入普通表

CALL add_test_user_memory(count);

#一次性把内存表的数据插入到普通表，这个过程是很快的

INSERT INTO test_user SELECT * FROM test_user_memory;

#清空内存表数据

delete from test_user_memory;

SET i = i + 1;

END WHILE;

END $$

DELIMITER ;

五、使用示例

# 插入20000条数据

CALL add_test_user_memory_to_outside(2,10000)

注意事项：

内存表在存储数据的时候，有可能会发生内存溢出，可以通过调整参数 tmp_table_size、max_heap_table_size这两个参数对临时表的大小进行控制，对应的sql如下：

# 查看max_heap_table_size大小

show variables like "%max_heap_table_size%";

# 设置max_heap_table_size大小为2G

set max_heap_table_size=2147483648;

# 查看tmp_table_size大小

show variables like "%tmp_table_size%";

# 设置tmp_table_size大小为2G

set tmp_table_size=2147483648;