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 ;$$
原来把旧表倒过来的时候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 ;$$
解决方案 »
- 求解???创建表时带括号的字段如何创建
- mysql是否支持intersect和except集合查询
- 100分悬赏一段mysql语句问题到底出在哪里了?希望有人帮忙,先谢过了!
- 问一个普通的sql,新手望好人回答。
- mysql 创建表出错。奇怪的很~
- MYSQL中怎样更改AUTO_INCEMENT属性列的值(娃娃节结贴)
- 我在vc中调用mysql的c函数,怎么总出现莫名其妙的错我
- 请问mysql难吗?
- 视图中计算double类型数据得到错误的结果,该怎么办?
- mysql怎么从串口读取数据?
- 我的mysql版本是5.1.57.log,为什么没有innodb_status_file这个参数
- 高手热心人来看看
我都加上了 但是提升很有限
比如我的库里面50w以前基本连续,id为50w-60w的数据是空的,id为70-80是不连续的,id为80-100w是空的 id为100w以后是连续的 新旧id没有任何关系 完全是2个库的id 现在是要2个库的id统一 以后还需要有后期的开发需要2边的id相同才能互相调用 所以才要进行这恶心的操作