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;写错了吗?
解决方案 »
- 统计出在N分钟(10、20、30分钟等变量)内,访问次数超过5次的记录
- mysql installer是mysql的安装文件么
- 请教这样的文本能不能导入数据库???
- MYSQL 中的单引号和双引号有区别没?
- 请教个怪异的mysql乱码的问题
- 关于C API连接数据库的问题,代码出错了,我改了1个星期没改出来,大家帮看看
- ???请教:如何用php在网页表中显示mysql表中的数据?
- 100分求jdbc连接字符串问题,MYSQL的,位于虚拟空间提供商那边,在JSP页里写代码连接
- 连接Mysql数据库的时候有问题
- 那里有人比较多的mysql的论坛啊??
- MySql中还原、备份、复制的问题
- MySQL优化问题
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)