MySQL 元数据 - MySQL 教程 | 小傅哥技术教程

元数据概述

元数据是描述数据的数据，在 MySQL 中，元数据包含了数据库、表、列、索引、约束等结构信息。MySQL 提供了多种方式来查询和获取这些元数据信息。

📊 INFORMATION_SCHEMA

标准的元数据视图，提供数据库结构信息

🔧 SHOW 语句

MySQL 特有的元数据查询命令

📈 PERFORMANCE_SCHEMA

性能监控和诊断信息

⚙️ SYS 模式

简化的性能监控视图

                    元数据的用途：
                    数据库结构分析和文档生成
自动化脚本和工具开发
数据库迁移和同步
性能监控和优化
安全审计和权限管理
数据字典维护

                

INFORMATION_SCHEMA 数据库

查看所有元数据表

-- 查看 INFORMATION_SCHEMA 中的所有表
SELECT TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME;

-- 查看常用的元数据表
SELECT TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND TABLE_NAME IN (
    'SCHEMATA', 'TABLES', 'COLUMNS', 'STATISTICS', 
    'KEY_COLUMN_USAGE', 'TABLE_CONSTRAINTS', 'VIEWS'
  )
ORDER BY TABLE_NAME;
                

数据库信息查询

-- 查看所有数据库
SELECT 
    SCHEMA_NAME as database_name,
    DEFAULT_CHARACTER_SET_NAME as charset,
    DEFAULT_COLLATION_NAME as collation
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY SCHEMA_NAME;

-- 查看当前数据库信息
SELECT 
    SCHEMA_NAME as current_database,
    DEFAULT_CHARACTER_SET_NAME as charset,
    DEFAULT_COLLATION_NAME as collation
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

-- 统计每个数据库的表数量
SELECT 
    TABLE_SCHEMA as database_name,
    COUNT(*) as table_count,
    SUM(CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 1 ELSE 0 END) as base_tables,
    SUM(CASE WHEN TABLE_TYPE = 'VIEW' THEN 1 ELSE 0 END) as views
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY TABLE_SCHEMA
ORDER BY table_count DESC;
                

表信息查询

-- 查看当前数据库的所有表
SELECT 
    TABLE_NAME as table_name,
    TABLE_TYPE as type,
    ENGINE as storage_engine,
    TABLE_ROWS as estimated_rows,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    TABLE_COMMENT as comment,
    CREATE_TIME as created_at,
    UPDATE_TIME as updated_at
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY size_mb DESC;

-- 查看表的详细存储信息
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) as data_size_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) as index_size_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as total_size_mb,
    ROUND(INDEX_LENGTH / DATA_LENGTH * 100, 2) as index_ratio_percent
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND DATA_LENGTH > 0
ORDER BY total_size_mb DESC;

-- 查找大表（超过指定大小）
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND (DATA_LENGTH + INDEX_LENGTH) > 10 * 1024 * 1024  -- 大于10MB
ORDER BY size_mb DESC;
                

列信息查询

-- 查看表的所有列信息
SELECT 
    COLUMN_NAME as column_name,
    DATA_TYPE as data_type,
    COLUMN_TYPE as full_type,
    IS_NULLABLE as nullable,
    COLUMN_DEFAULT as default_value,
    EXTRA as extra_info,
    COLUMN_COMMENT as comment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'  -- 替换为实际表名
ORDER BY ORDINAL_POSITION;

-- 查找所有包含特定列名的表
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME LIKE '%email%'
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 查找所有自增列
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND EXTRA LIKE '%auto_increment%'
ORDER BY TABLE_NAME;

-- 统计数据类型使用情况
SELECT 
    DATA_TYPE,
    COUNT(*) as usage_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()), 2) as percentage
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY DATA_TYPE
ORDER BY usage_count DESC;
                

索引和约束信息

索引信息查询

-- 查看表的所有索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX as position,
    NON_UNIQUE,
    INDEX_TYPE,
    COMMENT
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'  -- 替换为实际表名
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 查找复合索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COUNT(*) as column_count,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as columns
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) > 1
ORDER BY TABLE_NAME, column_count DESC;

-- 查找没有索引的表
SELECT 
    t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s 
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA 
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND s.TABLE_NAME IS NULL;

