SQL元数据获取

• 基于Centos7.6

1.元数据

所谓元数据，就是表示数据的数据，这些数据五花八门，总之，只要不是我们存储到数据库里的数据，大多都可以理解为元数据。
描述数据库的任何数据,作为数据库内容的对立面,是元数据。
因此，列名、数据库名、用户名、版本名以及从SHOW语句得到的结果中的大部分字符串是元数据。
还包括INFORMATION_SCHEMA数据库中的表中的内容，因为定义的那些表存储了关于数据库对象的信息。

元数据是存储在“基表”中。
通过专用的DDR/DCL语句进行修改。通过专用视图或命令进行元数据的查询。
information_schema中保存了大量元数据的视图。
show语句提供封装好的，用于元数据基本查询功能。

2.information_schema基本应用

视图：是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。
行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

例子：
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,group_concat(host) from mysql.user group by user;
+---------------+--------------------+
| user          | group_concat(host) |
+---------------+--------------------+
| mysql.session | localhost          |
| mysql.sys     | localhost          |
| root          | localhost          |
| test          | %,192.168.3.%      |
+---------------+--------------------+
4 rows in set (0.00 sec)

mysql> create view a1 as select user,group_concat(host) from mysql.user group by user;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a1;
+---------------+--------------------+
| user          | group_concat(host) |
+---------------+--------------------+
| mysql.session | localhost          |
| mysql.sys     | localhost          |
| root          | localhost          |
| test          | %,192.168.3.%      |
+---------------+--------------------+
4 rows in set (0.00 sec)

information_schema下tables视图介绍
功能：保存了整个数据库范围内所有表的元数据。

mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

其中：
TABLE_SCHEMA 表所在库名
TABLE_NAME 表名
ENGINE 存储引擎信息
TABLE_ROWS 表的行数(粗略统计)
AVG_ROW_LENGTH 平均行长度(粗略统计)
INDEX_LENGTH 索引长度(粗略统计)
DATA_FREE 碎片数
TABLE_COMMENT 表注释

例子：

1.显示所有库和表的信息
select table_schema,table_name from information_schema.tables;

2. 统计所有库,对应的表个数和名字列表 
库名       表个数       表名     
world       3           city,a,b 

select table_schema,count(*) ,group_concat(table_name) from information_schema.tables group by table_schema;

3.查询所有InnoDB引擎的表
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

4. 统计每个库的数据量大小(占用空间大小)并排序
数据量已kb为单位显示=(平均行长度*行数+索引长度)/1024
select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+index_length)/1024 as total_KB from information_schema.tables group by table_schema order by total_KB desc;

拼接语句 
配合concat()函数拼接语句或命令

1.模仿以下语句，进行数据库的分库分表备份。
mysqldump -uroot -p123 world(库名) city(表名) >/databak/world_city.sql

SELECT  concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/databak/",table_schema,"_",table_name,".sql") FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema') into outfile '/tmp/bak.sh' ;

2.模仿以下语句，进行批量生成，对world库下所有表进行操作。
alter table world.city discard tablespace;

SELECT  concat("alter table",table_schema," ",table_name,"discard tablespace") FROM information_schema.tables WHERE table_schema='world';

a. 查询一下业务数据库中,非InnoDB的表
SELECT  table_schema , table_name ,engine FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema') 
AND ENGINE <>'innodb';        

b. 将非InnoDB表批量替换为InnoDB
SELECT  concat("alter table ",table_schema,".",table_name," engine=innodb;") FROM information_schema.tables 
WHERE 
table_schema NOT IN ('mysql','sys','information_schema','performance_schema','world') 
AND ENGINE <>'innodb' into outfile '/tmp/alter.sql' ; 

3.show语句

show databases			查看数据库名
show tables			查看表名
show privileges			查看数据库整体权限用法			
show grants for xx			查看用户的权限信息 
show create database /table	xx		查看建库、表语句
show charset			查看支持的字符集
show collation			查看支持的校对信息
show processlist			查看所有用户连接情况
show variables like '%xx%'			查看参数信息
show status like ''			查看数据库整体状态信息
show relaylog events in ''			查看中继日志的事件信息
show index from xx			查看表的索引信息
show engines			查看所有支持的存储引擎类型
show binary logs			查看二进制日志的列表信息 
show binlog events in ''			查看二进制日志的事件信息
show master status			查看MySQL当前使用的二进制日志信息 
show slave status\G			查看从库状态信息  
show slave hosts			查看从库的hosts信息 
show plugins			查看插件信息 
show engine innodb status\G			查看innodb引擎详细状态信息
更多的可以用help show;查看