DBA技术分享–MySQL三个关于主键PrimaryKeys的查询

概述

分享作为DBA日常工作中，关于mysql主键的3个常用查询语句，分别如下：

• 列出 MySQL 数据库中的所有主键 (PK) 及其列。
• 列出用户数据库（模式）中没有主键的表。
• 查询显示了用户数据库（模式）中有多少没有主键的表，以及占总表的百分比。

列出 MySQL 数据库中的所有主键 (PK) 及其列

select tab.table_schemaas database_schema,
    sta.index_nameas pk_name,
    sta.seq_in_indexas column_id,
    sta.column_name,
    tab.table_name
from information_schema.tablesas tab
inner join information_schema.statisticsas sta
on sta.table_schema= tab.table_schema
and sta.table_name= tab.table_name
and sta.index_name='primary'
where tab.table_schema='your database name'
and tab.table_type='BASE TABLE'
orderby tab.table_name,
    column_id;

列说明：

• table_schema – PK 数据库（模式）名称。
• pk_name – PK 约束名称。
• column_id – 索引 (1, 2, …) 中列的 id。2 或更高表示键是复合键（包含多于一列）。
• column_name – 主键列名。
• table_name – PK 表名。

输出示例：

输出结果说明：

• 一行：代表一个主键列。
• 行范围：数据库中所有 PK 约束的列（模式）。
• 排序方式：表名、列id。

列出用户数据库（模式）中没有主键的表

select tab.table_schemaas database_name,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema= tco.table_schema
and tab.table_name= tco.table_name
and tco.constraint_type='PRIMARY KEY'
where tco.constraint_typeisnull
and tab.table_schemanotin('mysql','information_schema',
'performance_schema','sys')
and tab.table_type='BASE TABLE'
--      and tab.table_schema = 'sakila' -- put schema name here
orderby tab.table_schema,
         tab.table_name;

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

列说明：

• database_name – 数据库（模式）名称。
• table_name – 表名。

示例：

输出结果说明：

• 一行：表示数据库中没有主键的一张表（模式）。
• 行范围：数据库中没有主键的所有表（模式）。
• 排序方式：数据库（模式）名称、表名。

查询显示了用户数据库（模式）中有多少没有主键的表，以及占总表的百分比

selectcount(*)as all_tables,
count(*)-count(tco.constraint_type)as no_pk_tables,
       cast(100.0*(count(*)-count(tco.constraint_type))/count(*)
asdecimal(5,2))as no_pk_percent
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema= tco.table_schema
and tab.table_name= tco.table_name
and tco.constraint_type='PRIMARY KEY'
where tab.table_type='BASE TABLE'
-- and tab.table_schema = 'database_name' -- put your database name here
and tab.table_schemanotin('mysql','information_schema',
'sys','performance_schema');

列说明：

• all_tables – 数据库中所有表的数量
• no_pk_tables – 没有主键的表数
• no_pk_percent – 所有表中没有主键的表的百分比

示例：