使用存储过程插入一千万条数据

本小节主要包含：

• 存储过程是什么，基本操作
• 插入一千万条数据，为后续数据库优化做准备。

存储过程，可以视作函数，是为了完成特定功能的SQL语句集。可以将重复性很高、批处理操作封装成一个存储过程中，简化SQL调用。

1 存储过程的基本语法

1.1 创建存储过程

delimiter $$
CREATE PROCEDURE show_employee()
BEGIN
	具体操作
END$$
delimiter ;

调用存储过程

CALL show_employee();

查看存储过程状态

SHOW PROCEDURE STATUS WHERE DB = 'emall';
SHOW CREATE PROCEDURE show_employee;

1.2 变量声明与赋值

delimiter $$
CREATE PROCEDURE show_employee()
BEGIN
	-- 声明
	DECLARE x,y int DEFAULT 0;
	DECLARE avgSalary double DEFAULT 0;
	-- 两种赋值方式
	SET x = 3;
	SELECT avg(salary) INTO avgSalary FROM employee;
END$$
delimiter ;

1.3 参数传递

参数有三种：in、out、inout。

delimiter $$
CREATE PROCEDURE getSalary(IN n varchar(255), OUT s int)
BEGIN
	SELECT salary INTO s FROM employee WHERE name = n;
end$$
delimiter ;

CALL getSalary('鲁班', @s); -- 返回结果到@s
SELECT @s FROM DUAL; -- 从@s中取值

-- INOUT
delimiter $$
CREATE PROCEDURE cusinout(INOUT n int, in i int)
BEGIN
	SET n = n + i;
end$$
delimiter ;

set @num = 2;
call cusinout(@num, 1);
SELECT @num from dual;

1.4 自定义函数

返回指定位数的随机字符串。

delimiter $$
-- 创建函数
create function rand_str(n int) returns varchar(255)
BEGIN
    -- 声明所有字母变量
    DECLARE str varchar(100) default '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
    -- 记录当前第几个字符
    declare i int default 0;
    -- 生成结果
    declare res_str varchar(255) default '';
    while i < n do
    -- 1 + RAND() * 62 随机数
    -- floor(1 + RAND() * 62) 取整
    -- substr(str, floor(1 + RAND() * 62), 1) 从str中随机取一个字符
    -- 拼接到res_str后面
    set res_str = CONCAT(res_str,substr(str, floor(1 + RAND() * 62), 1));
    set i = i + 1;
    end while;
    return res_str;
END$$
delimiter ;

select rand_str(5);

2 插入1000万条数据

使用上个小节中自定义的函数，插入1000万条数据，顾客名字使用5位随机字符。

首先创建一张customer表，仅两个字段。

-- emall.customer definition
CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写存储过程，禁止自动提交，避免每条数据都插表。

delimiter $$
create procedure insert_customer(int startNum int, int max_num int)
begin
	declare i int default 0;
	-- 关闭默认自动提交sql
	set autocommit = 0;
	-- 循环插入
	repeat
	set i = i + 1;
	insert into customer (id, name) values(startNum + i, rand_str(5));
	until i = max_num
	end repeat;
	-- 整体提交
	commit; 
end$$
delimiter ;

执行结果

[SQL]call insert_customer(100, 10000000);
受影响的行: 0
时间: 1381.161s