MySQL教程FG051-MySQL数据库/表元数据信息查看

GF-MySQL

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 什么是元数据

元数据是描述数据的数据，在MySQL中，元数据包括数据库、表、列、索引等对象的定义和属性信息。通过查看元数据，可以了解数据库的结构和配置，为数据库管理和开发提供重要参考。

1.2 元数据的重要性

了解数据库结构：查看数据库、表、列的定义
分析表结构：了解表的字段类型、索引等
优化查询：根据表结构和索引信息优化SQL语句
数据库管理：监控数据库状态和性能
数据迁移：了解表结构以便进行数据迁移

Part02-生产环境规划与建议

2.1 information_schema

information_schema是MySQL自带的系统数据库，存储了所有数据库对象的元数据信息。 02 学习交流加群风哥微信: itpux-com

# 查看information_schema数据库中的表
USE information_schema;
SHOW TABLES;

2.2 mysql

mysql数据库存储了MySQL的系统信息，包括用户权限、系统变量等。 03 学习交流加群风哥QQ113257174

# 查看mysql数据库中的表
USE mysql;
SHOW TABLES;

2.3 performance_schema

performance_schema存储了MySQL的性能相关信息，用于监控和分析MySQL的性能。

# 查看performance_schema数据库中的表
USE performance_schema;
SHOW TABLES;

2.4 sys

sys数据库是MySQL 5.7+提供的，基于information_schema和performance_schema，提供了更友好的视图来查看系统信息。 04 风哥提示：

# 查看sys数据库中的表和视图
USE sys;
SHOW TABLES;

Part03-生产环境项目实施方案

3.1 查看所有数据库

# 查看所有数据库
SHOW DATABASES;

# 查看当前数据库
SELECT DATABASE();

3.2 查看数据库创建语句

# 查看数据库创建语句
SHOW CREATE DATABASE database_name;

3.3 查看数据库字符集和排序规则

# 查看数据库字符集和排序规则
SELECT schema_name, default_character_set_name, default_collation_name 
FROM information_schema.schemata 
WHERE schema_name = 'database_name';

3.4 查看数据库大小

# 查看数据库大小
SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables 
GROUP BY table_schema;

Part04-生产案例与实战讲解

4.1 查看数据库中的表

# 查看当前数据库中的表
SHOW TABLES;

# 查看指定数据库中的表
SHOW TABLES FROM database_name;

4.2 查看表结构

# 查看表结构
DESCRIBE table_name;

# 或使用SHOW COLUMNS
SHOW COLUMNS FROM table_name;

4.3 查看表创建语句

# 查看表创建语句
SHOW CREATE TABLE table_name;

4.4 查看表的引擎和字符集

# 查看表的引擎和字符集
SHOW TABLE STATUS LIKE 'table_name';

# 或从information_schema中查询
SELECT table_name, engine, table_collation 
FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

4.5 查看表大小

# 查看表大小
SELECT table_name AS 'Table', 
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)' 
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
ORDER BY (data_length + index_length) DESC;

Part05-风哥经验总结与分享

5.1 查看列信息

# 查看列信息
SHOW COLUMNS FROM table_name;

# 或使用DESCRIBE
DESCRIBE table_name;

5.2 查看列的数据类型和约束

# 从information_schema中查询列信息
SELECT column_name, data_type, character_maximum_length, 
column_default, is_nullable, column_key, extra 
FROM information_schema.columns 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

5.3 查看列的字符集和排序规则

# 查看列的字符集和排序规则
SELECT column_name, character_set_name, collation_name 
FROM information_schema.columns 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

6. 索引元数据查看

6.1 查看表的索引

# 查看表的索引
SHOW INDEX FROM table_name;

# 或使用SHOW KEYS
SHOW KEYS FROM table_name;

6.2 从information_schema中查询索引信息

# 从information_schema中查询索引信息
SELECT table_name, index_name, column_name, seq_in_index, index_type 
FROM information_schema.statistics 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

6.3 查看索引大小

# 查看索引大小
SELECT table_name, index_name, ROUND(index_length / 1024 / 1024, 2) AS 'Index Size (MB)' 
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
ORDER BY index_length DESC;

7. 外键元数据查看

7.1 查看表的外键

