元数据--MySQL获取元数据的方法

元数据：数据的数据，用以描述数据的信息也是数据，被称为元数据

 

[MySQL]获取元数据的方法

 

MySQL提供了以下三种方法用于获取数据库对象的元数据：

1）show语句

2）从INFORMATION_SCHEMA数据库里查询相关表（information_schema是一个虚拟数据库，并不物理存在，它储存数据的信息的数据库）

3）命令行程序，如mysqlshow, mysqldump

 

--用SHOW语句获取元数据

MySQL用show语句获取元数据是最常用的方法，下面提供了几种典型用法：

 

[sql]

show databases;  --列出所有数据库

show create database db_name;  --查看数据库的DDL

show tables; --列出默认数据库的所有表

show tables from db_name;  --列出指定数据库的所有表

show table status;  --查看表的描述性信息

show table status from db_name;

show create table tbl_name;  --查看表的DDL

show 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)  

 

转载于:https://www.cnblogs.com/jimmyChou/p/4285631.html