BEGIN
declare _un varchar(255);
declare _pa varchar(255);
declare done int default 0;
declare cur cursor for select username,password from user;
declare continue handler for not found set done = 1;
open cur;
repeat
fetch cur into _un,_pa;
if done <> 1 then
update user set username=_pa,password=_un;
end if;
until done=1
end repeat;
close cur;
END
我目的是想实现一个表的两列数据交换,但是结果是交换了,但是都用的第一行的数据做的,也就是每次循环两个变量中存储的值都是第一列的?之前遍历过游标,做过另一表的插入操作,难道是我的fetch cur into _un,_pa;写错了吗?
declare _un varchar(255);
declare _pa varchar(255);
declare done int default 0;
declare cur cursor for select username,password from user;
declare continue handler for not found set done = 1;
open cur;
repeat
fetch cur into _un,_pa;
if done <> 1 then
update user set username=_pa,password=_un;
end if;
until done=1
end repeat;
close cur;
END
我目的是想实现一个表的两列数据交换,但是结果是交换了,但是都用的第一行的数据做的,也就是每次循环两个变量中存储的值都是第一列的?之前遍历过游标,做过另一表的插入操作,难道是我的fetch cur into _un,_pa;写错了吗?
a d
b e
c f
变成了
f c
f c
f c
BEGIN
declare _id int;
declare _un varchar(255);
declare _pa varchar(255);
declare done int default 0;
declare cur cursor for select id,username,password from user;
declare continue handler for not found set done = 1;
open cur;
repeat
fetch cur into _id,_un,_pa;
if done <> 1 then
update user set username=_pa,password=_un where id=_id;
end if;
until done=1
end repeat;
close cur;
END
我之前认为循环表里游标,他会自己做这步where操作,看来我错了。。
Database changed
mysql> select *from bbb;
+------+------+------+
| id | b | c |
+------+------+------+
| 1 | b1 | c1 |
| 2 | b2 | c2 |
| 3 | b3 | c3 |
+------+------+------+
3 rows in set (0.00 sec)mysql> drop procedure if exists sp_bc;
Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$
mysql> create procedure sp_bc()
-> begin
-> declare bb varchar(10);
-> declare cc varchar(10);
-> declare done int default 0;
-> declare cur_1 cursor for select b,c from bbb;
-> declare continue handler for not found set done=1;
-> create temporary table tempt(id int AUTO_INCREMENT primary key,b varchar(
10),c varchar(10));
-> open cur_1;
-> repeat
-> fetch cur_1 into bb,cc;
-> if done = 0 then
-> insert into tempt(b,c) values (cc,bb);
-> update bbb,tempt set bbb.b=tempt.b where bbb.id=tempt.id;
-> update bbb,tempt set bbb.c=tempt.c where bbb.id=tempt.id;
-> end if;
-> until done=1 end repeat;
-> drop TEMPORARY table tempt;
-> end $$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call sp_bc();
Query OK, 0 rows affected (0.31 sec)mysql> select * from bbb;
+------+------+------+
| id | b | c |
+------+------+------+
| 1 | c1 | b1 |
| 2 | c2 | b2 |
| 3 | c3 | b3 |
+------+------+------+
3 rows in set (0.00 sec)