Mysql查詢所有表和字段信息的方法

Posted on by

1 MySQL中information_schema是什麼

  • information_schema數據庫是MySQL自帶的，它提供瞭訪問數據庫元數據的方式。
  • 元數據：元數據是關於數據的數據，如數據庫名或表名，列的數據類型，或訪問權限等。有些時候用於表述該信息的其他術語包括“數據字典”和“系統目錄”。
  • 在MySQL中，把information_schema看作是一個數據庫，確切說是信息數據庫。其中保存著關於MySQL服務器所維護的所有其他數據庫的信息。如數據庫名，數據庫的表，表欄的數據類型與訪問權限等。
  • information_schema數據庫表說明：

　　schemata表：提供瞭當前mysql實例中所有數據庫的信息。是show databases的結果取之此表。

　　tables表：提供瞭關於數據庫中的表的信息（包括視圖）。詳細表述瞭某個表屬於哪個schema，表類型，表引擎，創建時間等信息。是show tables from schemaname的結果取之此表。

　　columns表：提供瞭表中的列信息。詳細表述瞭某張表的所有列以及每個列的信息。是show columns from schemaname.tablename的結果取之此表。

　　statistics表：提供瞭關於表索引的信息。是show index from schemaname.tablename的結果取之此表。

　　user_privileges（用戶權限表）表：給出瞭關於用戶權限的信息。該信息源自mysql.user授權表。是非標準表。

　　schema_privileges（方案權限表）表：給出瞭關於方案（數據庫）權限的信息。該信息來自mysql.db授權表。是非標準表。

　　table_privileges（表權限）表：給出瞭關於表權限的信息。該信息源自mysql.tables_priv授權表。是非標準表。

　　column_privileges（列權限）表：給出瞭關於列權限的信息。該信息源自mysql.columns_priv授權表。是非標準表。

　　character_sets（字符集）表：提供瞭mysql實例可用字符集的信息。是show character set結果集取之此表。

　　collations表：提供瞭關於各字符集的對照信息。

　　collation_character_set_applicability表：指明瞭可用於校對的字符集。這些列等效於show collation的前兩個顯示字段。

　　table_constraints表：描述瞭存在約束的表。以及表的約束類型。

　　key_column_usage表：描述瞭具有約束的鍵列。

　　routines表：提供瞭關於存儲子程序（存儲程序和函數）的信息。此時，routines表不包含自定義函數（UDF）。名為“mysql.proc name”的列指明瞭對應於information_schema.routines表的mysql.proc表列。

　　views表：給出瞭關於數據庫中的視圖的信息。需要有show views權限，否則無法查看視圖信息。

　　triggers表：提供瞭關於觸發程序的信息。必須有super權限才能查看該表。

select * from information_schema.schemata;
select * from information_schema.tables;
select * from information_schema.columns;
select * from information_schema.statistics;
select * from information_schema.user_privileges;
select * from information_schema.schema_privileges;
select * from information_schema.table_privileges;
select * from information_schema.column_privileges;
select * from information_schema.character_sets;
select * from information_schema.collations;
select * from information_schema.collation_character_set_applicability;
select * from information_schema.table_constraints;
select * from information_schema.key_column_usage;
select * from information_schema.routines;
select * from information_schema.views;
select * from information_schema.triggers;

2 根據庫名獲取所有表的信息

【information_schema.`TABLES`】

select * from information_schema. tables where table_schema = '庫名';

3 根據庫名獲取所有的字段信息

【information_schema.`COLUMNS`】

select * from information_schema. columns

4 根據庫名獲取所有的表和表字段的基本信息

select c.table_schema             as '庫名',
       t.table_name               as '表名',
       t.table_comment            as '表註釋',
       c.column_name              as '列名',
       c.column_comment           as '列註釋',
       c.ordinal_position         as '列的排列順序',
       c.column_default           as '默認值',
       c.is_nullable              as '是否為空',
       c.data_type                as '數據類型',
       c.character_maximum_length as '字符最大長度',
       c.numeric_precision        as '數值精度(最大位數)',
       c.numeric_scale            as '小數精度',
       c.column_type              as 列類型,
       c.column_key 'KEY',
       c.extra                    as '額外說明'
  from information_schema. tables t
  left join information_schema. columns c
    on t.table_name = c.table_name
   and t.table_schema = c.table_schema
 where t.table_schema = 'dvmdm'
 order by c.table_name, c.ordinal_position;

5 查詢某個字段在多個數據庫表中的分佈

select group_concat(table_schema separator ',') as '庫名',
       group_concat(table_name separator ',') as '表名',
       column_name as '列名',
       group_concat(column_type separator ',') as '列類型',
       group_concat(column_comment separator ',') as '註釋'
  from information_schema. columns
 where table_schema in ('庫1', '庫2', '庫3')
 group by column_name
 order by group_concat(table_schema separator ','), column_name

到此這篇關於Mysql查詢所有表和字段信息的方法的文章就介紹到這瞭,更多相關mysql查詢所有表和字段信息內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：