数据库-元数据表
1. 什么是元数据表
元数据：数据的数据，用以描述数据的信息也是数据，被称为元数据
2. 获取元数据的方法
MySQL提供了以下三种方法用于获取数据库对象的元数据：
-
show语句
-
从INFORMATION_SCHEMA数据库里查询相关表（information_schema是一个虚拟数据库，并不物理存在，它储存数据的信息的数据库）
-
命令行程序，如mysqlshow, mysqldump
3. SHOW语句获取元数据
| 语句 | 作用 |
|---|---|
| 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开头的所有列：
SHOW COLUMNS FROM t_student LIKE 's%'; (EXPLAIN t_student;)
2.查看表的描述信息
SHOW TABLE STATUS FROM test LIKE 't_student'
-
-
Name * 作用：显示表的名称。这是用于唯一标识数据库中的每个表，通过表名可以在后续的查询、修改等操作中准确地引用该表 * 示例：如果有一个名为
customers的表，在SHOW TABLE STATUS的结果中，Name列会显示customers，可以通过这个名称来明确是针对哪个表的信息 -
Engine
-
作用：表示表所使用的存储引擎。常见的存储引擎有 InnoDB、MyISAM 等。不同的存储引擎具有不同的特性，例如 InnoDB 支持事务处理和外键约束，而 MyISAM 在一些简单的读写场景下可能具有更高的性能。
-
示例：若
Engine列显示为InnoDB，说明该表使用 InnoDB 存储引擎，这意味着在这个表上可以进行事务操作，如使用START TRANSACTION、COMMIT和ROLLBACK语句来控制数据的一致性
-
-
Version
-
作用：存储表的版本信息。这个版本信息通常是由 MySQL 内部用于管理表结构的更新和变化等情况，一般用户很少直接使用这个列的值
-
示例：在 MySQL 进行表结构升级等操作时，
Version列的值可能会发生改变，以记录表的更新次数或版本号等相关信息
-
-
Row_format
-
作用：指定表中行的存储格式。常见的行格式有 Compact、Dynamic 等。不同的行格式在存储效率和数据处理方式上有所不同。例如，Compact 格式对于存储空间的利用较为高效，而 Dynamic 格式在处理可变长度列较多的情况下可能更灵活
-
示例：如果
Row_format列显示为Compact，说明该表的行采用 Compact 格式存储，这种格式会对数据进行紧凑的存储，减少存储空间的占用，特别是对于包含变长字段（如 VARCHAR 类型）的表
-
-
Rows
-
作用：这是一个估计值，表示表中的行数。需要注意的是，这个值可能不是完全精确的，尤其是在对表进行频繁的插入、删除等操作后，MySQL 可能没有及时更新这个估计值
-
示例：如果
Rows列显示为1000，这大致表示该表中可能有 1000 行数据。可以用这个值来初步了解表的规模，例如在对数据量较大的表进行查询优化时，会考虑这个因素
-
-
Avg_row_length
-
作用：计算表中平均每行的长度（字节数）。这个值是通过表的总字节数除以估计的行数得到的。它可以帮助你了解数据在表中的存储密度等信息
-
示例：如果
Avg_row_length列显示为100字节，且Rows列显示为1000，那么可以大致估计出该表占用的存储空间约为100×1000 = 100000字节
-
-
Data_length
-
作用：表示表的数据部分的长度（字节数），即存储表中实际数据所占用的空间大小。这个值不包括索引等其他部分的存储空间
-
示例：若
Data_length列显示为50000字节，这就是表中数据本身占用的空间大小，可以用来评估数据存储的规模和效率
-
-
Max_data_length
-
作用：指定表所能容纳的最大数据长度（字节数）。这个限制取决于表的存储引擎和配置等因素。例如，对于某些存储引擎，这个值可能受到文件系统的文件大小限制或者存储引擎本身的内部限制
-
示例：如果
Max_data_length列显示为1073741824字节（1GB），这表示在当前存储引擎和配置下，该表最多可以存储 1GB 的数据部分，超过这个限制可能需要考虑对表进行优化或者扩展存储
-
-
Index_length
-
作用：表示表中索引部分的长度（字节数）。索引是用于提高查询速度的一种数据结构，这个列的值可以帮助你了解索引占用的存储空间情况
-
示例：若
Index_length列显示为20000字节，这说明表的索引总共占用了 20000 字节的存储空间。可以通过这个值与数据长度等进行比较，来评估索引的规模是否合理
-
-
Data_free
-
作用：显示表中已经分配但目前尚未使用的空间（字节数）。这部分空间可以用于后续的数据插入等操作，直到用完后可能需要重新分配空间
-
示例：如果
Data_free列显示为1000字节，这表示表中有 1000 字节的空间已经分配但还没有被数据占用，可以用于存储新插入的数据
-
-
Auto_increment
-
作用：如果表中有一个自增列（通常是一个整数类型的主键），这个列会显示自增列的下一个可用值。它用于自动为新插入的行生成唯一的标识符
-
示例：假设表中有一个名为
id的自增主键列，Auto_increment列显示为101，这意味着下一次插入新行时，id列的值将自动设置为 101
-
-
Create_time
-
作用：记录表的创建时间。这个时间戳可以帮助你了解表的历史，例如在进行数据库备份策略或者数据迁移计划时，可以参考这个时间来确定表的新旧程度
-
示例：如果
Create_time列显示为2024-01-01 10:00:00，这表示该表是在 2024 年 1 月 1 日 10 点创建的
-
-
Update_time
-
作用：表示表的最后更新时间。这个更新可能是因为数据的插入、删除或者修改等操作导致的。通过这个时间可以了解表中数据的活跃度
-
示例：若
Update_time列显示为2024-02-01 14:00:00，这意味着表中的数据最后一次更新是在 2024 年 2 月 1 日 14 点，可以用来判断数据是否是最新的，或者是否需要重新缓存表的数据等
-
-
Check_time
-
作用：用于存储表最后一次检查（如完整性检查）的时间。这个功能在一些存储引擎（如 MyISAM）中有更明显的体现，对于维护表的数据质量很重要
-
示例：在 MyISAM 存储引擎下，如果
Check_time列显示为2024-03-01 16:00:00，这表示该表最后一次完整性检查是在 2024 年 3 月 1 日 16 点
-
-
Collation
-
作用：指定表所使用的字符集校对规则。字符集校对规则决定了字符的比较和排序方式。例如，
utf8_general_ci是一种常用的校对规则，其中ci表示不区分大小写 -
示例：如果
Collation列显示为utf8_general_ci，说明在这个表中，字符数据（如 VARCHAR 类型的列）在进行比较和排序操作时，会按照不区分大小写的utf8字符集规则来执行
-
-
Checksum
-
作用：存储表的校验和信息（如果有的话）。校验和用于验证表数据的完整性，不过并不是所有的存储引擎都支持或者启用这个功能
-
示例：对于支持校验和的存储引擎，在数据完整性检查等操作中，可以参考
Checksum列的值来判断数据是否被篡改或者损坏
-
-
Create_options
-
作用：显示创建表时使用的额外选项。这些选项可能包括存储引擎特定的设置、表的分区设置等其他特殊的配置信息
-
示例：如果表是分区表，
Create_options列可能会显示分区的相关信息，如分区的类型（范围分区、列表分区等）和分区的表达式等内容
-
-
Comment
-
作用：可以用于存储对表的注释信息。这是一个自定义的字段，开发人员或者数据库管理员可以在这里添加对表的功能、用途等方面的说明
-
示例：如果在创建表时添加了注释，如
COMMENT = 'This table stores customer information'，那么在SHOW TABLE STATUS的Comment列就会显示This table stores customer information
-
-
4. INFORMATION_SCHEMA查询相关表
INFORMATION_SCHEMA是MySQL自带的一个系统数据库，它里面存储了所有的元数据，通过select里面的相关表就可以获取你想要的元数据。和show语句相比，它比较麻烦，但它的好处是标准的SQL语句，更具有可移植性，且更灵活，可以通过各种表达式获取你真正需要的信息。information_schema是一个虚拟数据库，并不物理存在，在select的时候，从其他数据库获取相应的信息
-
以下的语句可以查出超过1000行数据的表
SELECT CONCAT(table_schema,'.',table_name) AS table_name,table_rows
FROM information_schema.tables
WHERE table_rows > 1000
ORDER BY table_rows DESC;
查询所有没有主键的表
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';
查询5个最大表
SELECT
TABLE_NAME,
CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH)/(1024*1024),2),'MB') AS total_size
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'mysql' -- 将'your_database_name'替换为实际的数据库名
ORDER BY
total_size DESC
LIMIT 5;
获取指定数据库占用的磁盘空间
SELECT CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/(1024*1024), 2), 'MB') AS database_size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test';