获取数据库信息

导学

当你接手一个已有项目的数据库时，第一步不是写查询，而是先"摸清家底"——有哪些库、哪些表、表结构是什么。本节学习常用的元数据查询语句，每个场景都配有完整的 SQL 示例和操作前后对比。

定义

元数据（Metadata）：描述数据的数据。在 MySQL 中，元数据包括数据库名、表名、列名、数据类型、索引信息、权限信息等。MySQL 通过 SHOW 语句和 information_schema 库暴露元数据。

前置准备：创建测试环境

为了演示获取信息的各种方式，先创建一个包含多张表的数据库。

CREATE DATABASE IF NOT EXISTS demo_info
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE demo_info;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20) NOT NULL,
    dept VARCHAR(20) DEFAULT '技术部',
    score DECIMAL(5,2)
) ENGINE=InnoDB COMMENT='员工表';

CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    score DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_emp_id (emp_id)
) ENGINE=InnoDB COMMENT='绩效记录表';

-- 插入测试数据
INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

INSERT INTO scores (emp_id, score) VALUES
(1, 90);

场景一：查看所有数据库

当前数据状态：

SHOW DATABASES;

Database
demo_info
information_schema
mysql
performance_schema
sys

结果解读：SHOW DATABASES 列出当前用户有权限访问的所有数据库。

场景二：查看当前库中的所有表

当前数据状态：当前在 demo_info 数据库中。

SELECT DATABASE();

DATABASE()
demo_info

操作语句：

SHOW TABLES;

操作后的数据状态：

Tables_in_demo_info
employees
scores

结果解读：SHOW TABLES 列出当前数据库中的所有表。也可以查看指定数据库：

SHOW TABLES FROM demo_info;

结果相同。

场景三：查看表结构

当前数据状态：知道有 `employees` 表，但不知道具体结构。

操作语句：

DESCRIBE employees;
-- 简写
DESC employees;

操作后的数据状态：

Field	Type	Null	Key	Default	Extra
emp_id	int(11)	NO	PRI	NULL	auto_increment
emp_name	varchar(20)	NO		NULL
dept	varchar(20)	YES		技术部
score	decimal(5,2)	YES		NULL

结果解读：DESC 是查看表结构最快捷的方式，显示了列名、数据类型、是否可空、键类型、默认值和额外信息。

查看更详细的完整建表语句：

SHOW CREATE TABLE employees\G

结果解读：SHOW CREATE TABLE 输出完整的 CREATE TABLE 语句，包括所有约束、索引、注释和引擎信息，是迁移表结构时的最佳工具。

场景四：查看表中的索引

当前数据状态：

DESC scores;

Field	Type	Null	Key	Default	Extra
score_id	int(11)	NO	PRI	NULL	auto_increment
emp_id	int(11)	NO	MUL	NULL
score	decimal(5,2)	YES		NULL
created_at	timestamp	YES		CURRENT_TIMESTAMP

emp_id 列的 Key 显示 MUL，表示有索引。具体是什么索引？

操作语句：

SHOW INDEX FROM scores;

操作后的数据状态：

Table	Non_unique	Key_name	Seq_in_index	Column_name	Index_type
scores	0	PRIMARY	1	score_id	BTREE
scores	1	idx_emp_id	1	emp_id	BTREE

结果解读：

PRIMARY 是主键索引，Non_unique = 0 表示唯一索引
idx_emp_id 是普通索引，Non_unique = 1 表示非唯一索引
Index_type = BTREE 表示使用 B+ 树索引（MySQL 5.7 的默认索引类型）

场景五：通过 information_schema 精确查询

当前数据状态：需要了解 `demo_info` 库中所有表的详细信息。

操作语句：

-- 查询 demo_info 库中所有表及其引擎、行数（估算）、大小
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
FROM information_schema.tables
WHERE table_schema = 'demo_info'
ORDER BY data_length DESC;

操作后的数据状态：

table_name	engine	table_rows	data_size_mb	index_size_mb
employees	InnoDB	2	0.02	0.00
scores	InnoDB	1	0.02	0.00

结果解读：

information_schema.tables 是虚拟表，不占用磁盘空间
table_rows 对于 InnoDB 是估算值，基于索引统计信息，不是精确计数
精确行数应使用 SELECT COUNT(*) FROM table_name

验证 table_rows 是估算值：

SELECT table_rows FROM information_schema.tables
WHERE table_schema = 'demo_info' AND table_name = 'employees';

table_rows
2

SELECT COUNT(*) AS exact_count FROM employees;

exact_count
2

结果解读：小表中估算值可能恰好等于精确值，但在大表中两者可能有显著差异。始终用 COUNT(*) 获取精确行数。

场景六：查看表的列信息

