DBA技術分享-MySQL外鍵查詢語句
作者：雪竹頻道
MySQL外鍵是我們工作中經(jīng)常遇到的，這幾個關于外鍵查詢，可以幫忙提高數(shù)據(jù)庫維護的效率。
一、概述
作為DBA分享幾個工作中關于外鍵的常用查詢。具體如下 ：
- 如何查詢用戶數(shù)據(jù)庫（模式）中定義的外鍵約束。
- 如何查詢所有引用具有外鍵的特定的表。
- 如何查詢沒有外鍵的表。
- 如何查找沒有關系的表 - Loner Tables。
- 如何查詢MySQL 數(shù)據(jù)庫中沒有關系表的比率。
二、相關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 和方向的關系符號。
- 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、查找沒有關系的表 - 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 - 表示缺少外鍵約束引用的圖標。
- table- 表的名稱。
- fks - 象征缺少外鍵約束的圖標。
5、MySQL 數(shù)據(jù)庫中沒有關系表的比率
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 -孤獨者比率- 數(shù)據(jù)庫中孤獨者表的百分比（模式）。
三、小結
mysql外鍵是我們工作中經(jīng)常遇到的，這幾個關于外鍵查詢，可以幫忙提高數(shù)據(jù)庫維護的效率。
責任編輯：姜華
來源：
今日頭條