-- 统计索引使用情况
SELECT 
    TABLE_NAME,
    COUNT(DISTINCT INDEX_NAME) as index_count,
    SUM(CASE WHEN NON_UNIQUE = 0 THEN 1 ELSE 0 END) as unique_indexes,
    SUM(CASE WHEN NON_UNIQUE = 1 THEN 1 ELSE 0 END) as non_unique_indexes
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME
ORDER BY index_count DESC;
                

约束信息查询

-- 查看所有约束
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    CONSTRAINT_TYPE,
    ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;

-- 查看主键信息
SELECT 
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE tc.TABLE_SCHEMA = DATABASE()
  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_NAME, kcu.ORDINAL_POSITION;

-- 查看外键关系
SELECT 
    kcu.TABLE_NAME as child_table,
    kcu.COLUMN_NAME as child_column,
    kcu.REFERENCED_TABLE_NAME as parent_table,
    kcu.REFERENCED_COLUMN_NAME as parent_column,
    rc.CONSTRAINT_NAME,
    rc.UPDATE_RULE,
    rc.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    AND kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME;

-- 查看唯一约束
SELECT 
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION) as columns
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE tc.TABLE_SCHEMA = DATABASE()
  AND tc.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY tc.TABLE_NAME, tc.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME;
                

SHOW 语句

基本 SHOW 语句

-- 显示所有数据库
SHOW DATABASES;

-- 显示当前数据库的所有表
SHOW TABLES;

-- 显示表结构
SHOW COLUMNS FROM employees;
-- 或者使用简写
DESC employees;
DESCRIBE employees;

-- 显示表的创建语句
SHOW CREATE TABLE employees;

-- 显示表的索引
SHOW INDEX FROM employees;

-- 显示表状态
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'employees';

-- 显示所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';
                

高级 SHOW 语句

-- 显示服务器状态
SHOW STATUS;
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Slow_queries';

-- 显示系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'max_connections';

-- 显示进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 显示存储引擎
SHOW ENGINES;

-- 显示字符集
SHOW CHARACTER SET;
SHOW COLLATION;

-- 显示权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

-- 显示错误和警告
SHOW ERRORS;
SHOW WARNINGS;

-- 显示二进制日志
SHOW BINARY LOGS;
SHOW MASTER STATUS;
                

条件过滤的 SHOW 语句

-- 使用 LIKE 过滤
SHOW TABLES LIKE 'emp%';
SHOW VARIABLES LIKE 'innodb_buffer%';
SHOW STATUS LIKE 'Com_select';

-- 使用 WHERE 子句（MySQL 5.7+）
SHOW COLUMNS FROM employees WHERE Field LIKE '%name%';
SHOW TABLE STATUS WHERE Engine = 'InnoDB';

-- 显示特定数据库的表
SHOW TABLES FROM mysql;
SHOW TABLES IN information_schema LIKE 'TABLE%';
                

视图和存储过程元数据

视图信息查询

-- 查看所有视图
SELECT 
    TABLE_NAME as view_name,
    VIEW_DEFINITION as definition,
    CHECK_OPTION,
    IS_UPDATABLE,
    DEFINER,
    SECURITY_TYPE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- 查看视图的列信息
SELECT 
    TABLE_NAME as view_name,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = DATABASE()
  )
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 创建示例视图用于演示
CREATE VIEW employee_summary AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees
WHERE status = 'active'
GROUP BY department;

-- 查看视图定义
SHOW CREATE VIEW employee_summary;
                

存储过程和函数信息

-- 查看所有存储过程和函数
SELECT 
    ROUTINE_NAME as name,
    ROUTINE_TYPE as type,
    DATA_TYPE as return_type,
    ROUTINE_DEFINITION as definition,
    DEFINER,
    CREATED,
    LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE();

-- 查看存储过程参数
SELECT 
    SPECIFIC_NAME as routine_name,
    PARAMETER_NAME,
    PARAMETER_MODE,
    DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = DATABASE()
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION;

-- 显示存储过程
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- 创建示例存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = dept_name AND status = 'active'
    ORDER BY salary DESC;
END //
DELIMITER ;

-- 查看存储过程定义
SHOW CREATE PROCEDURE GetEmployeesByDepartment;
                

性能相关元数据

PERFORMANCE_SCHEMA 查询

-- 查看表的 I/O 统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = DATABASE()
ORDER BY (COUNT_READ + COUNT_WRITE) DESC;

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
  AND INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
ORDER BY OBJECT_NAME;

