mysql的left jion改写not in或not exists

来源：互联网发布：古永锵知乎编辑：程序博客网时间：2024/05/22 05:22

需求：查询数据库gip_demo比数据库epmp多的表和字段：

1.优先考虑使用not exist：

SELECTa.table_name,a.column_nameFROMinformation_schema.COLUMNS aWHEREa.TABLE_NAME REGEXP '^s_'AND a.TABLE_SCHEMA = 'gip_demo'and NOT EXISTS (SELECT1FROMinformation_schema.COLUMNS bWHERETABLE_NAME REGEXP '^s_'AND TABLE_SCHEMA = 'epmp'AND a.column_name = b.column_nameAND a.table_name = b.table_name);

查询时间：54.67s

2.使用not in:

select a.table_name,a.column_name from (SELECTi.table_name,i.column_nameFROMinformation_schema.COLUMNS iWHEREi.TABLE_NAME REGEXP '^s_' AND i.TABLE_SCHEMA = 'gip_demo') a where (a.table_name,a.column_name)not in(SELECTb.table_name,b.column_nameFROMinformation_schema.COLUMNS bWHERETABLE_NAME REGEXP '^s_'AND TABLE_SCHEMA = 'epmp');

执行时间：40.341s

3.使用left join:

SELECT     a.table_name na,a.column_nameFROM(SELECTa.table_name,a.column_nameFROMinformation_schema. COLUMNS aWHEREa.TABLE_NAME REGEXP '^s_'AND a.TABLE_SCHEMA = 'gip_demo') aleft JOIN (SELECTb.table_name,b.column_nameFROMinformation_schema. COLUMNS bWHEREb.TABLE_NAME REGEXP '^s_'AND b.TABLE_SCHEMA = 'epmp') b ON a.table_name = b.table_name  AND a.column_name = b.column_name WHEREb.table_name IS NULL;

执行时间：0.730s

总结：sql语句并没有铁律说用什么方式实现就一定会快，一定要看使用场景。

阅读全文

0 0