DBA技术分享-MySQL外键查询语句

一、概述

作为DBA分享几个工作中关于外键的常用查询。具体如下 ：

• 如何查询用户数据库（模式）中定义的外键约束。
• 如何查询所有引用具有外键的特定的表。
• 如何查询没有外键的表。
• 如何查找没有关系的表 – Loner Tables。
• 如何查询MySQL 数据库中没有关系表的比率。

二、相关SQL

1、查询用户数据库（模式）中定义的外键约束

select concat(fks.constraint_schema,'.', fks.table_name)as foreign_table,
'->'as rel,
       concat(fks.unique_constraint_schema,'.', fks.referenced_table_name)
as primary_table,
       fks.constraint_name,
       group_concat(kcu.column_name
orderby position_in_unique_constraint separator ', ')
as fk_columns
from information_schema.referential_constraints fks
join information_schema.key_column_usage kcu
on fks.constraint_schema= kcu.table_schema
and fks.table_name= kcu.table_name
and fks.constraint_name= kcu.constraint_name
-- where fks.constraint_schema = 'database name'
groupby fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
orderby fks.constraint_schema,
         fks.table_name;

注意：如果您需要特定数据库（模式）的信息，请取消注释 where 子句并提供您的数据库名称。

2、查询所有引用具有外键的特定的表。

selectdistinct concat(table_schema,'.', table_name)as foreign_table,
'>-'as rel,
       concat(referenced_table_schema,'.', referenced_table_name)
as primary_table
from information_schema.key_column_usage
where referenced_table_name ='table name'-- provide table name here
--      and table_schema = 'database name'
orderby foreign_table;

说明：

• foreign_table – 外部表名 – 您要查找的表。
• rel – 涉及 FK 和方向的关系符号。
• primary_table – 主要（引用）表名 – 您作为参数提供的表。

3、查询没有外键的表

select tab.table_schemaas database_name,
       tab.table_name,
'>- no FKs'as foreign_keys
from information_schema.tables tab
left join information_schema.table_constraints fks
on fks.table_schema= tab.table_schema
and fks.table_name= tab.table_name
and fks.constraint_type='FOREIGN KEY'
where tab.table_type='BASE TABLE'
and tab.table_schemanotin('mysql','information_schema',
'performance_schema','sys')
and fks.table_nameisnull
-- and tab.table_schema = 'your database name'
orderby tab.table_schema,
         tab.table_name;

说明：

• database_name – 数据库的名称（模式）。
• table_name – 表的名称。
• foreign_keys – 表示缺少 FK 的符号。

4、查找没有关系的表 – Loner Tables

select'No FKs >-'as refs,
       concat(tab.table_schema,'.', tab.table_name)as'table',
'>- no FKs'as fks
from information_schema.tables tab
left join information_schema.referential_constraints ref
on tab.table_schema= ref.constraint_schema
and tab.table_name= ref.table_name
left join information_schema.referential_constraints ref_by
on tab.table_schema= ref_by.unique_constraint_schema
and tab.table_name= ref_by.referenced_table_name
where ref.constraint_nameisnull
and ref_by.constraint_nameisnull
and tab.table_type='BASE TABLE'
and tab.table_schemanotin('mysql','information_schema',
'performance_schema','sys')
-- and tab.table_schema = 'your database name'
orderby tab.table_schema,
         tab.table_name;

说明：

• refs – 表示缺少外键约束引用的图标。
• table– 表的名称。
• fks – 象征缺少外键约束的图标。

5、MySQL 数据库中没有关系表的比率

select all_tables as table_count,
       no_rel as loner_tables,
       concat(cast(100.0*(no_rel/all_tables)asdecimal(5,2)),'%')
as loner_ratio
from
(selectcount(distinct concat(tab.table_schema,'.', tab.table_name))
as all_tables,
            SUM(case when ref.constraint_nameisnull
and ref_by.constraint_nameisnull
                     then 1
                     else 0 end)as no_rel
from information_schema.tables tab
    left join information_schema.referential_constraints ref
on tab.table_schema= ref.constraint_schema
and tab.table_name= ref.table_name
    left join information_schema.referential_constraints ref_by
on tab.table_schema= ref_by.unique_constraint_schema
and tab.table_name= ref_by.referenced_table_name
where tab.table_type='BASE TABLE'
and tab.table_schemanotin('mysql','information_schema',
'sys','performance_schema')
) temp;

说明：

• table_count – 数据库中的表数（模式）。
• loner_tables – 数据库中Loner 表的数量（模式）。
• loner_ratio -孤独者比率- 数据库中孤独者表的百分比（模式）。

三、小结

mysql外键是我们工作中经常遇到的，这几个关于外键查询，可以帮忙提高数据库维护的效率。