个人中心
退出
MySQL 常用脚本之查看数据库、表结构、约束、索引等信息
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
数据库和模式
列出数据库/模式
列出用户创建的数据库/模式
查看数据库的创建语句
数据表
列出某个数据库中的表
列出所有数据库中的表
列出缺少主键的表
列出数据库中的 InnoDB 表
列出数据库中的 MyISAM 表
查看数据表的存储引擎
查找最近创建的表
查找最近修改的表
查看表的创建语句
字段
列出数据库中所有表的字段
列出数据库中指定表的字段
列出所有数字类型的字段
列出所有字符类型的字段
列出所有日期时间类型的字段
列出字段的详细信息
列出计算列及其表达式
主键、外键、唯一等约束
列出指定数据库中的主键约束
列出指定数据库中的外键约束
列出指定数据库中的唯一约束
列出指定数据库中的字段默认值
索引
列出指定数据库中的索引
列出指定表中的索引
数据行及大小
查询表中的行数
查看表分配和使用的空间
查看表中数据和索引的使用空间
查看 InnoDB 辅助索引占用的空间
查看 LOB 大对象占用的空间
数据库比较
比较两个数据库中的表和字段信息
大家好！我是只谈技术不剪发的 Tony 老师。今天带来的分享是如何查看 MySQL 数据库模式以及数据表的结构，包括字段定义、主键、外键、唯一等约束和索引信息，如何查看表和索引占用的磁盘空间等。
数据库和模式
列出数据库/模式
在 MySQL 中，数据库（database）和模式（schema）是相同的概念，可以使用以下查询列出当前实例中包含的数据库/模式：
-- 方法一
select schema_name as database_name
from information_schema.schemata
order by schema_name;
-- 方法二
show databases;
-- 方法三
show schemas;
查询结果中的 information_schema、mysql、performance_schema 以及 sys 属于系统数据库/模式。
列出用户创建的数据库/模式
将系统数据库/模式排除之外就是用户创建的数据库/模式：
select schema_name as database_name
from information_schema.schemata
where schema_name not in ('information_schema','mysql','performance_schema','sys')
order by schema_name;
查看数据库的创建语句
使用 SHOW 命令查看数据库的创建语句：
show create database database_name;
show create schema database_name;
数据表
列出某个数据库中的表
查看当前数据库中的表：
-- 方法一
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = database()
order by database_name, table_name;
-- 方法二
show tables [LIKE 'pattern' | WHERE expr];
-- 方法三
show table status [LIKE 'pattern' | WHERE expr];
其中，database() 函数返回当前数据库的名称。
查看指定数据库中的表，将查询条件中的 database_name 替换成需要查询的数据库名：
-- 方法一
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = 'database_name'
order by database_name, table_name;
-- 方法二
show tables {in | from} database_name [LIKE 'pattern' | WHERE expr];
show tables status {in | from} database_name [LIKE 'pattern' | WHERE expr];
列出所有数据库中的表
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
order by database_name, table_name;
列出缺少主键的表
select tab.table_schema as 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_type is null
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys')
and tab.table_type = 'BASE TABLE'
-- and tab.table_schema = 'sakila'
order by tab.table_schema, tab.table_name;
列出数据库中的 InnoDB 表
select table_schema as database_name, table_name
from information_schema.tables
where engine = 'InnoDB'
and table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
列出数据库中的 MyISAM 表
select table_schema as database_name, table_name
from information_schema.tables
where engine = 'MyISAM'
and table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
查看数据表的存储引擎
select table_schema as database_name, table_name, engine
from information_schema.tables
where table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
查找最近创建的表
使用以下脚本查找最近 30 天之内创建的表：
select table_schema as database_name, table_name, create_time
from information_schema.tables
where table_type = 'BASE TABLE'
and create_time > adddate(current_date, interval - 30 day)
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by create_time desc, table_name;
查找最近修改的表
使用以下脚本查找最近 30 天之内被修改过的表：
select table_schema as database_name, table_name, update_time
from information_schema.tables
where table_type = 'BASE TABLE'
and update_time > (current_timestamp() - interval 30 day)
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by update_time desc, table_name;
以上查询返回的结果可能不准确，因为update_time 取决于存储引擎，具体可以参考官方文档。
查看表的创建语句
show create table table_name;
字段
列出数据库中所有表的字段
select tab.table_schema as database_schema,
tab.table_name as table_name,
col.ordinal_position as column_id,
col.column_name as column_name,
col.data_type as data_type,
case when col.numeric_precision is not null then col.numeric_precision
else col.character_maximum_length
end as max_length,
case when col.datetime_precision is not null then col.datetime_precision
when col.numeric_scale is not null then col.numeric_scale
else 0
end as 'precision'
from information_schema.tables as tab
join information_schema.columns as col
on col.table_schema = tab.table_schema
and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and tab.table_schema = database()
-- 查看指定数据库中的表
-- and tab.table_schema = 'database_name'
order by tab.table_name, col.ordinal_position;
列出数据库中指定表的字段
-- 方法一
desc table_name;
-- 方法二
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} table_name
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE expr];
-- 方法二
select ordinal_position as column_id,
column_name as column_name,
data_type as data_type,
case when numeric_precision is not null then numeric_precision
else character_maximum_length
end as max_length,
case when datetime_precision is not null then datetime_precision
when numeric_scale is not null then numeric_scale
else 0
end as data_precision,
is_nullable,
column_default
from information_schema.columns
where table_name = 'tablename'
and table_schema = 'schema_name'
order by ordinal_position;
列出所有数字类型的字段
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('tinyint', 'smallint', 'mediumint', 'int', 'bigint',
'decimal', 'bit', 'float', 'double')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
列出所有字符类型的字段
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('char', 'varchar', 'binary', 'varbinary',
'blob', 'tinyblob', 'mediumblob', 'longblob',
'text', 'tinytext', 'mediumtext', 'longtext'
'enum', 'set')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
列出所有日期时间类型的字段
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('date', 'time', 'datetime', 'year', 'timestamp')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
列出字段的详细信息
以下查询用于列出字段的详细信息，包括是否主键、外键、唯一、默认值、是否可空以及计算列的表达式等：
select col.table_schema as database_name,
col.table_name,
col.column_name,
col.data_type,
case when col.data_type in ('datetime', 'timestamp', 'time') then col.datetime_precision
else col.numeric_precision
end as 'precision',
col.numeric_scale,
col.character_maximum_length as char_length,
col.column_default,
col.generation_expression,
case when (group_concat(constraint_type separator ', ')) like '%PRIMARY KEY%' then 'YES'
else 'NO'
end as PK,
case when (group_concat(constraint_type separator ', ')) like '%UNIQUE%' then 'YES'
else 'NO'
end as UQ,
case when (group_concat(constraint_type separator ', ')) like '%FOREIGN KEY%' then 'YES'
else 'NO'
end as FK,
col.is_nullable
from information_schema.columns col
join information_schema.tables tab
on col.table_schema = tab.table_schema and col.table_name = tab.table_name and tab.table_type = 'BASE TABLE'
left join information_schema.key_column_usage kcu
on col.table_schema = kcu.table_schema and col.table_name = kcu.table_name and col.column_name = kcu.column_name
left join information_schema.table_constraints tco
on kcu.constraint_schema = tco.constraint_schema and kcu.constraint_name = tco.constraint_name and kcu.table_name = tco.table_name
where tab.table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and tab.table_schema = database()
-- 查看指定数据库中的表
-- and tab.table_schema = 'database_name'
group by 1,2,3,4,5,6,7,8,9,13
order by col.table_schema, col.table_name, col.column_name;
列出计算列及其表达式
select table_schema as database_name,
table_name,
column_name,
data_type,
generation_expression
from information_schema.columns
where length(generation_expression) > 0
and table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and table_schema = database()
-- 查看指定数据库中的表
-- and table_schema = 'database_name'
-- 查看指定表
-- and table_name = 'tablename'
order by table_schema, table_name, column_name;
主键、外键、唯一等约束
列出指定数据库中的主键约束
select tab.table_schema as database_schema,
sta.index_name as pk_name,
sta.seq_in_index as column_id,
sta.column_name,
tab.table_name
from information_schema.tables as tab
join information_schema.statistics as sta
on sta.table_schema = tab.table_schema and sta.table_name = tab.table_name and sta.index_name = 'primary'
where tab.table_schema = 'database_name'
and tab.table_type = 'BASE TABLE'
order by tab.table_name, column_id;
列出指定数据库中的外键约束
select concat(col.table_schema, '.', col.table_name) as 'foreign_table',
col.column_name as column_name,
'->' as rel,
concat(kcu.referenced_table_schema, '.', kcu.referenced_table_name) as primary_table,
kcu.referenced_column_name as pk_column_name,
kcu.constraint_name as fk_constraint_name
from information_schema.columns col
join information_schema.tables tab
on col.table_schema = tab.table_schema and col.table_name = tab.table_name
left join information_schema.key_column_usage kcu
on col.table_schema = kcu.table_schema and col.table_name = kcu.table_name and col.column_name = kcu.column_name
where col.table_schema not in('information_schema','sys', 'mysql', 'performance_schema')
and tab.table_type = 'BASE TABLE'
and kcu.referenced_table_schema is not null
and tab.table_schema = 'hrdb'
order by col.table_schema, col.table_name, col.ordinal_position;
列出指定数据库中的唯一约束
select stat.table_schema as database_name,
stat.table_name,
stat.index_name,
group_concat(stat.column_name order by stat.seq_in_index separator ', ') as columns,
tco.constraint_type
from information_schema.statistics stat
join information_schema.table_constraints tco
on stat.table_schema = tco.table_schema and stat.table_name = tco.table_name and stat.index_name = tco.constraint_name
where stat.non_unique = 0
and stat.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
and stat.table_schema = 'database_name'
group by stat.table_schema, stat.table_name, stat.index_name, tco.constraint_type
order by stat.table_schema, stat.table_name;
列出指定数据库中的字段默认值
select table_schema as database_name,
table_name,
column_name,
column_default
from information_schema.columns
where column_default is not null
and table_schema not in ('information_schema', 'sys', 'performance_schema','mysql')
and table_schema = 'database_name'
order by table_schema, table_name, ordinal_position;
索引
列出指定数据库中的索引
select table_schema as database_name,
table_name,
index_name,
group_concat(column_name order by seq_in_index) as columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and table_schema = 'database_name'
group by table_schema, table_name, index_name, index_type, non_unique
order by table_schema, table_name;
列出指定表中的索引
-- 方法一
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
{FROM | IN} table_name
[{FROM | IN} database_name]
[WHERE expr];
-- 方法二
select table_schema as database_name,
table_name,
index_name,
group_concat(column_name order by seq_in_index) as columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and table_schema = 'database_name'
and table_name = 'table_name'
group by table_schema, table_name, index_name, index_type, non_unique
order by table_schema, table_name;
数据行及大小
查询表中的行数
对于不同的存储引擎使用不同的查询方法：
-- MyISAM 存储引擎表
select table_schema, table_name, table_rows
from information_schema.tables
where table_type = 'BASE TABLE'
and engine = 'MyISAM'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
order by table_schema, table_name;
-- InnoDB 存储引擎表
select count(*) from table_name;
查看表分配和使用的空间
select table_schema as database_name,
table_name,
round(sum((data_length + index_length)) / power(1024, 2), 2) as used_mb,
round(sum((data_length + index_length + data_free)) /power(1024, 2), 2) as allocated_mb,
round(sum(data_free) /power(1024, 2), 2) as free_mb
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
group by table_schema, table_name
order by used_mb desc;
查看表中数据和索引的使用空间
select table_schema as database_name,
table_name,
engine,
round(1.0*data_length/1024/1024, 2) as data_size_mb,
round(index_length/1024/1024, 2) as index_size_mb,
round((data_length + index_length)/1024/1024, 2) as total_size_mb
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
order by total_size_mb desc;
对于 InnoDB 表，data_length 字段表示聚集索引的大小（包含了所有的数据）。InnoDB 表的 index_length 和 data_length 返回的是近似值。
查看 InnoDB 辅助索引占用的空间
select database_name,
table_name,
index_name,
(1.0*stat_value*@@innodb_page_size/1024/1024) as index_size_mb
from mysql.innodb_index_stats
where stat_name = 'size'
and index_name not in ('PRIMARY', 'GEN_CLUST_INDEX')
-- and database_name = 'database_name'
-- and table_name = 'table_name'
order by index_size_mb desc;
查看 LOB 大对象占用的空间
select tab.table_schema as database_name, tab.table_name,
round(sum(data_length + index_length) / power(1024, 2), 2) as used_mb,
round(sum(data_length + index_length + data_free) / power(1024, 2), 2) as allocated_mb
from information_schema.tables as tab
join information_schema.columns as col
on col.table_schema = tab.table_schema and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and col.data_type in ('blob', 'mediumblob', 'longblob', 'text', 'mediumtext', 'longtext')
-- and tab.table_schema = 'database_name'
-- and tab.table_name = 'table_name'
group by 1,2
order by 2;
数据库比较
比较两个数据库中的表和字段信息
以下查询比较两个数据库中的所有表，返回任意数据库中越少的字段：
set @database_1 = 'database_name_1'; -- provide first database name here
set @database_2 = 'database_name_2'; -- provide second database name here
select *
from (
select COALESCE(c1.table_name, c2.table_name) as table_name,
COALESCE(c1.column_name, c2.column_name) as table_column,
c1.column_name as database1,
c2.column_name as database2
from
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_1) c1
right join
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_2) c2
on c1.table_name = c2.table_name and c1.column_name = c2.column_name
union
select COALESCE(c1.table_name, c2.table_name) as table_name,
COALESCE(c1.column_name, c2.column_name) as table_column,
c1.column_name as schema1,
c2.column_name as schema2
from
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_1) c1
left join
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_2) c2
on c1.table_name = c2.table_name and c1.column_name = c2.column_name
) tmp
where database1 is null
or database2 is null
order by table_name,
table_column;
set @database_1 = null;
set @database_2 = null;
写作不易，如果你点击了收藏⭐，请不要忘了关注❤️、评论📝、点赞👍！
分类导航