一、概述

作為DBA分享幾個(gè)工作中關(guān)于外鍵的常用查詢。具體如下 ：

目前成都創(chuàng)新互聯(lián)已為數(shù)千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)站空間、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計(jì)、溫州網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

• 如何查詢用戶數(shù)據(jù)庫（模式）中定義的外鍵約束。
• 如何查詢所有引用具有外鍵的特定的表。
• 如何查詢沒有外鍵的表。
• 如何查找沒有關(guān)系的表 - Loner Tables。
• 如何查詢MySQL 數(shù)據(jù)庫中沒有關(guān)系表的比率。

二、相關(guān)SQL

1、查詢用戶數(shù)據(jù)庫（模式）中定義的外鍵約束

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
            order by 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'
group by fks.constraint_schema,
         fks.table_name,
         fks.unique_constraint_schema,
         fks.referenced_table_name,
         fks.constraint_name
order by fks.constraint_schema,
         fks.table_name;

注意：如果您需要特定數(shù)據(jù)庫（模式）的信息，請取消注釋 where 子句并提供您的數(shù)據(jù)庫名稱。

2、查詢所有引用具有外鍵的特定的表。

select distinct 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'
order by foreign_table;

說明：

• foreign_table - 外部表名 - 您要查找的表。
• rel - 涉及 FK 和方向的關(guān)系符號。
• primary_table - 主要（引用）表名 - 您作為參數(shù)提供的表。

3、查詢沒有外鍵的表

select tab.table_schema as 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_schema not in ('mysql', 'information_schema',
                                   'performance_schema', 'sys')
      and fks.table_name is null
     -- and tab.table_schema = 'your database name'
order by tab.table_schema,
         tab.table_name;

說明：

• database_name - 數(shù)據(jù)庫的名稱（模式）。
• table_name - 表的名稱。
• foreign_keys - 表示缺少 FK 的符號。

4、查找沒有關(guān)系的表 - 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_name is null
      and ref_by.constraint_name is null
      and tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('mysql', 'information_schema',
                                   'performance_schema', 'sys')
     -- and tab.table_schema = 'your database name'
order by tab.table_schema,
         tab.table_name;

說明：

• refs - 表示缺少外鍵約束引用的圖標(biāo)。
• table- 表的名稱。
• fks - 象征缺少外鍵約束的圖標(biāo)。

5、MySQL 數(shù)據(jù)庫中沒有關(guān)系表的比率

select all_tables as table_count,
       no_rel as loner_tables,
       concat(cast(100.0*(no_rel/all_tables) as decimal(5,2)), '%')
              as loner_ratio
from 
    (select count(distinct concat(tab.table_schema, '.', tab.table_name)) 
            as all_tables,
            SUM(case when ref.constraint_name is null
                          and ref_by.constraint_name is null
                     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_schema not in ('mysql', 'information_schema',
                                       'sys', 'performance_schema')
    ) temp;

說明：

• table_count - 數(shù)據(jù)庫中的表數(shù)（模式）。
• loner_tables - 數(shù)據(jù)庫中Loner 表的數(shù)量（模式）。
• loner_ratio -孤獨(dú)者比率- 數(shù)據(jù)庫中孤獨(dú)者表的百分比（模式）。

三、小結(jié)

mysql外鍵是我們工作中經(jīng)常遇到的，這幾個(gè)關(guān)于外鍵查詢，可以幫忙提高數(shù)據(jù)庫維護(hù)的效率。

分享名稱：DBA技術(shù)分享-MySQL外鍵查詢語句
文章源于：http://uogjgqi.cn/article/dhipohh.html