MySQL 常用脚本之查看数据库、表结构、约束、索引等信息 |
您所在的位置：网站首页 › 数据库中查看表和表的结构用什么命令 › MySQL 常用脚本之查看数据库、表结构、约束、索引等信息 |
|
文章目录
数据库和模式列出数据库/模式列出用户创建的数据库/模式查看数据库的创建语句
数据表列出某个数据库中的表列出所有数据库中的表列出缺少主键的表列出数据库中的 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;写作不易，如果你点击了收藏⭐，请不要忘了关注❤️、评论📝、点赞👍！ |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |