mysql存储过程 in 怎么用

out 表示输出的参数，存储过程调用 代码 需要获得此参数值。

in 表示输入参数，默认为in

例1、一个简单存储过程游标实例

复制代码代码如下:

DELIMITER $$

DROP PROCEDURE IF EXISTS getUserInfo $$

CREATE PROCEDURE getUserInfo(in date_day datetime)

--

-- 实例

-- 存储过程名为：getUserInfo

-- 参数为：date_day日期格式:2008-03-08

--

BEGIN

declare _userName varchar(12)-- 用户名

declare _chinese int -- 语文

declare _math int-- 数学

declare done int

-- 定义游标

DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1

-- 获取昨天的日期

if date_day is null then

set date_day = date_add(now(),interval -1 day)

end if

open rs_cursor

cursor_loop:loop

FETCH rs_cursor into _userName, _chinese, _math-- 取数据

if done=1 then

leave cursor_loop

end if

-- 更新表

update infoSum set total=_chinese+_math where UserName=_userName

end loop cursor_loop

close rs_cursor

END$$

DELIMITER

例2、存储过程游标循环跳出现

在MySQL的存储过程中,游标 *** 作时,需要执行一个conitnue的 *** 作.众所周知,MySQL中的游标循环 *** 作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.

1.REPEAT

复制代码代码如下:

REPEAT

Statements

UNTIL expression

END REPEAT

demo

DECLARE num INT

DECLARE my_string VARCHAR(255)

REPEAT

SET my_string =CONCAT(my_string,num,',')

SET num = num +1

UNTIL num <5

END REPEAT

2.WHILE

复制代码代码如下:

WHILE expression DO

Statements

END WHILE

demo

DECLARE num INT

DECLARE my_string VARCHAR(255)

SET num =1

SET str =''

WHILE num <span>10DO

SET my_string =CONCAT(my_string,num,',')

SET num = num +1

END WHILE

3.LOOP(这里面有非常重要的ITERATE,LEAVE)

代码如下 复制代码

DECLARE num INT

DECLARE str VARCHAR(255)

SET num =1

SET my_string =''

loop_label: LOOP

IF num <10THEN

LEAVE loop_label

ENDIF

SET num = num +1

IF(num mod3)THEN

ITERATE loop_label

ELSE

SET my_string =CONCAT(my_string,num,',')

ENDIF

END LOOP

PS:可以这样理解ITERATE就是我们程序中常用的contiune,而ITERATE就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.

例3,mysql 存储过程中使用多游标

先创建一张表，插入一些测试数据：

复制代码代码如下:

DROP TABLE IF EXISTS netingcn_proc_test

CREATE TABLE `netingcn_proc_test` (

`id` INTEGER(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20),

`password` VARCHAR(20),

PRIMARY KEY (`id`)

)ENGINE=InnoDB

insert into netingcn_proc_test(name, password) values

('procedure1', 'pass1'),

('procedure2', 'pass2'),

('procedure3', 'pass3'),

('procedure4', 'pass4')下面就是一个简单存储过程的例子：

drop procedure IF EXISTS test_proc

delimiter //

create procedure test_proc()

begin

-- 声明一个标志done， 用来判断游标是否遍历完成

DECLARE done INT DEFAULT 0

-- 声明一个变量，用来存放从游标中提取的数据

-- 特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是NULL

DECLARE tname varchar(50) DEFAULT NULL

DECLARE tpass varchar(50) DEFAULT NULL

-- 声明游标对应的 SQL 语句

DECLARE cur CURSOR FOR

select name, password from netingcn_proc_test

-- 在游标循环到最后会将 done 设置为 1

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

-- 执行查询

open cur

-- 遍历游标每一行

REPEAT

-- 把一行的信息存放在对应的变量中

FETCH cur INTO tname, tpass

if not done then

-- 这里就可以使用 tname， tpass 对应的信息了

select tname, tpass

end if

UNTIL done END REPEAT

CLOSE cur

end

//

delimiter

-- 执行存储过程

call test_proc()

需要注意的是变量的声明、游标的声明和HANDLER声明的顺序不能搞错，必须是先声明变量，再申明游标，最后声明HANDLER。上述存储过程的例子中只使用了一个游标，那么如果要使用两个或者更多游标怎么办，其实很简单，可以这么说，一个怎么用两个就是怎么用的。例子如下：

复制代码代码如下:

drop procedure IF EXISTS test_proc_1

delimiter //

create procedure test_proc_1()

begin

DECLARE done INT DEFAULT 0

DECLARE tid int(11) DEFAULT 0

DECLARE tname varchar(50) DEFAULT NULL

DECLARE tpass varchar(50) DEFAULT NULL

DECLARE cur_1 CURSOR FOR

select name, password from netingcn_proc_test

DECLARE cur_2 CURSOR FOR

select id, name from netingcn_proc_test

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

open cur_1

REPEAT

FETCH cur_1 INTO tname, tpass

if not done then

select tname, tpass

end if

UNTIL done END REPEAT

