需求:查询数据库gip_demo比数据库epmp多的表和字段:
1.优先考虑使用not exist:
SELECT
a.table_name,
a.column_name
FROM
information_schema.COLUMNS a
WHERE
a.TABLE_NAME REGEXP '^s_'
AND a.TABLE_SCHEMA = 'gip_demo'
and
NOT EXISTS (
SELECT
1
FROM
information_schema.COLUMNS b
WHERE
TABLE_NAME REGEXP '^s_'
AND TABLE_SCHEMA = 'epmp'
AND a.column_name = b.column_name
AND a.table_name = b.table_name
);
查询时间: 54.67s
2.使用not in:
select a.table_name,a.column_name from
(SELECT
i.table_name,i.column_name
FROM
information_schema.COLUMNS i
WHERE
i.TABLE_NAME REGEXP '^s_'
AND i.TABLE_SCHEMA = 'gip_demo') a
where (a.table_name,a.column_name)
not in( SELECT
b.table_name,b.column_name
FROM
information_schema.COLUMNS b
WHERE
TABLE_NAME REGEXP '^s_'
AND TABLE_SCHEMA = 'epmp');
执行时间:40.341s
3.使用left join:
SELECT
a.table_name na,a.column_name
FROM
(
SELECT
a.table_name,
a.column_name
FROM
information_schema. COLUMNS a
WHERE
a.TABLE_NAME REGEXP '^s_'
AND a.TABLE_SCHEMA = 'gip_demo'
) a
left JOIN (
SELECT
b.table_name,
b.column_name
FROM
information_schema. COLUMNS b
WHERE
b.TABLE_NAME REGEXP '^s_'
AND b.TABLE_SCHEMA = 'epmp'
) b ON a.table_name = b.table_name AND a.column_name = b.column_name
WHERE
b.table_name IS NULL;
执行时间:
0.730s
总结:sql语句并没有铁律说用什么方式实现就一定会快,一定要看使用场景。