MySQL元数据与information_schema

 2024/09/03 

元数据

mysql库和表的属性信息即元数据，都存储在”基表”中，这部分无法直接进行增删改查

只能通过专用DDL、DCL语句进行影响(修改)，比如create、drop，创建之后元数据自动就会修改了

可以通过一些专用视图和命令进行元数据查看：

information_schema中保存了大量元数据查询的视图
show命令可以使用封装好的命令进行查询

使用一个视图(别名)来定义一个复杂操作

因为复杂操作比较难记，所以可以将这个操作以一个别名的方式保存下来

SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course 
ON teacher.tno = course.tno
JOIN sc 
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;
↓
create view aa as 
SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course 
ON teacher.tno = course.tno
JOIN sc 
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;

直接查这个视图，就可以查到其的结果
select * from aa;
+--------+--------+-------+
| tname  | sname  | score |
+--------+--------+-------+
| hesw   | zhang3 |    59 |
| oldguo | li4    |    40 |
| oldguo | zh4    |    40 |
+--------+--------+-------+
3 rows in set (0.00 sec)

information_schema就是其中一个，存放了大量视图的表
use information_schema;
select * from tables; -- 能查看到所有表的元数据

information_schema

tables视图

use information_schema;
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  |     存储引擎
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     数据行行数      |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     平均行长度|       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     索引长度|       |
| 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   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

-- 显示所有库和表信息：
USE information_schema;
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES`;
-- 合并一下结果
SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;

-- 查询所有innodb引擎的表
SELECT TABLE_NAME FROM information_schema.tables
WHERE ENGINE='innodb';

-- 统计每个库的总占用大小
SELECT table_schema,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024 AS used
FROM information_schema.tables
GROUP BY table_schema 
ORDER BY used DESC; -- 排序并从大到小查看

concat函数的拼接操作

concat的作用是，可以使用concat在数据库中，生成所需要的一些命令，来完成一些需求

在concat执行完成后，只需要将生成的结果复制出来，就可以拿来做脚本，非常方便

-- 对数据库进行分库分表备份
原：mysqldump -uroot -proot school student > /BACKUP/school_student.sql
↓
SELECT CONCAT("mysqldump -uroot -proot",table_schema," ",table_name,">/backup/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;

-- 批量对school库下所有表进行操作
例:ALTER TABLE school.student DISCARD TABLESPACE;
↓
select
concat("alter table ",table_schema,".",table_name," discard tablespace;")
from information_schema.tables
where table_schema='school';