# 查看表的外键
SHOW CREATE TABLE table_name;

# 或从information_schema中查询
SELECT table_name, column_name, referenced_table_name, referenced_column_name 
FROM information_schema.key_column_usage 
WHERE table_schema = 'database_name' AND referenced_table_name IS NOT NULL;

7.2 查看外键约束

# 查看外键约束
SELECT constraint_name, table_name, column_name, 
referenced_table_name, referenced_column_name 
FROM information_schema.referential_constraints 
WHERE constraint_schema = 'database_name';

8. 视图元数据查看

8.1 查看数据库中的视图

# 查看数据库中的视图
SHOW TABLES FROM database_name WHERE TABLE_TYPE = 'VIEW';

# 或从information_schema中查询
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_type = 'VIEW';

8.2 查看视图定义

# 查看视图定义
SHOW CREATE VIEW view_name;

# 或从information_schema中查询
SELECT view_definition 
FROM information_schema.views 
WHERE table_schema = 'database_name' AND table_name = 'view_name';

9. 存储过程和函数元数据查看

9.1 查看存储过程和函数

# 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

# 查看函数
SHOW FUNCTION STATUS WHERE Db = 'database_name';

9.2 查看存储过程和函数定义

# 查看存储过程定义
SHOW CREATE PROCEDURE procedure_name;

# 查看函数定义
SHOW CREATE FUNCTION function_name;

# 或从information_schema中查询
SELECT routine_name, routine_type, routine_definition 
FROM information_schema.routines 
WHERE routine_schema = 'database_name';

10. 触发器元数据查看

10.1 查看触发器

# 查看触发器
SHOW TRIGGERS WHERE Db = 'database_name';

# 或从information_schema中查询
SELECT trigger_name, event_manipulation, event_object_table, action_statement 
FROM information_schema.triggers 
WHERE trigger_schema = 'database_name';

10.2 查看触发器定义

# 查看触发器定义
SHOW CREATE TRIGGER trigger_name;

11. 系统变量和状态变量

11.1 查看系统变量

# 查看所有系统变量
SHOW VARIABLES;

# 查看特定系统变量
SHOW VARIABLES LIKE 'max_connections';

# 或使用SELECT
SELECT @@max_connections;

11.2 查看状态变量

# 查看所有状态变量
SHOW GLOBAL STATUS;

# 查看会话状态变量
SHOW SESSION STATUS;

# 查看特定状态变量
SHOW GLOBAL STATUS LIKE 'Threads_connected';

12. 使用sys schema查看元数据

12.1 查看表信息

# 使用sys schema查看表信息
USE sys;

# 查看表大小
SELECT * FROM schema_table_statistics WHERE table_schema = 'database_name';

# 查看表索引使用情况
SELECT * FROM schema_index_statistics WHERE table_schema = 'database_name';

12.2 查看数据库信息

# 查看数据库大小
SELECT * FROM schema_statistics;

# 查看数据库表数量
SELECT schema_name, COUNT(*) AS table_count 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
GROUP BY schema_name;

13. 元数据查询的最佳实践

13.1 常用元数据查询语句

# 1. 查看所有数据库及其大小
SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables 
GROUP BY table_schema 
ORDER BY SUM(data_length + index_length) DESC;

# 2. 查看数据库中的表及其大小
SELECT table_name AS 'Table', 
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)' 
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
ORDER BY (data_length + index_length) DESC;

# 3. 查看表的索引信息
SELECT table_name, index_name, column_name, seq_in_index, index_type 
FROM information_schema.statistics 
WHERE table_schema = 'database_name' 
ORDER BY table_name, index_name, seq_in_index;

# 4. 查看表的外键信息
SELECT constraint_name, table_name, column_name, 
referenced_table_name, referenced_column_name 
FROM information_schema.key_column_usage 
WHERE table_schema = 'database_name' AND referenced_table_name IS NOT NULL;

13.2 元数据查询的性能考虑

避免全表扫描：在查询information_schema时，尽量使用WHERE子句限制范围
使用索引：information_schema中的表也有索引，合理使用WHERE子句可以利用索引
限制结果集：使用LIMIT子句限制返回的行数
定期缓存：对于频繁查询的元数据，可以考虑缓存结果