CLOSE cur_1

-- 注意这里，一定要重置done的值为 0

set done = 0

open cur_2

REPEAT

FETCH cur_2 INTO tid, tname

if not done then

select tid, tname

end if

UNTIL done END REPEAT

CLOSE cur_2

end

//

delimiter

call test_proc_1()

上述代码和第一个例子中基本一样，就是多了一个游标声明和遍历游标。这里需要注意的是，在遍历第二个游标前使用了set done = 0，因为当第一个游标遍历玩后其值被handler设置为1了，如果不用set把它设置为 0 ，那么第二个游标就不会遍历了。当然好习惯是在每个打开游标的 *** 作前都用该语句，确保游标能真正遍历。当然还可以使用begin语句块嵌套的方式来处理多个游标,例如：

复制代码代码如下:

drop procedure IF EXISTS test_proc_2

delimiter //

create procedure test_proc_2()

begin

DECLARE done INT DEFAULT 0

DECLARE tname varchar(50) DEFAULT NULL

DECLARE tpass varchar(50) DEFAULT NULL

DECLARE cur_1 CURSOR FOR

select name, password from netingcn_proc_test

DECLARE cur_2 CURSOR FOR

select id, name from netingcn_proc_test

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

open cur_1

REPEAT

FETCH cur_1 INTO tname, tpass

if not done then

select tname, tpass

end if

UNTIL done END REPEAT

CLOSE cur_1

begin

DECLARE done INT DEFAULT 0

DECLARE tid int(11) DEFAULT 0

DECLARE tname varchar(50) DEFAULT NULL

DECLARE cur_2 CURSOR FOR

select id, name from netingcn_proc_test

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

open cur_2

REPEAT

FETCH cur_2 INTO tid, tname

if not done then

select tid, tname

end if

UNTIL done END REPEAT

CLOSE cur_2

end

end

//

delimiter

call test_proc_2()

MySQL中使用IN会不会走索引

文章很短，先看下结论，在看下文。

结论：IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描

navicat可视化工具使用explain函数查看sql执行信息

场景1：当IN中的取值只有一个主键时

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果值从好到坏依次是：

system >const >eq_ref >ref >fulltext >ref_or_null >index_merge >unique_subquery >index_subquery >range >index >ALL

all:全表扫描

index:另一种形式的全表扫描，只不过他的扫描方式是按照索引的顺序

range：有范围的索引扫描，相对于index的全表扫描，他有范围限制，因此要优于index

ref: 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

const:通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器

一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。

当extra出现Using filesor或Using temproary时，表示无法使用索引，必须尽快做优化。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

这里可以参考之前写的一篇：用MySQL 执行计划分析 DATE_FORMAT 函数对索引的影响

场景2：扩大IN中的取值范围

此时仍然走了索引，但是效率降低了

场景3：继续扩大IN的取值范围

看上面的图，发现此时已经没有走索引了，而是全表扫描。

在说一下结论

结论：IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。

By the way：如果使用了 not in，则不走索引。

网上有很多人都在谈论or与in的使用，有的说二者没有什么区别，其实不然，估计是测试做的不够，其实or的效率为O(n)，而in的效率为O(log2n)，当基数越大时，in的效率就能凸显出来了。

有人做了这么一组实验(测试库数据为1000万条记录)：A组分别用or与in查询3条记录，B组分别用or与in查询120条记录,C组分别用or与in查询500条记录,D组分别用or与in查询1000条记录.

第一种情况，目标列为主键的情况，4组测试执行计划一样，执行的时间也基本没有区别。

A组or和in的执行时间： or的执行时间为：0.002s in的执行时间为：0.002s

B组or和in的执行时间： or的执行时间为：0.004s in的执行时间为：0.004s

C组or和in的执行时间： or的执行时间为：0.006s in的执行时间为：0.005s

D组or和in的执行时间： or的执行时间为：0.017s in的执行时间为：0.014s

第二种情况，目标列为一般索引的情况，4组测试执行计划一样，执行的时间也基本没有区别。

A组or和in的执行时间： or的执行时间为：0.002s in的执行时间为：0.002s

B组or和in的执行时间： or的执行时间为：0.006s in的执行时间为：0.005s

C组or和in的执行时间： or的执行时间为：0.008s in的执行时间为：0.008s

D组or和in的执行时间： or的执行时间为：0.020s in的执行时间为：0.019s

第三种情况，目标列没有索引的情况，4组测试执行计划就不一样，执行的时间也有了很大的区别。

A组or和in的执行时间： or的执行时间为：5.016s in的执行时间为：5.071s

B组or和in的执行时间： or的执行时间为：1min 02s in的执行时间为：5.018s

C组or和in的执行时间： or的执行时间为：1min 50s in的执行时间为：5.010s

D组or和in的执行时间： or的执行时间为：6min 13s in的执行时间为：5.047s

结论：

in和or的效率，取决目标条件列是否有索引或者是否是主键，如果有索引或者主键性能没啥差别，如果没有索引，in的性能要远远优于or.

---