個(gè)人中心
退出
MySQL 常用腳本之查看數(shù)據(jù)庫、表結(jié)構(gòu)、約束、索引等信息
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
數(shù)據(jù)庫和模式
列出數(shù)據(jù)庫/模式
列出用戶創(chuàng)建的數(shù)據(jù)庫/模式
查看數(shù)據(jù)庫的創(chuàng)建語句
數(shù)據(jù)表
列出某個(gè)數(shù)據(jù)庫中的表
列出所有數(shù)據(jù)庫中的表
列出缺少主鍵的表
列出數(shù)據(jù)庫中的 InnoDB 表
列出數(shù)據(jù)庫中的 MyISAM 表
查看數(shù)據(jù)表的存儲(chǔ)引擎
查找最近創(chuàng)建的表
查找最近修改的表
查看表的創(chuàng)建語句
字段
列出數(shù)據(jù)庫中所有表的字段
列出數(shù)據(jù)庫中指定表的字段
列出所有數(shù)字類型的字段
列出所有字符類型的字段
列出所有日期時(shí)間類型的字段
列出字段的詳細(xì)信息
列出計(jì)算列及其表達(dá)式
主鍵、外鍵、唯一等約束
列出指定數(shù)據(jù)庫中的主鍵約束
列出指定數(shù)據(jù)庫中的外鍵約束
列出指定數(shù)據(jù)庫中的唯一約束
列出指定數(shù)據(jù)庫中的字段默認(rèn)值
索引
列出指定數(shù)據(jù)庫中的索引
列出指定表中的索引
數(shù)據(jù)行及大小
查詢表中的行數(shù)
查看表分配和使用的空間
查看表中數(shù)據(jù)和索引的使用空間
查看 InnoDB 輔助索引占用的空間
查看 LOB 大對(duì)象占用的空間
數(shù)據(jù)庫比較
比較兩個(gè)數(shù)據(jù)庫中的表和字段信息
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天帶來的分享是如何查看 MySQL 數(shù)據(jù)庫模式以及數(shù)據(jù)表的結(jié)構(gòu)，包括字段定義、主鍵、外鍵、唯一等約束和索引信息，如何查看表和索引占用的磁盤空間等。
數(shù)據(jù)庫和模式
列出數(shù)據(jù)庫/模式
在 MySQL 中，數(shù)據(jù)庫（database）和模式（schema）是相同的概念，可以使用以下查詢列出當(dāng)前實(shí)例中包含的數(shù)據(jù)庫/模式：
-- 方法一
select schema_name as database_name
from information_schema.schemata
order by schema_name;
-- 方法二
show databases;
-- 方法三
show schemas;
查詢結(jié)果中的 information_schema、mysql、performance_schema 以及 sys 屬于系統(tǒng)數(shù)據(jù)庫/模式。
列出用戶創(chuàng)建的數(shù)據(jù)庫/模式
將系統(tǒng)數(shù)據(jù)庫/模式排除之外就是用戶創(chuàng)建的數(shù)據(jù)庫/模式：
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;
查看數(shù)據(jù)庫的創(chuàng)建語句
使用 SHOW 命令查看數(shù)據(jù)庫的創(chuàng)建語句：
show create database database_name;
show create schema database_name;
數(shù)據(jù)表
列出某個(gè)數(shù)據(jù)庫中的表
查看當(dāng)前數(shù)據(jù)庫中的表：
-- 方法一
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() 函數(shù)返回當(dāng)前數(shù)據(jù)庫的名稱。
查看指定數(shù)據(jù)庫中的表，將查詢條件中的 database_name 替換成需要查詢的數(shù)據(jù)庫名：
-- 方法一
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];
列出所有數(shù)據(jù)庫中的表
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;
列出數(shù)據(jù)庫中的 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;
列出數(shù)據(jù)庫中的 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;
查看數(shù)據(jù)表的存儲(chǔ)引擎
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;
查找最近創(chuàng)建的表
使用以下腳本查找最近 30 天之內(nèi)創(chuàng)建的表：
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 天之內(nèi)被修改過的表：
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;
以上查詢返回的結(jié)果可能不準(zhǔn)確，因?yàn)閡pdate_time 取決于存儲(chǔ)引擎，具體可以參考官方文檔。
查看表的創(chuàng)建語句
show create table table_name;
字段
列出數(shù)據(jù)庫中所有表的字段
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')
-- 查看當(dāng)前數(shù)據(jù)庫中的表
-- and tab.table_schema = database()
-- 查看指定數(shù)據(jù)庫中的表
-- and tab.table_schema = 'database_name'
order by tab.table_name, col.ordinal_position;
列出數(shù)據(jù)庫中指定表的字段
-- 方法一
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;
列出所有數(shù)字類型的字段
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;
列出所有日期時(shí)間類型的字段
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;
列出字段的詳細(xì)信息
以下查詢用于列出字段的詳細(xì)信息，包括是否主鍵、外鍵、唯一、默認(rèn)值、是否可空以及計(jì)算列的表達(dá)式等：
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')
-- 查看當(dāng)前數(shù)據(jù)庫中的表
-- and tab.table_schema = database()
-- 查看指定數(shù)據(jù)庫中的表
-- 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;
列出計(jì)算列及其表達(dá)式
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')
-- 查看當(dāng)前數(shù)據(jù)庫中的表
-- and table_schema = database()
-- 查看指定數(shù)據(jù)庫中的表
-- and table_schema = 'database_name'
-- 查看指定表
-- and table_name = 'tablename'
order by table_schema, table_name, column_name;
主鍵、外鍵、唯一等約束
列出指定數(shù)據(jù)庫中的主鍵約束
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;
列出指定數(shù)據(jù)庫中的外鍵約束
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;
列出指定數(shù)據(jù)庫中的唯一約束
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;
列出指定數(shù)據(jù)庫中的字段默認(rèn)值
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;
索引
列出指定數(shù)據(jù)庫中的索引
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;
數(shù)據(jù)行及大小
查詢表中的行數(shù)
對(duì)于不同的存儲(chǔ)引擎使用不同的查詢方法：
-- MyISAM 存儲(chǔ)引擎表
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 存儲(chǔ)引擎表
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;
查看表中數(shù)據(jù)和索引的使用空間
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;
對(duì)于 InnoDB 表，data_length 字段表示聚集索引的大?。ò怂械臄?shù)據(jù)）。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 大對(duì)象占用的空間
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;
數(shù)據(jù)庫比較
比較兩個(gè)數(shù)據(jù)庫中的表和字段信息
以下查詢比較兩個(gè)數(shù)據(jù)庫中的所有表，返回任意數(shù)據(jù)庫中越少的字段：
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;
寫作不易，如果你點(diǎn)擊了收藏?，請(qǐng)不要忘了關(guān)注??、評(píng)論??、點(diǎn)贊??！
分類導(dǎo)航