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

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 order by position_in_unique_constraint separator ', ') as fk_columnsfrom information_schema.referential_constraints fksjoin 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_nameorder 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_tablefrom information_schema.key_column_usagewhere 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_keysfrom information_schema.tables tableft 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 fksfrom information_schema.tables tableft join information_schema.referential_constraints ref on tab.table_schema = ref.constraint_schema and tab.table_name = ref.table_nameleft join information_schema.referential_constraints ref_by on tab.table_schema = ref_by.unique_constraint_schema and tab.table_name = ref_by.referenced_table_namewhere 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_ratiofrom (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外键是我们工作中经常遇到的，这几个关于外键查询，可以帮忙提高数据库维护的效率。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。