元数据--MySQL获取元数据的方法
浏览数：26 /
时间：2015年06月12日
元数据：数据的数据，用以描述数据的信息也是数据，被称为元数据
[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)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
