社区编辑申请
注册/登录
DBA技术分享-MySQL外键查询语句
数据库 MySQL
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_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 子句并提供您的数据库名称。

DBA技术分享(六)-分享收藏的mysql外键查询语句

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

DBA技术分享(六)-分享收藏的mysql外键查询语句

3、查询没有外键的表

DBA技术分享(六)-分享收藏的mysql外键查询语句

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 的符号。

DBA技术分享(六)-分享收藏的mysql外键查询语句

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

DBA技术分享(六)-分享收藏的mysql外键查询语句

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 - 象征缺少外键约束的图标。

DBA技术分享(六)-分享收藏的mysql外键查询语句

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

DBA技术分享(六)-分享收藏的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 -孤独者比率- 数据库中孤独者表的百分比(模式)。

DBA技术分享(六)-分享收藏的mysql外键查询语句

三、小结

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

责任编辑:姜华 来源: 今日头条
相关推荐

2022-06-09 17:20:21

DBAMySQL

2010-11-22 10:08:03

2010-10-09 09:55:41

MySQL外键

2010-05-11 19:11:13

2011-08-03 17:43:53

MySQL数据库外键约束

2010-01-05 09:24:42

2010-09-25 11:39:37

SQL语句

2019-10-09 08:52:59

2022-02-11 10:16:50

2011-02-25 15:31:19

MySQL数据库DBA

2010-07-19 16:54:21

2010-08-10 13:44:51

DB2创建外键

2021-03-15 10:01:40

Mysql查询语句

2021-03-15 10:03:17

MySQL数据库索引

2020-07-16 15:00:56

MySQL索引数据库

2011-07-07 14:22:24

SQL查询update

2020-08-05 08:26:28

SQLDBA MySQL

2020-05-27 10:35:16

SQLDBA 索引

2018-06-01 10:08:00

DBA美团SQL

2019-07-26 09:22:20

同话题下的热门内容

推荐几款好用的MySQL开源客户端,建议收藏MySQL性能调优,应该掌握这一个工具!!!不同数据库存储引擎技术的优劣势分析慢SQL,压垮团队的最后一根稻草!基于图数据库的元数据血缘关系分析技术研究与实践

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号