mysql批量更新存储过程怎么写

问答首页 → Java企业应用

0 0

mysql批量更新存储过程怎么写0

我要写个存储过程，传入记录数N。
然后查找出按积分排序的前N条记录，然后循环设置记录的排名=i.
请问能给个demo么。

MySQL

2014年5月27日 15:37

jkluoooposm
3
0 0 12

采纳的答案

create procedure batchUpdate(in n int)
begin
declare inParam int default n;
declare i int default 1;
declare id int;
declare username varchar(255);
declare integral varchar(255);
declare ranking int;
declare _resultSet cursor for select a.id, a.username,a.integral,a.ranking from user a order by a.integral desc limit inParam;
open _resultSet;
while i <= inParam do
fetch _resultSet into id, username, integral,ranking;
update user a set a.ranking = i where a.id = id;
set i = i +1;
end while;
close _resultSet;
end;

mysql> select * from user;
+----+----------+----------+---------+
| id | username | integral | ranking |
+----+----------+----------+---------+
|  1 | abc      |        1 |       0 |
|  2 | abc      |        2 |       0 |
|  3 | abc      |        3 |       0 |
|  4 | abc      |        4 |       0 |
|  5 | abc      |        5 |       0 |
|  6 | abc      |        6 |       0 |
|  7 | abc      |        7 |       0 |
|  8 | abc      |        8 |       0 |
|  9 | abc      |        9 |       0 |
| 10 | abc      |       10 |       0 |
| 11 | abc      |       11 |       0 |
| 12 | abc      |       12 |       0 |
| 13 | abc      |       13 |       0 |
| 14 | abc      |       14 |       0 |
+----+----------+----------+---------+
14 rows in set

mysql> call batchUpdate(14);
Query OK, 1 row affected

mysql> select * from user;
+----+----------+----------+---------+
| id | username | integral | ranking |
+----+----------+----------+---------+
|  1 | abc      |        1 |      14 |
|  2 | abc      |        2 |      13 |
|  3 | abc      |        3 |      12 |
|  4 | abc      |        4 |      11 |
|  5 | abc      |        5 |      10 |
|  6 | abc      |        6 |       9 |
|  7 | abc      |        7 |       8 |
|  8 | abc      |        8 |       7 |
|  9 | abc      |        9 |       6 |
| 10 | abc      |       10 |       5 |
| 11 | abc      |       11 |       4 |
| 12 | abc      |       12 |       3 |
| 13 | abc      |       13 |       2 |
| 14 | abc      |       14 |       1 |
+----+----------+----------+---------+
14 rows in set

2014年5月27日 17:42

十三哥
95
0 0 4

添加评论

0 0

drop PROCEDURE  if exists dorank;
delimiter //
CREATE PROCEDURE dorank(IN N INT)
BEGIN
	
  SET @i=0;
  PREPARE s1 FROM '
	  update tiezi as a
	  inner join (
		select @i := @i +1 as myrank, id, zan from tiezi order by zan desc limit ?
		) as b
	  on a.id=b.id
	  set a.rank = b.myrank
  ';
  
  set @a=N;
  EXECUTE s1 USING @a;
DEALLOCATE PREPARE s1;

END;
//
delimiter ;


mysql> select * from tiezi;
+----+------+------+
| id | zan  | rank |
+----+------+------+
|  1 |  100 |    0 |
|  2 |  300 |    0 |
|  3 |   50 |    0 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> call dorank(2);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tiezi;
+----+------+------+
| id | zan  | rank |
+----+------+------+
|  1 |  100 |    2 |
|  2 |  300 |    1 |
|  3 |   50 |    0 |
+----+------+------+
3 rows in set (0.00 sec)

2014年5月27日 17:33

bylijinnan
42
0 1 9

1条评论

0 0

可以利用自定义变量（MySQL）

2014年5月27日 16:53

bylijinnan
42
0 1 9

添加评论

0 0

drop PROCEDURE if EXISTS dealData;

CREATE PROCEDURE dealData()
BEGIN

DECLARE totalCount INT(10);
        DECLARE i INT(10);
set totalCount =0; //记录数
        set i=1;//排名
SELECT count(1) into totalCount from 表 ;

while(totalCount>0) DO
update 表 set 列=i where...;
set totalCount = totalCount-1;
                set i = i +1;
end WHILE;
END;
CALL dealData();