-- 查看语句统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as execution_count,
    AVG_TIMER_WAIT / 1000000000 as avg_time_seconds,
    SUM_TIMER_WAIT / 1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY COUNT_STAR DESC
LIMIT 10;
                

SYS 模式查询

-- 查看表和索引大小（需要 SYS 模式）
SELECT 
    table_schema,
    table_name,
    total_size,
    data_size,
    index_size
FROM sys.schema_table_statistics
WHERE table_schema = DATABASE()
ORDER BY total_size DESC;

-- 查看未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- 查看冗余索引
SELECT 
    table_schema,
    table_name,
    redundant_index_name,
    redundant_index_columns,
    dominant_index_name,
    dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- 查看表访问统计
SELECT 
    table_schema,
    table_name,
    rows_fetched,
    rows_inserted,
    rows_updated,
    rows_deleted,
    io_read_requests,
    io_write_requests
FROM sys.schema_table_statistics_with_buffer
WHERE table_schema = DATABASE()
ORDER BY (rows_fetched + rows_inserted + rows_updated + rows_deleted) DESC;
                

实用的元数据查询

数据库健康检查

-- 数据库大小统计
SELECT 
    table_schema as database_name,
    COUNT(*) as table_count,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as total_size_mb,
    ROUND(SUM(data_length) / 1024 / 1024, 2) as data_size_mb,
    ROUND(SUM(index_length) / 1024 / 1024, 2) as index_size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema
ORDER BY total_size_mb DESC;

-- 查找没有主键的表
SELECT 
    t.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 t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND tc.CONSTRAINT_NAME IS NULL;

-- 查找可能需要优化的表（数据大但索引少）
SELECT 
    t.TABLE_NAME,
    t.TABLE_ROWS,
    ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    COUNT(DISTINCT s.INDEX_NAME) as index_count,
    ROUND(t.INDEX_LENGTH / t.DATA_LENGTH * 100, 2) as index_ratio_percent
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_ROWS > 1000
GROUP BY t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH
HAVING index_count < 3 OR index_ratio_percent < 10
ORDER BY size_mb DESC;
                

数据字典生成

