中繼資料--MySQL擷取中繼資料的方法

標籤：

中繼資料：資料的資料，用以描述資料的資訊也是資料，被稱為中繼資料  [MySQL]擷取中繼資料的方法 MySQL提供了以下三種方法用於擷取資料庫物件的中繼資料：1）show語句2）從INFORMATION_SCHEMA資料庫裡查詢相關表（information_schema是一個虛擬資料庫，並不物理存在，它儲存資料的資訊的資料庫）3）命令列程式，如mysqlshow, mysqldump  --用SHOW語句擷取中繼資料 MySQL用show語句擷取中繼資料是最常用的方法，下面提供了幾種典型用法： [sql]show databases;  --列出所有資料庫show create database db_name;  --查看資料庫的DDLshow tables; --列出預設資料庫的所有表show tables from db_name;  --列出指定資料庫的所有表show table status;  --查看錶的描述性資訊show table status from db_name;show create table tbl_name;  --查看錶的DDLshow columns from tbl_name;  --查看列資訊show index from tbl_name;  --查看索引資訊  有幾種show語句還可以帶有一條like ‘pattern‘字句，用來限制語句的輸出範圍，其中‘pattern‘允許包含‘%‘和‘_‘萬用字元，比如下面這條語句返回domaininfo表中以s開頭的所有列： [sql]show columns from domaininfo like ‘s%‘;  像上面這張支援like字句的所有show都可以改寫成一條where字句，如： [sql]show columns from domaininfo where field=‘sysdomain‘;  註：desc tbl_name和explain tbl_name的效果和show columns from tbl_name一致。  --從INFORMATION_SCHEMA資料庫裡查詢相關表 INFORMATION_SCHEMA是MySQL內建的一個系統資料庫，它裡面儲存了所有的中繼資料，通過select裡面的相關表就可以擷取你想要的中繼資料。和show語句相比，它比較麻煩，但它的好處是標準的SQL語句，更具有可移植性，且更靈活，可以通過各種運算式擷取你真正需要的資訊。 從命令列擷取中繼資料前面兩種方法都必須得在MySQL命令列裡執行，而mysqlshow和mysqldump提供了從OS命令列擷取中繼資料庫的方法，如： [plain]mysqlshow  --列出所有資料庫mysqlshow db_name  --列出給定資料庫的所有表mysqlshow db_name tbl_name  --列出給定資料庫表的所有列mysqlshow --keys db_name tbl_name  --列出索引資訊mysqlshow --status db_name  --列出資料庫的描述性資訊mysqldump可以讓你看到create table語句（就想show create table語句一樣），如：[sql]mysqldump --no-data db_name [tbl_name] ...  *注意：在用mysqldump查看錶結構時，一定要加上--no-data，否則你看到的將是資料庫表裡的資料。   --MySql資料庫資訊information_schema的查詢使用從MySQL 5開始, 你可以看到多了一個系統資料庫information_schema . information_schema 存貯了其他所有資料庫的資訊。讓我們來看看幾個使用這個資料庫的例子： <!--more-->1. 取得關於 information_schema的基本資料 information_schema是一個虛擬資料庫，並不物理存在，在select的時候，從其他資料庫擷取相應的資訊。     mysql> show databases;      +--------------------+      | Database           |      +--------------------+      | information_schema |      | bugs               |      | mysql              |      | sugarcrm           |      +--------------------+      4 rows in set (0.00 sec)     以下是information_schema資料庫中的表.      mysql> use information_schema;      mysql> show tables;      +---------------------------------------+      | Tables_in_information_schema          |      +---------------------------------------+      | CHARACTER_SETS                        |      | COLLATIONS                            |      | COLLATION_CHARACTER_SET_APPLICABILITY |      | COLUMNS                               |      | COLUMN_PRIVILEGES                     |      | KEY_COLUMN_USAGE                      |      | PROFILING                             |      | ROUTINES                              |      | SCHEMATA                              |      | SCHEMA_PRIVILEGES                     |      | STATISTICS                            |      | TABLES                                |      | TABLE_CONSTRAINTS                     |      | TABLE_PRIVILEGES                      |      | TRIGGERS                              |      | USER_PRIVILEGES                       |      | VIEWS                                 |      +---------------------------------------+      17 rows in set (0.00 sec)   2. 查詢表中資料超過1000行的表     以下的語句可以查出超過1000行資料的表           mysql> select concat(table_schema,‘.‘,table_name) as table_name,table_rows          -> from information_schema.tables where table_rows > 1000          -> order by table_rows desc;           +----------------------------------+------------+      | table_name                       | table_rows |      +----------------------------------+------------+      | bugs.series_data                 |      52778 |      | bugs.bugs_activity               |      26436 |      | bugs.longdescs                   |      21473 |      | bugs.email_setting               |       5370 |      | bugs.attachments                 |       4714 |      | bugs.attach_data                 |       4651 |      | bugs.cc                          |       4031 |      | bugs.bugs                        |       2190 |      | bugs.namedqueries_link_in_footer |       1228 |      +----------------------------------+------------+      9 rows in set (0.04 sec)   3. 查詢所有沒有主鍵的表     This example gives a list of all the tables without primary key.           SELECT CONCAT(t.table_name,".",t.table_schema) as table_name      FROM information_schema.TABLES t      LEFT JOIN information_schema.TABLE_CONSTRAINTS tc      ON t.table_schema = tc.table_schema      AND t.table_name = tc.table_name      AND tc.constraint_type = ‘PRIMARY KEY‘      WHERE tc.constraint_name IS NULL      AND t.table_type = ‘BASE TABLE‘;   4. 實現錶的歷史資料information_schema Putting the MySQL information_schema to Use article implements a history database using the information schema. The first half of this article describes the requirements for the history database, and a generic design to implement it. The second half describes the stepwise construction of code-generator that creates the SQL to construct and load the history database. The code-generator is driven by the information schema and some features of the information schema are discussed in detail.   5. 查詢5個最大表     mysql> SELECT concat(table_schema,‘.‘,table_name) table_name,          -> concat(round(data_length/(1024*1024),2),‘M‘) data_length          -> FROM information_schema.TABLES          -> ORDER BY data_length DESC LIMIT 5;           +--------------------+-------------+      | table_name         | data_length |      +--------------------+-------------+      | bugs.attach_data   | 706.89M     |      | bugs.longdescs     | 3.45M       |      | bugs.bugs_activity | 1.45M       |      | bugs.series_data   | 0.75M       |      | bugs.attachments   | 0.51M       |      +--------------------+-------------+      5 rows in set (0.05 sec)   

中繼資料--MySQL擷取中繼資料的方法