本小节主要包含:
- 存储过程是什么,基本操作
- 插入一千万条数据,为后续数据库优化做准备。
存储过程,可以视作函数,是为了完成特定功能的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