在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

MySQL 常用腳本之查看數據庫、表結構、約束、索引等信息

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數據庫管理與開發(fā)經驗，目前在一家全球性的金融公司從事數據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        數據庫和模式
            列出數據庫/模式
            列出用戶創(chuàng)建的數據庫/模式
            查看數據庫的創(chuàng)建語句
        數據表
            列出某個數據庫中的表
            列出所有數據庫中的表
            列出缺少主鍵的表
            列出數據庫中的 InnoDB 表
            列出數據庫中的 MyISAM 表
            查看數據表的存儲引擎
            查找最近創(chuàng)建的表
            查找最近修改的表
            查看表的創(chuàng)建語句
        字段
            列出數據庫中所有表的字段
            列出數據庫中指定表的字段
            列出所有數字類型的字段
            列出所有字符類型的字段
            列出所有日期時間類型的字段
            列出字段的詳細信息
            列出計算列及其表達式
        主鍵、外鍵、唯一等約束
            列出指定數據庫中的主鍵約束
            列出指定數據庫中的外鍵約束
            列出指定數據庫中的唯一約束
            列出指定數據庫中的字段默認值
        索引
            列出指定數據庫中的索引
            列出指定表中的索引
        數據行及大小
            查詢表中的行數
            查看表分配和使用的空間
            查看表中數據和索引的使用空間
            查看 InnoDB 輔助索引占用的空間
            查看 LOB 大對象占用的空間
        數據庫比較
            比較兩個數據庫中的表和字段信息

大家好！我是只談技術不剪發(fā)的 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 屬于系統數據庫/模式。
列出用戶創(chuàng)建的數據庫/模式

將系統數據庫/模式排除之外就是用戶創(chuàng)建的數據庫/模式：

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;



查看數據庫的創(chuàng)建語句

使用 SHOW 命令查看數據庫的創(chuàng)建語句：

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;



查找最近創(chuàng)建的表

使用以下腳本查找最近 30 天之內創(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 天之內被修改過的表：

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 取決于存儲引擎，具體可以參考官方文檔。
查看表的創(chuàng)建語句

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;


寫作不易，如果你點擊了收藏?，請不要忘了關注??、評論??、點贊??！