删除sql转化为inner join格式 发现这个sql很慢delete from a wherea.id not in (select outId from )因为2个都是大表,感觉可以转化为inner join格式,如何转? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 delete a from a inner join b on a.id=b.idMySQL 官方文档中就有这个例子 !MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html delete a from a inner join b on a.id=b.outId not in 不是相等的条件啦 delete a from a left join b on a.id=b.id where b.id is null 如何理解delete a from a left join b on a.id=b.id where b.id is null这个还是清楚明白delete from a where a.id not in (select outId from ) delete a from a left join b on a.id=b.outid where a.id is null mysql> select * from tb;+------+------+| id | rid |+------+------+| 1 | 78 || 2 | 64 || 2 | 78 || 3 | 78 || 4 | 78 || 4 | 64 || 4 | 56 |+------+------+7 rows in set (0.00 sec)mysql> select * from user;+--------+----------+| userid | username |+--------+----------+| 1 | g?? || 2 | ??? || 3 | ??? || 4 | || 5 | |+--------+----------+5 rows in set (0.01 sec)mysql> select user.* from user left join tb on id=userid where id is not null;+--------+----------+| userid | username |+--------+----------+| 1 | g?? || 2 | ??? || 2 | ??? || 3 | ??? || 4 | || 4 | || 4 | |+--------+----------+7 rows in set (0.02 sec)mysql> delete user from user left join tb on id=userid where id is null;Query OK, 1 row affected (0.06 sec)mysql> select * from user;+--------+----------+| userid | username |+--------+----------+| 1 | g?? || 2 | ??? || 3 | ??? || 4 | |+--------+----------+4 rows in set (0.00 sec) delete a from a left join b on a.id=b.outid where b.outid is null 用a表的id字段与b表的outid字段进行左联接,你的not in即左联接后b.outid为null的那些记录即是。 delete from a where a.id>1000 and a.id not in (select outId from where a.id>1000)这个又如何转换为left join 楼主基本上是一点脑筋都不想动啊。你的项目全是靠这么做啊。delete a from a left join b on a.id=b.id where b.id is null and a.id>1000 不是的 自己写了需要核对的 ------------感谢楼上提醒,以后自己搭建环境多实践确实需要indepentdent 楼上方法 最终放弃因为复制环境下 删除不是那么简单我使用临时表来解决问题 (另外实际的sql是4个表这样写不直观 最终放弃) 周末加班耗费大量时间, 发现delete left join不能使用delete a from a left join b on a.id=b.id 。原因是 a 主表在不停的增加新数据。最后解决如下:1临时表2delete from a where a.id not in (select outId from ) 有时候好的技术不一定管用,高效实用才是最关键(有时候效率可以低,但一定有用)。 求 mysql 的学习方法 Group by 后如何保持原先的排序? 一个sql,insert语句中如果有中文就会出错,怎么回事儿呢? 【三个表相交】执行了这个SQL语句之后,机子垮了,服务器也垮了 mysql 数据的异常问题 更换用户名密码登陆失败问题。 创建中文表的问题 请问为什么我从MySql中取出的数据与实际的数据不对呢? 数据导入批处理问题 下载的MySQL源码怎么用 指定的数据库不能出现 类似与这样的表 如果 要修改 field里面的字段名字 改如何修改?
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
不是相等的条件啦
delete a from a left join b on a.id=b.id where b.id is null
delete a from a left join b on a.id=b.id where b.id is null这个还是清楚明白
delete from a where
a.id not in (select outId from )
+------+------+
| id | rid |
+------+------+
| 1 | 78 |
| 2 | 64 |
| 2 | 78 |
| 3 | 78 |
| 4 | 78 |
| 4 | 64 |
| 4 | 56 |
+------+------+
7 rows in set (0.00 sec)mysql> select * from user;
+--------+----------+
| userid | username |
+--------+----------+
| 1 | g?? |
| 2 | ??? |
| 3 | ??? |
| 4 | |
| 5 | |
+--------+----------+
5 rows in set (0.01 sec)
mysql> select user.* from user left join tb on id=userid where id is not null;
+--------+----------+
| userid | username |
+--------+----------+
| 1 | g?? |
| 2 | ??? |
| 2 | ??? |
| 3 | ??? |
| 4 | |
| 4 | |
| 4 | |
+--------+----------+
7 rows in set (0.02 sec)
mysql> delete user from user left join tb on id=userid where id is null;
Query OK, 1 row affected (0.06 sec)mysql> select * from user;
+--------+----------+
| userid | username |
+--------+----------+
| 1 | g?? |
| 2 | ??? |
| 3 | ??? |
| 4 | |
+--------+----------+
4 rows in set (0.00 sec)
a.id>1000 and a.id not in (select outId from where a.id>1000)
这个又如何转换为left join
需要核对的
------------感谢楼上提醒,以后自己搭建环境多实践
确实需要indepentdent
因为复制环境下 删除不是那么简单
我使用临时表来解决问题 (另外实际的sql是4个表
这样写不直观 最终放弃)
发现delete left join不能使用
delete a from a left join b on a.id=b.id 。
原因是 a 主表在不停的增加新数据。最后解决如下:
1临时表
2delete from a where
a.id not in (select outId from ) 有时候好的技术不一定管用,高效实用才是最关键(有时候效率可以低,但一定有用)。