mysql存储过程添加s_Mysql存储过程（procedure）增加测试数据

语法

存储过程语法如下:

CREATE PROCEDURE(参数列表)

存储过程体

删除存储过程:

DROPPROCEDURE

调用自定义函数语法:

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 

COMMIT

commit

提交

commit;

CALL

call

执行存储过程

call procedure_name();