Mysql存储过程（procedure）增加测试数据

2019独角兽企业重金招聘Python工程师标准>>>

语法

存储过程语法如下:

　　CREATE PROCEDURE <存储过程名称>(参数列表)

　　存储过程体

删除存储过程:

　　DROP PROCEDURE <存储过程名称>

调用自定义函数语法:

　　SELECT <存储过程名称>(parameter_value,...)

语法实例

create procedure test_procedure()
begin
declare i int;
set i=0;
while i<100000 do
insert into test_innodb values(i+1,rand_string(100));
insert into test_myisam values(i+1,rand_string(100));
commit;
set i = i+1;
end while;
end;

效果

    注：由于存储过程中是为两个表中增加10W条测试数据，导致执行时间过长。建议更改为1000，如果不修改可以增加游标批量添加数据。今后会有介绍

mysql> show create procedure test_procedure
;
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure      | sql_mode               | Create Procedure                                                                                                                                                                                                                                     | character_set_client | collation_connection | Database Collation |
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test_procedure | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()
begin
declare i int;
set i=0;
while i<100000 do
insert into test_innodb values(i+1,rand_string(100));
insert into test_myisam values(i+1,rand_string(100));
commit;
set i = i+1;
end while;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set
mysql> show create procedure test_procedure
;
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure      | sql_mode               | Create Procedure                                                                                                                                                                                                                                     | character_set_client | collation_connection | Database Collation |
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test_procedure | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()
begin
declare i int;
set i=0;
while i<100000 do
insert into test_innodb values(i+1,rand_string(100));
insert into test_myisam values(i+1,rand_string(100));
commit;
set i = i+1;
end while;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set
mysql> call test_procedure();

  rand_string(100)为上一篇自定义函数

名词解释

大写	小写	词义	用法
USE	use	切换数据库	use test
IF EXIST	if exist	判断是否存在	IF EXIST 名
DELIMITER	delimiter	定义结束符	delimiter //
DEFINER	definer	权限	definer=root@localhost
PROCEDURE	procedure	存储过程	procedure procedure_name
DECLARE	declare	声明	DECLARE return_str varchar(10240)
WHILE..DO	while..do	while循环	while i < n do
COMMIT	commit	提交	commit;
CALL	call	执行存储过程	call procedure_name();

转载于:https://my.oschina.net/Clarences/blog/909844