[{"createTime":1735734952000,"id":1,"img":"hwy_ms_500_252.jpeg","link":"https://activity.huaweicloud.com/cps.html?fromacct=261f35b6-af54-4511-a2ca-910fa15905d1&utm_source=V1g3MDY4NTY=&utm_medium=cps&utm_campaign=201905","name":"华为云秒杀","status":9,"txt":"华为云38元秒杀","type":1,"updateTime":1735747411000,"userId":3},{"createTime":1736173885000,"id":2,"img":"txy_480_300.png","link":"https://cloud.tencent.com/act/cps/redirect?redirect=1077&cps_key=edb15096bfff75effaaa8c8bb66138bd&from=console","name":"腾讯云秒杀","status":9,"txt":"腾讯云限量秒杀","type":1,"updateTime":1736173885000,"userId":3},{"createTime":1736177492000,"id":3,"img":"aly_251_140.png","link":"https://www.aliyun.com/minisite/goods?userCode=pwp8kmv3","memo":"","name":"阿里云","status":9,"txt":"阿里云2折起","type":1,"updateTime":1736177492000,"userId":3},{"createTime":1735660800000,"id":4,"img":"vultr_560_300.png","link":"https://www.vultr.com/?ref=9603742-8H","name":"Vultr","status":9,"txt":"Vultr送$100","type":1,"updateTime":1735660800000,"userId":3},{"createTime":1735660800000,"id":5,"img":"jdy_663_320.jpg","link":"https://3.cn/2ay1-e5t","name":"京东云","status":9,"txt":"京东云特惠专区","type":1,"updateTime":1735660800000,"userId":3},{"createTime":1735660800000,"id":6,"img":"new_ads.png","link":"https://www.iodraw.com/ads","name":"发布广告","status":9,"txt":"发布广告","type":1,"updateTime":1735660800000,"userId":3},{"createTime":1735660800000,"id":7,"img":"yun_910_50.png","link":"https://activity.huaweicloud.com/discount_area_v5/index.html?fromacct=261f35b6-af54-4511-a2ca-910fa15905d1&utm_source=aXhpYW95YW5nOA===&utm_medium=cps&utm_campaign=201905","name":"底部","status":9,"txt":"高性能云服务器2折起","type":2,"updateTime":1735660800000,"userId":3}]
1.like与 in批量删除指定记录
like语法
like主要是用来模糊查询了
sql = "delete from A where field like '%ABC%'" 这个可用于字符与数字
in 语法
sql = "delete from A where field id(1,2,3)" 这个只能用在数字
关联删除
delete B from B
where exists (select 1 from A where B.accountid=A.accountid);
上面两条方法如果删除100W级别的数据库估计是没什么问题，如果是1000W估计就不行了
假设有一个表(syslogs)有1000万条记录，需要在业务不停止的情况下删除其中statusid=1的所有记录，差不多有600万条， 直接执行
DELETE FROM syslogs WHERE statusid=1
会发现删除失败，因为lock wait timeout exceed的错误。
因为这条语句所涉及的记录数太多，因此我们通过LIMIT参数分批删除，比如每10000条进行一次删除，那么我们可以利用 MySQL这样的语句来完成
代码如下
DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;
然后分多次执行就可以把这些记录成功删除。
另一种 删除方向。
上面通过比较觉的需要删除的量不是太大时，把需要删除的PID生到到另一个临时表中。
代码如下
mysql DBname -e "select a.pid from table1 a ,table2 b where
a.pid=b.pid">del_pid.txt;
sed -i '1d' del_pid.txt
awk '{print "delete from table1 where pid=",$1,";"}' del_pid.txt >del_pid.sql
mysql DBname
这样把SQL拆成多个SQL执行速度应该不会太慢了。
