定义
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集
功能
(批量插入)
是将常用或复杂的工作,预先用SQL语句写好并用一个指定名称存储起来, 以后需要数据库提供与已定义好的存储过程的功能相同的服务时,只需调用 call 存储过程名字, 即可自动完成命令。
存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,可由应用程序通过一个调用来执行,而且允许用户声明变量 。
同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。
查看所有存储过程
select `name` from MySQL.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
show procedure status;
删除存储过程
drop procedure if exists your_procedure;
创建存储过程
CREATE TABLE example (
id int PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL
)
create PROCEDURE your_prod()
BEGIN
DECLARE i int;
set i=0;
START TRANSACTION;
while i < 100 DO
insert into example
(name, value)
values(rand() * 10 * i,rand() * 20 * i);
set i=i+1;
end while;
COMMIT;
end
调用call命令执行操作
call your_prod();
提速方法
最快的当然是直接 copy 数据库表的数据文件(版本和平台最好要相同或相似);
1. 设置 innodb_flush_log_at_trx_commit = 0 ,相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度;
2. 使用 load data local infile 提速明显;
3. 修改参数 bulk_insert_buffer_size, 调大批量插入的缓存;
4. 合并多条 insert 为一条: insert into t values(a,b,c), (d,e,f) ,,,
P.S.
插入一条时
INSERT INTO example
( name, value)
VALUES
( 'Name 1', 'Value 1');
插入N条时,合并为一条
INSERT INTO example
(name, value)
VALUES
( 'Name 1', 'Value 1'),
( 'Name 2', 'Value 2'),
( 'Name 3', 'Value 3'),
('Name 4', 'Value 4');
如果我们插入列的顺序和表中列的顺序一致的话,还可以省去列名的定义
INSERT INTO example
VALUES
( 'Name 1', 'Value 1'),
( 'Name 2', 'Value 2'),
( 'Name 3', 'Value 3'),
('Name 4', 'Value 4');
5. 手动使用事物;