mysql获取元数据

01 元数据概念介绍：

元（meta）一般会被翻译成中文是"关于...的..."，元数据（meta data）等价于data about data，表示关于数据的数据；

一般是元数据就是结构化数据，例如存储在数据库里的数据，规定了字段的长度。类型等；

元数据就是描述数据的数据，在MySQL中就是描述database的数据，属性，状态等相关信息；

表示在数据库服务中有哪些数据库，库中有哪些表，表中有多少字段，字段是什么类型等等，这样的数据就是数据库的元数据；

元数据获取方法：

元数据获取方式一：利用命令获取（show）
元数据获取方式二：利用库中视图（information_schema）

具体案例：

元数据获取方式一：利用命令获取（show）

# 常用SQL语句的show命令查看元数据信息
mysql> show databases;
-- 查询数据库服务中的所有数据库信息（数据库名称-元数据）
​
mysql> show tables;
mysql> show tables from mysql;
-- 查询数据库服务中的相应数据表信息（数据表名称-元数据）
​
mysql> show create database <库名>;
-- 查询数据库服务中的建库语句信息 （建库语句参数-元数据 建库语句就是DDL语句，定义建立数据库的属性信息）
​
mysql> show create table <表名>;
-- 查询数据库服务中的建表语句信息 （建表语句参数-元数据 建表语句就是DDL语句，定义建立数据表的属性信息）
​
mysql> desc <表名>;
mysql> show columns from <表名>;
-- 查询数据库服务中的数据表的结构（数据表的列定义信息-元数据）
​
mysql> show table status from <库名>;
-- 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据）
mysql> show table status from world like 'city' \G
*************************** 1. row ***************************
           Name: city                 -- 数据表名称信息
         Engine: InnoDB           -- 使用的数据库引擎信息
        Version: 10
     Row_format: Dynamic
           Rows: 4046                -- 数据表的行数信息
 Avg_row_length: 101         -- 平均行长度
    Data_length: 409600       
Max_data_length: 0
   Index_length: 114688      -- 索引长度信息
      Data_free: 0
 Auto_increment: 4080       -- 自增列的值计数
    Create_time: 2022-11-04 09:13:27   -- 数据表创建时间
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci      -- 校对规则信息
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-- 查看数据库服务中的具体数据库表的状态信息（属于单库或单表查询）
​
mysql> show index from world.city;
-- 查询数据库服务中的相应数据表的索引情况（了解即可）
​
mysql> show grants for root@'localhost';
-- 查询数据库服务中的用户权限属性配置信息
​
mysql> show [full] processlist;
-- 查询数据库服务的系统状态信息，表示当前数据库的所有连接情况
​
mysql> show variables; 
mysql> show variables like '%xx%';
-- 查询数据库服务的所有配置信息
​
mysql> show status;
mysql> show status like '%lock%'; 
-- 查询数据库服务的系统整体状态，表示当前数据库服务运行的即时状态情况
​
mysql> show binary logs;
-- 查询数据库服务的所有二进制日志信息（binlog日志）
mysql> show master status;
-- 查询数据库服务正在使用的二进制日志
mysql> show binlog events in 'binlog.000009';
-- 查询数据库服务具体二进制日志内容事件信息
​
mysql> show engine innodb status \G
-- 查询数据库服务存储引擎相关信息
​
mysql> show slave hosts;
-- 在数据库服务主库查看从库信息
mysql> show slave status;
-- 查询数据库服务主从状态信息

说明：使用show语句虽然可以快速得到相应的数据库元数据信息，但是查询功能过于单一，想查询全面信息，就需要执行多条语句；

元数据获取方式二：利用库中视图（information_schema）

视图概念：

将查询基表元数据语句信息方法封装在一个变量或别名中，这个封装好的变量或别名就成为视图，视图信息都是存储在内存中的表

元数据信息存储在系统基表中，通过一般的select命令只能查看数据信息，不能查看到系统基表，以免被随意调整篡改；

而查询基表的语句过于复杂，可以将整个查询基表语句定义为一个视图信息（等价于别名/变量），调取视图等价于调取查询基表语句；

information_schema库中的内存表都是每次数据库服务启动时生成的，里面存储了查询元数据基表的视图信息；