当前数据状态：需要了解 `employees` 表每一列的详细定义。

操作语句：

SELECT
    column_name,
    data_type,
    is_nullable,
    column_default,
    column_comment,
    character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'demo_info'
  AND table_name = 'employees'
ORDER BY ordinal_position;

操作后的数据状态：

column_name	data_type	is_nullable	column_default	character_maximum_length
emp_id	int	NO	NULL	NULL
emp_name	varchar	NO	NULL	20
dept	varchar	YES	技术部	20
score	decimal	YES	NULL	NULL

结果解读：information_schema.columns 提供了比 DESC 更丰富的列信息，包括注释、字符长度限制等。ordinal_position 表示列在表定义中的顺序。

场景七：查看当前连接和进程

当前数据状态：需要了解谁在访问数据库。

操作语句：

SHOW PROCESSLIST;

操作后的数据状态（示例）：

Id	User	Host	db	Command	Time	State	Info
1	root	localhost	demo_info	Query	0	starting	SHOW PROCESSLIST
5	app_user	192.168.1.10:51234	demo_info	Sleep	120		NULL

结果解读：

Id：连接线程 ID，可用 KILL id 终止连接
Command = Sleep：连接空闲中
Time：当前状态持续的秒数
Info：正在执行的 SQL 语句

通过 information_schema 查询相同信息：

SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 50) AS info_preview
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

结果解读：information_schema.processlist 可以用标准的 SQL 语法过滤和排序，比 SHOW PROCESSLIST 更灵活。

场景八：查看表状态信息

当前数据状态：需要了解 `scores` 表的存储详情。

操作语句：

SHOW TABLE STATUS LIKE 'scores'\G

操作后的数据状态（关键字段节选）：

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Index_length
scores	InnoDB	10	Dynamic	1	5461	16384	0

结果解读：

Row_format = Dynamic：InnoDB 的动态行格式（MySQL 5.7 默认）
Rows：估算行数（InnoDB 是估算值）
Data_length：数据文件字节数
Index_length：索引文件字节数

计算表总大小：

SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_mb
FROM information_schema.tables
WHERE table_schema = 'demo_info'
  AND table_name = 'scores';

table_name	total_size_mb
scores	0.02

结果解读：表总大小 = 数据大小 + 索引大小。在磁盘空间管理时，这是判断表增长趋势的重要指标。

场景九：查看当前用户的权限

当前数据状态：需要确认自己有哪些权限。

操作语句：

SHOW GRANTS;
-- 或查看指定用户
SHOW GRANTS FOR CURRENT_USER();

操作后的数据状态（root 用户示例）：

Grants for root@localhost
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION

结果解读：SHOW GRANTS 显示当前用户的权限列表。生产环境中，应确保账号只拥有必要的最小权限。

常见误区

误区	正解
`information_schema` 是真实数据库	它是内存中的虚拟数据库，查询它不会触发磁盘 I/O（部分视图会查询系统表）。
`SHOW PROCESSLIST` 能看到所有查询	只能看到当前用户有权限看到的线程，且只展示前 100 个字符的 SQL。
`SHOW TABLE STATUS` 的行数是精确的	InnoDB 的 `Rows` 字段是估算值，基于采样统计。

面试考点

Q：如何查看一张表占用了多少磁盘空间？

SHOW TABLE STATUS LIKE 'users'\G 查看 Data_length 和 Index_length；或通过 information_schema.tables 查询 data_length + index_length。

验证：

SELECT 
    table_name,
    CONCAT(ROUND((data_length + index_length) / 1024, 2), ' KB') AS total_size
FROM information_schema.tables
WHERE table_schema = 'demo_info'
ORDER BY (data_length + index_length) DESC;

table_name	total_size
employees	16.00 KB
scores	16.00 KB

Q：SHOW 和 information_schema 有什么区别？

SHOW 语句语法简洁、结果直观，适合日常快速查看；information_schema 是标准的 SQL 方式，支持 WHERE、JOIN、聚合 等复杂查询，适合程序化获取元数据。

Q：如何查看当前有哪些连接在访问数据库？

SHOW PROCESSLIST; 或查询 information_schema.processlist。发现慢查询或锁等待时，这是首选诊断工具。

小结

SHOW DATABASES/TABLES 快速浏览环境
DESC / SHOW CREATE TABLE 查看表结构
information_schema 提供标准化的元数据查询能力
table_rows 在 InnoDB 中是估算值，不是精确值
SHOW PROCESSLIST 是诊断连接和慢查询的首选工具

清理测试数据库（可选）：

-- DROP DATABASE IF EXISTS demo_info;

下一章引子：掌握了数据库的"地形图"，接下来进入 SQL 最核心的部分——数据查询。