MySQL的元数据

元数据访问方式介绍

查询 INFORMATION_SCHEMA 数据库表。

– 其中包含 MySQL 数据库服务器所管理的所有对象的相关数据

• 使用 SHOW 语句。

　　– 用于获取数据库和表信息的 MySQL 专用语句

• 使用 DESCRIBE(或 DESC)语句。

　　– 用于检查表结构和列属性的快捷方式

• 使用 mysqlshow 客户端程序。

　　– SHOW 语法的命令行程序

INFORMATION_SCHEMA 数据库介绍

• 充当数据库元数据的中央系统信息库

　　– 模式和模式对象

　　– 服务器统计信息(状态变量、设置、连接)

• 采用表格式以实现灵活访问

　　– 使用任意 SELECT 语句

• 是“虚拟数据库”

　　– 表并非“真实”表(基表)，而是“系统视图”

　　– 根据当前用户的特权动态填充表

注意：为什么说是虚拟数据库。因为在数据库的data目录下并不存在这个数据库information_schema，而是在数据库启动之后生成"系统视图"。也是为了保护数据库的安全。

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_METRICS                        |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_INDEXES                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_CMPMEM                         |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_TABLESPACES                |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_CONFIG                      |
+---------------------------------------+
59 rows in set (0.00 sec)

我们将来需要关注的是跟INNODB相关的，比如跟事务、锁、数据库对象相关的表

使用命令查询元数据

# 在tables查找数据名为world的表名、引擎。

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';

# 在clolums中查找数据类型为set的数据库名、表名、列名

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'set';

#查看每个字符集默认的校验规则

SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE IS_DEFAULT = 'Yes';

# 统计下所有库下的所有表的个数

SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;

# 由于是虚拟的表只能查询，不能修改和删除。所以删除会报错！

DELETE FROM INFORMATION_SCHEMA.VIEWS;

使用 INFORMATION_SCHEMA 表获取有关创建 shell 命令的信息

将 SELECT 和 CONCAT 一起使用以创建 mysqldump 脚本

select concat("mysqldump -uroot -poldboy123  ",
            table_schema," ",table_name,">>","/backup/",
            table_schema,"_",table_name,".bak.sql") 
            from information_schema.tables 
            where table_schema='world';

直接生成了备份world库所有表的mysqldump的语句,执行结果如下：

使用 mysql 命令生成创建 SQL 语句

SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
			 TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
			 TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
			 WHERE TABLE_SCHEMA = 'world';

直接生成创建world库中所有表的备份表的表结构的语句，执行结果如下：

SHOW 语句

show databases
show create database oldboy
show tables
show create table t1

SHOW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的所有表
SHOW COLUMNS FROM <table_name>：显示表的列结构
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值

mysqlshow用命令行查询数据库信息

[root@minion_1 ~]# mysqlshow -p123456
Warning: Using a password on the command line interface can be insecure.
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| test               |
| world              |
+--------------------+