首页 / MYSQL / mysql海量数据条件删除
mysql海量数据条件删除
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql海量数据条件删除，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2940字，纯文字阅读大概需要5分钟。
内容图文
1. 问题描述：现在存在两个表，具体表结构及记录数如下所示：
mysql> desc user_mapping; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | open_id | varchar(64) | NO | PRI | NULL | | | platform | tinyint(4) | NO | PRI | NULL | | | serverid | int(10) unsigned | NO | PRI | 0 | | | uid | int(10) unsigned | NO | | NULL | | | updatetime | int(11) | YES | | NULL | | | lastlogin | int(11) | YES | | NULL | | | via | varchar(128) | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> select count(*) from user_mapping; +----------+ | count(*) | +----------+ | 12579610 | +----------+ 1 row in set (2.49 sec) mysql> desc uid1202; +-----------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+----------------------+------+-----+---------+-------+ | uid | int(10) unsigned | NO | | NULL | | | last_login_time | int(10) unsigned | YES | | NULL | | | accCharge | int(10) unsigned | YES | | 0 | | | level | smallint(5) unsigned | YES | | NULL | | +-----------------+----------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select count(*) from uid1202; +----------+ | count(*) | +----------+ | 9887299 | +----------+ 1 row in set (3.25 sec)
目的是将user_mapping表中的uid在uid1202表中存在的记录删除，mysql处理大数据时，多表连接会直接将服务器单核卡死，而且还不知道猴年马月才能处理完。
解决方案：
step1. 将user_mapping表和uid1202表dump到内网，建新库导入
step2. 去掉内网user_mapping表中的主键
alter
table user_mapping dropprimarykey;
step3. 删除内网user_mapping表中uid重复的值删除保留一条
mysql -uusername -ppassword -e"select platform,uid from user_mapping group by uid having count(*) > 1 ;" > uid_double.txt awk‘{print "delete from user_mapping where platform="$1" and uid="$2";"}‘ uid_double.txt > del_double.sql mysql -uusername -ppassword user_del < del_double.sql
step4. 修改user_mapping表，重新建立以uid为主键
alter
table user_mapping addprimarykey(uid);
step4. 构造查询user_mapping表中uid在uid1202表中的语句
mysql -uusername -ppassword user_del -e"select uid from uid1202" > uid.txt awk‘{print "select open_id,platform,serverid from user_mapping where uid="$1"; "}‘ uid.txt > del_uid.sql
step5. 构造删除user_mapping表中以open_id,platform,serverid为条件的语句
mysql -uroot -p1234 user_del < del_uid.sql > del_usermapping.txt
sed -i ‘/open_id/d‘ del_usermapping.txt #删除奇数行table头 awk‘{print "delete from user_mapping where open_id=\""$1"\" and platform="$2" and uid="$3" ;"}‘ del_usermapping.txt > del_usermapping.txt
step6. 分解查询语句到多个sql文件，在外网同时运行
#!/bin/bash for i in $(seq18) docat del_usermapping.sql | head -n 1300000 > del_usermapping_$i.sql sed -i ‘1,1300000d‘ del_usermapping.sql cat del_usermapping_$i.sql | wc -l donefor i in $(seq18) do mysql -uroot -p1234 user_del < del_usermapping_$i.sql & done
原文：http://www.cnblogs.com/forilen/p/4158381.html
内容总结
以上是互联网集市为您收集整理的mysql海量数据条件删除全部内容，希望文章能够帮你解决mysql海量数据条件删除所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。