mysql批量更新数据

CREATE PROCEDURE `sp_update_temp_data`(
    out po_returnvalue varchar(12)
)
leave_top:BEGIN
  #Routine body goes here...
     declare l_id bigint;
     declare l_cnt int;
     declare code CHAR(5) DEFAULT '00000';
     declare msg text;
     declare done int;
     declare cur_data CURSOR for select id from tb_test;
     declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     DECLARE exit handler FOR SQLEXCEPTION
     BEGIN
                  GET DIAGNOSTICS CONDITION 1
                  code = RETURNED_SQLSTATE,@x2 = MESSAGE_TEXT;
      set po_returnvalue = 999;
     END;
    set po_returnvalue = -10;
    set l_cnt = 0;

     open cur_data;
     loop_label:
     loop
      begin
             FETCH cur_data INTO l_id;
             IF done THEN
                     LEAVE loop_label;
             end IF;
             update tb_test
                set name=CONCAT('updatetest',l_id)
              where id=l_id;
       if mod(l_cnt,1000) THEN
          commit;
          set l_cnt:=0;
       end if;
       end;
       end Loop;
    close cur_data;
    commit;
    set po_returnvalue = 0;

END

 

调用如下:

CALL sp_update_temp_data(@po_returnvalue);
select @po_returnvalue;