视图定义：案例

# 假设查询基表语句信息如下

select a.tname as '老师名',group_concat(d.sname)  as '不及格学生名'
from teacher as a
join course as b
on a.tno=b.tno 
join sc as c
on b.cno=c.cno 
join student as d
on c.sno=d.sno 
where c.score<60 
group by a.tno;
-- 会获取如下查询后的信息
+-----------+--------------------+
| 老师名    | 不及格学生名       |
+-----------+--------------------+
| xiaoQ     | zhang3             |
| xiaoA     | li4,zh4            |
+-----------+--------------------+

# 可以将复杂的查询语句定义为视图

create view tv as select a.tname as '老师名',group_concat(d.sname)  as '不及格学生名'
from teacher as a
join course as b
on a.tno=b.tno 
join sc as c
on b.cno=c.cno 
join student as d
on c.sno=d.sno 
where c.score<60 
group by a.tno;

# 调取视图信息等价于调取复杂的查询语句

mysql> select * from tv;
+-----------+--------------------+
| 老师名    | 不及格学生名       |
+-----------+--------------------+
| xiaoQ     | zhang3             |
| xiaoA     | li4,zh4            |
+-----------+--------------------+
2 rows in set (0.00 sec)

视图查询：

# 切换进入information_schema数据库中查看表信息
mysql> use information_schema;
mysql> show tables;
-- 此时看到的所有表信息，其实都是视图信息
​
# 查看获取视图信息创建语句
mysql> show create view tables;
-- 查看tables这个视图表的创建过程
​
# 查看视图表信息应用
# 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息（业务相关）
mysql> desc information_schema.tables;
-- 查看information_scheam中的tables表的结构信息；
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables group by table_schema;
-- 获取相应数据库中表的个数，与数据库中拥有的表信息
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema;
​
# 统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间
mysql> select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024 from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema;
​
# 统计数据库资产信息（数据资产），获取具有碎片信息的表
mysql> select table_schema,table_name,data_free from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
-- 碎片信息过多会导致索引信息失效，以及统计信息不真实的情况
​
# 统计数据库资产信息（数据资产），处理具有碎片信息的表
mysql> alter table t1 engine=innodb;
-- 可以对已经是innodb存储引擎的表做操作，实现整理碎片功能
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
-- 可以对已经是innodb存储引擎的表做操作，实现批量整理碎片功能
​
# 统计数据库资产信息（数据资产），获取数据库中非innodb表信息
mysql>select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb';
-- 获取非innodb数据库引擎表
mysql> use school;
mysql> create table t1 (id int) engine=myisam;
mysql> create table t2 (id int) engine=myisam;
mysql> create table t3 (id int) engine=myisam;
-- 模拟创建一些myisam引擎数据表
​
# 统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb
mysql> alter table world.t1 engine=innodb;
-- 可以对不是innodb存储引擎的表做操作，实现数据表引擎修改
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and  engine !='innodb';
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and  engine!='innodb' into outfile '/tmp/alter.sql';  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
-- 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务
mysql> source /tmp/alter.sql
-- 可以对不是innodb存储引擎的表做操作，实现数据表批量化引擎修改，调用数据库脚本信息

tables视图表的结构信息：

| 序号 | 字段信息 | 解释说明 | |----|----------------|---------------| | 01 | TABLE_SCHEMA | 表示数据表所属库的名称信息 | | 02 | TABLE_NAME | 表示数据库中所有数据表名称 | | 03 | ENGINE | 表示数据库服务中的引擎信息 | | 04 | TABLE_ROWS | 表示数据库相应数据表的行数 | | 05 | AVG_ROW_LENGTH | 表示数据表中每行的平均长度 | | 06 | INDEX_LENGTH | 表示数据表中索引信息的长度 | | 07 | DATA_FREE | 表示数据库服务碎片数量信息 | | 08 | CREATE_TIME | 表示数据表创建的时间戳信息 | | 09 | UPDATE_TIME | 表示数据表修改的时间戳信息 | | 10 | TABLE_COMMENT | 表示数据表对应所有注释信息 |

说明：使用information_schema的视图查看功能，可以看到全局数据库或数据表的元数据信息，探究全局层面的元数据