MySQL存储过程的优点
预编译，相对于直接的SQL效率会高点，同时可以降低SQL语句传输过程中消耗的流量；
简化业务逻辑，可以把需求转化给专业的DBA(如果有的话)；
更方便的使用MySQL数据库事物的处理，尤其是购物类网站；
安全、用户权限更容易管理；
修改存储过程基本上不需要修改程序代码，而直接写SQL修改SQL一般都要修改相关的程序
mysql储存过程的创建等语句：
1、CREATE PROCEDURE （创建储存过程）
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
SQL语句代码块
END
注：由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。 当使用delimiter命令时，你应该避免使用反斜杠(‘’)字符，因为那是MySQL的转义字符。
| 代码如下 | 复制代码 |
|
CREATE PROCEDURE proEntpTypeInfo(iid int(11),lvl int) BEGIN -- 局部变量定义 declare tid int(11) default -1 ; declare ttype_name varchar(255) default '' ; declare tptype_id int(11) default -1 ; -- 游标定义 declare cur1 CURSOR FOR select id,type_name,ptype_id from entp_type_info where (ptype_id=iid or id=iid)and type = 20 and is_del = 0; -- 游标介绍定义 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,ttype_name=null,tptype_id=null; SET @@max_sp_recursion_depth = 13; -- 开游标 OPEN cur1; FETCH cur1 INTO tid,ttype_name,tptype_id; WHILE ( tid is not null ) DO insert into tmp_entp_type_info values(tid,ttype_name,tptype_id,lvl); -- 树形结构数据递归收集到建立的临时表中 call proEntpTypeInfo(tid,lvl+1); FETCH cur1 INTO tid,ttype_name,tptype_id ; END WHILE; END; drop procedure if exists proEntpTypeInfo; drop temporary table if exists tmp_entp_type_info; create temporary table if not exists tmp_entp_type_info(id int(20),type_name varchar(255), fid int(11),lvl int); call proEntpTypeInfo(7,0); select * from tmp_entp_type_info ; |
|
下面是一个简单的测试，一个dept表，1-1000个部门，和部门的别名；一个users表，200000个用户，随机属于1000个部门中的一个；假设users表中只有部门名称，没有部门名称别名，在users表中添加此字段`dept_alias`后根据dept表更新`dept_alias`的值：
| 代码如下 | 复制代码 |
|
//部门信息表 CREATE TABLE `dept` ( `name` char(255) CHARACTER SET utf8 NOT NULL DEFAULT NULL, `alias` char(255) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; //用户数据表 CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` char(255) CHARACTER SET utf8 DEFAULT NULL, `gender` enum('男','女') CHARACTER SET utf8 DEFAULT '男', `dept` char(255) CHARACTER SET utf8 DEFAULT NULL, `dept_alias` char(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_dept` (`dept`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8; //测试存储过程 DROP PROCEDURE IF EXISTS testProcedure; CREATE PROCEDURE testProcedure() BEGIN DECLARE flag INT DEFAULT 0; DECLARE tID INT; DECLARE tDept CHAR(255); DECLARE tAlias CHAR(20); DECLARE cur CURSOR FOR SELECT id,dept FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1; OPEN cur; FETCH cur INTO tID,tDept; WHILE flag<>1 DO SELECT alias FROM dept WHERE name = tDept INTO tAlias; UPDATE users SET dept_alias=tAlias WHERE id=tID; FETCH cur INTO tID,tDept; END WHILE; CLOSE cur; END |
|
首先，这个需要使用下面的一条SQL语句就可以实现。
| 代码如下 | 复制代码 |
|
-- 4.25 s UPDATE users AS u SET u.dept_alias=(SELECT alias FROM dept WHERE name=u.dept); |
|
不过，为了测试，先将users中的数据逐一读出，然后一一查询更新，使用存储过程和使用通常的查询做法分别如下所示：
| 代码如下 | 复制代码 |
|
//time: 17.667736053467 s //memory: 55128 bytes (不包含MySQL内存，仅供参考) mysql_connect('127.0.0.1','root','develop') OR die('Connect Failure'); mysql_select_db('test') OR die('SELECT DB Error!'); mysql_query('SET NAMES utf8;'); $t1 = getMicrotime(); mysql_query('CALL testProcedure();'); $t2 = getMicrotime(); var_dump( $t2-$t1,memory_get_usage() ); mysql_close(); function getMicrotime() { list( $usec, $sec ) = explode(" ", microtime()); return ((float)$usec + (float)$sec); } |
|