更改用户表的主键id

DROP procedure IF EXISTS MODIFY_USER_ID;

delimiter //
create procedure MODIFY_USER_ID() ## 创建存储过程
begin 
declare d_login_id varchar(50) default 'system_operator';
declare to_id varchar(50) default 'system_operator_id';
declare acct_user_role_id varchar(50);
declare done int default false;
declare o_acct_user_id VARCHAR(32);

DECLARE d_wallet_id varchar(32);

DECLARE user_role_cursor CURSOR FOR select aur.id from acct_user_role as aur where aur.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);

DECLARE acct_coupon_cursor CURSOR FOR select ac.id from acct_coupon as ac where ac.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);

DECLARE acct_logistics_coupon_cursor CURSOR FOR select alcc.id from acct_logistics_coupon as alcc where alcc.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);  

DECLARE acct_coupon_batch_cursor CURSOR FOR select acb.id from acct_coupon_batch as acb where acb.user_id=(select au.id from acct_user as au where au.login_id=d_login_id);  

declare continue HANDLER for not found set done = true;
start TRANSACTION;
open user_role_cursor;
open acct_coupon_cursor;
open acct_logistics_coupon_cursor;
open acct_coupon_batch_cursor;

select id into o_acct_user_id from acct_user au where au.login_id=d_login_id;

-- 保存wallet的id
  select ww.id into d_wallet_id from  wall_wallet  as ww where ww.`user`=(select au.id from acct_user as au where au.login_id=d_login_id);

-- 将关联的userID全部都置空
  update acct_user_role set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
  update acct_coupon set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
    update acct_logistics_coupon set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);
  update acct_coupon_batch set user_id=null where user_id = (select id from acct_user where login_id=d_login_id);

  update wall_wallet set `user`=null where `user`= (select id from acct_user where login_id=d_login_id);

-- 开始更新

update acct_user set id=to_id where login_id=d_login_id;

select d_wallet_id;

update wall_wallet set `user`=to_id where id=d_wallet_id;

    read_loop:loop

            fetch user_role_cursor into acct_user_role_id;
            if done then
                leave read_loop;
            end if;

                update acct_user_role set user_id=to_id where id=acct_user_role_id;

    end loop;

set done = false;
    read_loop:loop

            fetch acct_coupon_cursor into acct_user_role_id;
            if done then
                leave read_loop;
            end if;

                update acct_coupon set user_id=to_id where id=acct_user_role_id;

    end loop;

set done = false;
    read_loop:loop

            fetch acct_logistics_coupon_cursor into acct_user_role_id;
            if done then
                leave read_loop;
            end if;

                update acct_logistics_coupon set user_id=to_id where id=acct_user_role_id;

    end loop;

set done = false;
    read_loop:loop

            fetch acct_coupon_batch_cursor into acct_user_role_id;
            if done then
                leave read_loop;
            end if;

                update acct_coupon_batch set user_id=to_id where id=acct_user_role_id;

    end loop;

CLOSE user_role_cursor;
CLOSE acct_coupon_cursor;
CLOSE acct_logistics_coupon_cursor;
CLOSE acct_coupon_batch_cursor;

  select * from acct_user_role where user_id = (select id from acct_user where login_id=d_login_id);

    select * from wall_wallet where `user`= (select id from acct_user where login_id=d_login_id);

-- 最后更新customer

update balidrop_product set customer_id=to_id where customer_id= ( select id from cust_customer where user_id = o_acct_user_id);
update balidrop_prdouct_category set customer_id=to_id ,customer_name=to_id where customer_id= ( select id from cust_customer where user_id = o_acct_user_id);
update cust_customer set id=to_id, user_id=to_id where user_id = o_acct_user_id;

COMMIT;

end;//

delimiter;

call MODIFY_USER_ID();