80万条数据和80个表要更新如何优化
mysql的数据库, 需要更新以下80个表的uid这个字段,用了一个十分笨的方法来进行存储过程更新,发现太慢了
原来把旧表倒过来的时候id没有同步过来,现在想把id和旧库统一一下,一开始用程序循环更新发现1秒才能更新2-3个id,80w的数据量要更新好几天,改为存储过程以后发现也没有快多少,求高手来个方法或者思路,如何提高效率
因为这个库已经使用了,不要说从新导入这样的方法 谢谢~~~~
DELIMITER $$;
DROP PROCEDURE IF EXISTS `ultrax`.`test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
mysqluid char(8),--更改前的uid
sqlid char(8)--更改后的uid
)
BEGIN
update ucenter.uc_members set uid=sqlid where uid=mysqluid; /*将用户表先更新如果有id被占用将会报错停止更新*/
update ucenter.uc_admins set uid=sqlid where uid=mysqluid;
update ucenter.uc_friends set uid=sqlid where uid=mysqluid;
update ucenter.uc_memberfields set uid=sqlid where uid=mysqluid;
update ucenter.uc_newpm set uid=sqlid where uid=mysqluid;
update ucenter.uc_pm_members set uid=sqlid where uid=mysqluid;
update ucenter.uc_protectedmembers set uid=sqlid where uid=mysqluid;
update pre_common_admincp_cmenu set uid=sqlid where uid=mysqluid;
update pre_common_admincp_session set uid=sqlid where uid=mysqluid;
update pre_common_block set uid=sqlid where uid=mysqluid;
update pre_common_block_item_data set uid=sqlid where uid=mysqluid;
update pre_common_credit_log set uid=sqlid where uid=mysqluid;
update pre_common_credit_rule_log set uid=sqlid where uid=mysqluid;
update pre_common_credit_rule_log_field set uid=sqlid where uid=mysqluid;
update pre_common_diy_data set uid=sqlid where uid=mysqluid;
update pre_common_invite set uid=sqlid where uid=mysqluid;
update pre_common_magiclog set uid=sqlid where uid=mysqluid;
update pre_common_member set uid=sqlid where uid=mysqluid;
update pre_common_member_action_log set uid=sqlid where uid=mysqluid;
update pre_common_member_count set uid=sqlid where uid=mysqluid;
update pre_common_member_field_forum set uid=sqlid where uid=mysqluid;
update pre_common_member_field_home set uid=sqlid where uid=mysqluid;
update pre_common_member_magic set uid=sqlid where uid=mysqluid;
update pre_common_member_profile set uid=sqlid where uid=mysqluid;
update pre_common_member_status set uid=sqlid where uid=mysqluid;
update pre_common_onlinetime set uid=sqlid where uid=mysqluid;
update pre_common_statuser set uid=sqlid where uid=mysqluid;
update pre_forum_attachment set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_1 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_2 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_3 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_4 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_5 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_6 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_7 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_8 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_9 set uid=sqlid where uid=mysqluid;
update pre_forum_debate set uid=sqlid where uid=mysqluid;
update pre_forum_groupcreditslog set uid=sqlid where uid=mysqluid;
update pre_forum_groupuser set uid=sqlid where uid=mysqluid;
update pre_forum_modwork set uid=sqlid where uid=mysqluid;
update pre_forum_pollvoter set uid=sqlid where uid=mysqluid;
update pre_forum_spacecache set uid=sqlid where uid=mysqluid;
update pre_forum_threadmod set uid=sqlid where uid=mysqluid;
update pre_forum_threadpartake set uid=sqlid where uid=mysqluid;
update pre_home_album set uid=sqlid where uid=mysqluid;
update pre_home_blog set uid=sqlid where uid=mysqluid;
update pre_home_blogfield set uid=sqlid where uid=mysqluid;
update pre_home_class set uid=sqlid where uid=mysqluid;
update pre_home_clickuser set uid=sqlid where uid=mysqluid;
update pre_home_comment set uid=sqlid where uid=mysqluid;
update pre_home_docomment set uid=sqlid where uid=mysqluid;
update pre_home_doing set uid=sqlid where uid=mysqluid;
update pre_home_favorite set uid=sqlid where uid=mysqluid;
update pre_home_feed set uid=sqlid where uid=mysqluid;
update pre_home_friend set uid=sqlid where uid=mysqluid;
update pre_home_friendlog set uid=sqlid where uid=mysqluid;
update pre_home_friend_request set uid=sqlid where uid=mysqluid;
update pre_home_notification set uid=sqlid where uid=mysqluid;
update pre_home_pic set uid=sqlid where uid=mysqluid;
update pre_home_poke set uid=sqlid where uid=mysqluid;
update pre_home_share set uid=sqlid where uid=mysqluid;
update pre_home_show set uid=sqlid where uid=mysqluid;
update pre_home_visitor set uid=sqlid where uid=mysqluid;
update pre_common_magiclog set targetuid =sqlid where targetuid =mysqluid;
update pre_common_invite set fuid =sqlid where fuid =mysqluid;
update pre_forum_debate set affirmvoterids =sqlid where affirmvoterids =mysqluid;
update pre_forum_debate set negavoterids =sqlid where negavoterids =mysqluid;
update pre_forum_forumfield set founderuid =sqlid where founderuid =mysqluid;
update pre_forum_forumrecommend set authorid =sqlid where authorid =mysqluid;
update pre_common_plugin set adminid =sqlid where adminid =mysqluid;
update pre_forum_polloption set voterids =sqlid where voterids =mysqluid;
update pre_forum_post set authorid =sqlid where authorid =mysqluid;
update pre_forum_thread set authorid =sqlid where authorid =mysqluid;
update pre_home_album set target_ids=sqlid where target_ids=mysqluid;
update pre_home_blogfield set target_ids=sqlid where target_ids=mysqluid;
update pre_home_comment set authorid=sqlid where authorid=mysqluid;
update pre_home_comment set id=sqlid where id=mysqluid;
update pre_home_favorite set spaceuid=sqlid where spaceuid=mysqluid;
update pre_home_feed set target_ids=sqlid where target_ids=mysqluid;
update pre_home_friend set fuid =sqlid where fuid =mysqluid;
update pre_home_friend_request set fuid=sqlid where fuid=mysqluid;
update pre_home_friendlog set fuid =sqlid where fuid =mysqluid;
update pre_home_notification set authorid =sqlid where authorid =mysqluid;
update pre_home_poke set fromuid =sqlid where fromuid =mysqluid;
update pre_home_share set fromuid=sqlid where fromuid=mysqluid;
update pre_home_visitor set vuid =sqlid where vuid =mysqluid;
update ucenter.uc_friends set FRIENDID =sqlid where FRIENDID =mysqluid;
END$$
DELIMITER ;$$