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

一、术分概述

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

如何查询用户数据库（模式）中定义的查询外键约束。如何查询所有引用具有外键的语句特定的表。如何查询没有外键的术分表。如何查找没有关系的外键表 - Loner Tables。如何查询MySQL 数据库中没有关系表的查询比率。二、语句相关SQL1、术分查询用户数据库（模式）中定义的外键外键约束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;

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

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 - 主要（引用）表名 - 您作为参数提供的表。

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 - 数据库的名称（模式）。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 数据库中没有关系表的比率

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 - 数据库中的表数（模式）。loner_tables - 数据库中Loner 表的数量（模式）。loner_ratio -孤独者比率- 数据库中孤独者表的百分比（模式）。

三、小结

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

很赞哦!（5）