第八篇：MySQL 表存储与元数据查询
|
文章 , 学习笔记
|
657 字
|
2 分钟

表存储位置与文件

MySQL 表默认存储在{MySQL安装目录}/data/{数据库名}/，不同存储引擎的文件不同：

MyISAM 引擎（已较少使用）

  • 表名.frm：存储表结构定义；
  • 表名.MYD：存储表数据；
  • 表名.MYI：存储表索引。

InnoDB 引擎（默认推荐）

  • 数据和索引通常存储在共享表空间（ibdata1文件）或独立表空间（表名.ibd）；
  • 可通过以下命令查看表空间设置：
1
2
<code>SHOW CREATE TABLE 表名;
</code>

表引擎管理（选择合适的引擎）

存储引擎决定了表的存储方式和功能，MySQL 支持多种引擎，常用的有：

  • InnoDB：支持事务、外键，安全性高，适合核心业务（如订单表）；
  • MyISAM：不支持事务，查询速度快，适合只读场景（如日志表）。

查看表引擎

1
2
<code>SHOW TABLE STATUS LIKE &#039;表名&#039; \G;  -- \G使结果竖排显示，更易读
</code>

创建表时指定引擎

1
2
3
4
5
<code>CREATE TABLE 表名 (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB;  -- 指定InnoDB引擎
</code>

修改表引擎

1
2
<code>ALTER TABLE 表名 ENGINE=MyISAM;  -- 将表引擎改为MyISAM
</code>

表字符集设置（避免乱码）

字符集用于存储文字（如中文、英文），推荐使用utf8mb4（支持所有字符，包括 emoji）。

查看表字符集

1
2
<code>SHOW TABLE STATUS LIKE &#039;表名&#039; \G;
</code>

创建表时指定字符集

1
2
3
4
5
<code>CREATE TABLE 表名 (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) DEFAULT CHARSET=utf8mb4;
</code>

修改表字符集

1
2
3
4
5
6
<code>-- 修改表整体字符集
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4;

-- 修改单个字段字符集
ALTER TABLE 表名 MODIFY COLUMN name VARCHAR(50) CHARACTER SET utf8mb4;
</code>

元数据查询（查询数据库 / 表的信息）

元数据是描述数据库、表、字段的数据，可通过information_schema系统库查询。

查询所有数据库

1
2
3
<code>SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
-- 等价于SHOW DATABASES;，但更符合SQL标准
</code>

查询指定数据库的所有表

1
2
3
4
5
6
7
<code>SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = &#039;目标数据库名&#039;;

-- 示例：查询test_db的所有表
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = &#039;test_db&#039;;
</code>

查询指定表的所有字段

1
2
3
4
5
6
7
8
9
10
11
12
<code>SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = &#039;目标数据库名&#039; 
  AND TABLE_NAME = &#039;目标表名&#039;
ORDER BY ORDINAL_POSITION;  -- 按字段顺序排序

-- 示例：查询test_db中users表的字段信息
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = &#039;test_db&#039; 
  AND TABLE_NAME = &#039;users&#039;
ORDER BY ORDINAL_POSITION;</code>
上一篇
下一篇