13.3 元数据的应用场景

数据库监控：监控数据库大小、表大小、索引使用情况等
性能优化：根据表结构和索引信息优化SQL语句
数据迁移：了解表结构以便进行数据迁移
自动化脚本：编写脚本自动生成数据库文档或进行数据库维护
安全审计：检查用户权限和数据库结构

14. 实际应用案例

14.1 生成数据库文档

#!/bin/bash

# 数据库连接信息
USER="root"
PASSWORD="password"
DATABASE="company_db"

# 生成数据库文档
echo "# $DATABASE 数据库文档" > database_doc.md
echo "" >> database_doc.md

# 查看数据库表
TABLES=$(mysql -u $USER -p$PASSWORD -e "SHOW TABLES FROM $DATABASE;")

# 遍历每个表
for TABLE in $TABLES; do
    if [ "$TABLE" != "Tables_in_$DATABASE" ]; then
        echo "## $TABLE 表" >> database_doc.md
        echo "" >> database_doc.md
        
        # 查看表结构
        echo "### 表结构" >> database_doc.md
        echo "| 字段名 | 数据类型 | 约束 | 描述 |" >> database_doc.md
        echo "| --- | --- | --- | --- |" >> database_doc.md
        
        mysql -u $USER -p$PASSWORD -e "DESCRIBE $DATABASE.$TABLE;" | while read line; do
            if [[ ! $line =~ ^Field ]]; then
                FIELDS=($line)
                echo "| ${FIELDS[0]} | ${FIELDS[1]} | ${FIELDS[2]} | ${FIELDS[3]} |" >> database_doc.md
            fi
        done
        
        # 查看表索引
        echo "" >> database_doc.md
        echo "### 索引信息" >> database_doc.md
        echo "| 索引名 | 类型 | 字段 |" >> database_doc.md
        echo "| --- | --- | --- |" >> database_doc.md
        
        mysql -u $USER -p$PASSWORD -e "SHOW INDEX FROM $DATABASE.$TABLE;" | while read line; do
            if [[ ! $line =~ ^Table ]]; then
                FIELDS=($line)
                echo "| ${FIELDS[2]} | ${FIELDS[10]} | ${FIELDS[4]} |" >> database_doc.md
            fi
        done
        
        echo "" >> database_doc.md
    fi
done

echo "数据库文档生成完成: database_doc.md"

14.2 检查表结构一致性

# 检查两个数据库之间的表结构差异
SELECT t1.table_name, t1.column_name, t1.data_type, t1.column_default, t1.is_nullable
FROM information_schema.columns t1
LEFT JOIN information_schema.columns t2 
ON t1.table_name = t2.table_name 
AND t1.column_name = t2.column_name 
AND t2.table_schema = 'database2'
WHERE t1.table_schema = 'database1' 
AND (t2.column_name IS NULL 
OR t1.data_type != t2.data_type 
OR t1.column_default != t2.column_default 
OR t1.is_nullable != t2.is_nullable);

14.3 监控数据库增长

#!/bin/bash

# 数据库连接信息
USER="root"
PASSWORD="password"
DATABASE="company_db"

# 监控数据库大小
echo "$(date +'%Y-%m-%d %H:%M:%S'),$(mysql -u $USER -p$PASSWORD -e "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) FROM information_schema.tables WHERE table_schema = '$DATABASE';")" >> database_growth.log

echo "数据库大小已记录到 database_growth.log"

15. 总结

MySQL元数据信息查看是数据库管理和开发中的重要技能。通过查看元数据，可以了解数据库的结构、配置和状态，为数据库管理、性能优化和开发提供重要参考。

本文介绍了如何使用各种命令和查询语句查看MySQL的元数据信息，包括数据库、表、列、索引、外键、视图、存储过程、函数和触发器等对象的信息。同时，还介绍了如何使用sys schema和information_schema等系统数据库来查询元数据，并提供了实际应用案例。 05更多学习教程公众号风哥教程itpux_com

通过掌握这些元数据查看方法，您可以更好地了解和管理MySQL数据库，提高数据库管理和开发的效率和质量。 06 from mysql视频:www.itpux.com

GF-MySQL培训系列文档，由资深数据库专家精心打造，涵盖MySQL全方位技术知识。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html