-- 生成完整的数据字典
SELECT 
    t.TABLE_NAME as '表名',
    t.TABLE_COMMENT as '表说明',
    c.COLUMN_NAME as '字段名',
    c.COLUMN_TYPE as '数据类型',
    CASE WHEN c.IS_NULLABLE = 'YES' THEN '是' ELSE '否' END as '允许空值',
    IFNULL(c.COLUMN_DEFAULT, '') as '默认值',
    c.EXTRA as '额外信息',
    c.COLUMN_COMMENT as '字段说明',
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN '是' ELSE '' END as '主键',
    CASE WHEN uk.COLUMN_NAME IS NOT NULL THEN '是' ELSE '' END as '唯一键'
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN (
    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN (
    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
    WHERE tc.CONSTRAINT_TYPE = 'UNIQUE'
) uk ON c.TABLE_SCHEMA = uk.TABLE_SCHEMA
    AND c.TABLE_NAME = uk.TABLE_NAME
    AND c.COLUMN_NAME = uk.COLUMN_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;
                

依赖关系分析

-- 查看表之间的外键依赖关系
WITH RECURSIVE table_dependencies AS (
    -- 基础查询：没有依赖的表
    SELECT 
        TABLE_NAME,
        0 as level,
        CAST(TABLE_NAME AS CHAR(1000)) as path
    FROM INFORMATION_SCHEMA.TABLES t
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_TYPE = 'BASE TABLE'
      AND NOT EXISTS (
          SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
          WHERE kcu.TABLE_SCHEMA = DATABASE()
            AND kcu.TABLE_NAME = t.TABLE_NAME
            AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
      )
    
    UNION ALL
    
    -- 递归查询：有依赖的表
    SELECT 
        kcu.TABLE_NAME,
        td.level + 1,
        CONCAT(td.path, ' -> ', kcu.TABLE_NAME)
    FROM table_dependencies td
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON td.TABLE_NAME = kcu.REFERENCED_TABLE_NAME
        AND kcu.TABLE_SCHEMA = DATABASE()
    WHERE td.level < 10  -- 防止无限递归
      AND FIND_IN_SET(kcu.TABLE_NAME, REPLACE(td.path, ' -> ', ',')) = 0
)
SELECT 
    level as dependency_level,
    TABLE_NAME,
    path as dependency_path
FROM table_dependencies
ORDER BY level, TABLE_NAME;

-- 简化版本：直接查看外键关系
SELECT 
    kcu.TABLE_NAME as child_table,
    kcu.COLUMN_NAME as child_column,
    kcu.REFERENCED_TABLE_NAME as parent_table,
    kcu.REFERENCED_COLUMN_NAME as parent_column,
    'depends on' as relationship
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.REFERENCED_TABLE_NAME, kcu.TABLE_NAME;
                

元数据的实际应用

自动化脚本示例

-- 生成备份脚本的元数据查询
SELECT 
    CONCAT(
        'mysqldump -u username -p ',
        TABLE_SCHEMA, ' ',
        TABLE_NAME, ' > ',
        TABLE_NAME, '_backup.sql;'
    ) as backup_command
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- 生成表结构比较脚本
SELECT 
    TABLE_NAME,
    CONCAT(
        'SELECT "', TABLE_NAME, '" as table_name, ',
        'COUNT(*) as column_count, ',
        'GROUP_CONCAT(CONCAT(COLUMN_NAME, ":", DATA_TYPE) ORDER BY ORDINAL_POSITION) as structure ',
        'FROM INFORMATION_SCHEMA.COLUMNS ',
        'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "', TABLE_NAME, '";'
    ) as structure_query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- 生成索引分析报告
SELECT 
    TABLE_NAME,
    CONCAT(
        'ANALYZE TABLE ', TABLE_NAME, '; ',
        'SHOW INDEX FROM ', TABLE_NAME, ';'
    ) as analysis_command
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;
                

监控和报警查询

-- 监控表大小增长
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as current_size_mb,
    UPDATE_TIME as last_updated,
    CASE 
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 100 * 1024 * 1024 THEN 'LARGE'
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 50 * 1024 * 1024 THEN 'MEDIUM'
        ELSE 'SMALL'
    END as size_category
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 检查表的健康状况
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    CASE 
        WHEN UPDATE_TIME IS NULL THEN 'NO_UPDATES'
        WHEN UPDATE_TIME < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 'STALE'
        WHEN UPDATE_TIME < DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 'OLD'
        ELSE 'RECENT'
    END as update_status,
    CASE 
        WHEN TABLE_ROWS = 0 THEN 'EMPTY'
        WHEN TABLE_ROWS < 1000 THEN 'SMALL'
        WHEN TABLE_ROWS < 100000 THEN 'MEDIUM'
        ELSE 'LARGE'
    END as row_count_category
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY size_mb DESC;
                

最佳实践和注意事项

                    最佳实践：
                    定期查询元数据以监控数据库健康状况
使用元数据自动生成文档和报告
通过元数据分析优化数据库性能
建立基于元数据的监控和报警机制
使用元数据验证数据库结构的一致性
在数据迁移时利用元数据确保完整性

                

注意事项：

INFORMATION_SCHEMA 查询可能对性能有影响
某些元数据信息可能不是实时更新的
不同 MySQL 版本的元数据结构可能有差异
PERFORMANCE_SCHEMA 需要启用才能使用
元数据查询权限可能受到限制
大型数据库的元数据查询可能较慢

应用场景	推荐方法	注意事项
结构分析	INFORMATION_SCHEMA	查询可能较慢
快速查看	SHOW 语句	输出格式固定
性能监控	PERFORMANCE_SCHEMA	需要启用相关功能
自动化脚本	组合查询	考虑权限和性能
文档生成	格式化查询	定期更新

-- 清理示例对象
DROP VIEW IF EXISTS employee_summary;
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;

SELECT 'Metadata tutorial completed. Example objects cleaned up.' AS message;
                

🔍 MySQL 元数据

元数据概述

📊 INFORMATION_SCHEMA

🔧 SHOW 语句

📈 PERFORMANCE_SCHEMA

⚙️ SYS 模式

INFORMATION_SCHEMA 数据库

查看所有元数据表

数据库信息查询

表信息查询

列信息查询

索引和约束信息

索引信息查询

约束信息查询

SHOW 语句

基本 SHOW 语句

高级 SHOW 语句

条件过滤的 SHOW 语句

视图和存储过程元数据

视图信息查询

存储过程和函数信息

性能相关元数据

PERFORMANCE_SCHEMA 查询

SYS 模式查询

实用的元数据查询

数据库健康检查

数据字典生成

依赖关系分析

元数据的实际应用

自动化脚本示例

监控和报警查询

最佳实践和注意事项