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

2021年11月22日 阅读数:1
这篇文章主要向大家介绍使用存储过程插入一千万条数据,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

本小节主要包含:mysql

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

存储过程,能够视做函数,是为了完成特定功能的SQL语句集。能够将重复性很高、批处理操做封装成一个存储过程当中,简化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 自定义函数

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

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位随机字符。code

首先建立一张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;

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

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 ;

执行结果it

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