DBA技术分享-MySQL外键查询语句
2023-03-14 09:34:34 时间
一、概述
作为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 子句并提供您的数据库名称。
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外键是我们工作中经常遇到的,这几个关于外键查询,可以帮忙提高数据库维护的效率。
相关文章
- Ambari部署HDP时,Select Version时页面数据来源解析
- 通过 KoP 将 Kafka 应用迁移到 Pulsar
- ChIP-seq 分析:数据比对(3)
- R语言、SAS潜类别(分类)轨迹模型LCTM分析体重指数 (BMI)数据可视化|附代码数据
- R语言空气污染数据的地理空间可视化和分析:颗粒物2.5(PM2.5)和空气质量指数(AQI)|附代码数据
- Matlab用深度学习循环神经网络RNN长短期记忆LSTM进行波形时间序列数据预测|附代码数据
- θ - γ耦合作为脑机接口调节慢性脑卒中运动恢复的皮质生物标志物
- Meta研究人员利用人工智能解码脑电语音信号(全文解读)
- 司机开车偏离车道难道与大脑想睡觉有关?
- 2023十大科技趋势预测!百度研究院最新发布
- 工程监测多通道振弦模拟信号采集仪VTN数据查看
- 开发语音脑机接口和语音神经假肢的颅内EEG数据
- 利用EEG和动态编码器-解码器建模框架实时估算驾驶员睡意
- GB/T28181-2022协议版本标识X-GB-Ver解读
- FormFactor 添加了对量子计算机部署至关重要的稀释制冷 (DR) 系统
- SAP软件MDG模块之License的度量方式
- Android OpenGL ES 纹理
- AntDB-M设计之内存结构
- 基于Gan的cifar10数据生成器
- CelebA Datasets——Readme