发现这个sql很慢
delete from a where
a.id not in (select outId from )
因为2个都是大表,感觉可以转化为inner join格式,如何转?
delete from a where
a.id not in (select outId from )
因为2个都是大表,感觉可以转化为inner join格式,如何转?
解决方案 »
- mysql 数据库 #sql-开头的是什么表,可以删除吗?
- 用ADO连接的MySQL,客户端自己怎么知道用的是什么IP连接的服务器
- Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
- mysql 数字0问题
- Mysql 按照字段长度排序,该怎么写啊~在线等待~
- 创建存储过程时“CONTAINS SQL”选项的问题
- 请各位大虾指教MySQL Manager 与 MySQL 5.0连接不上
- 涉猎 MySQL 和 C# 的兄弟姐妹们来看看
- mysql 安装不能启动
- mysql 子查询sql怎么写
- 指定的数据库不能出现
- 类似与这样的表 如果 要修改 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 ) 有时候好的技术不一定管用,高效实用才是最关键(有时候效率可以低,但一定有用)。