MySQL 数据导入导出与表结构管理核心操作指南

进阶用法 1：添加别名实现一键备份

手动输入长命令容易出错，可将备份命令添加到 ~/.bashrc 文件中，设置别名，实现一键备份：

# 编辑.bashrc 文件
vim ~/.bashrc
# 添加别名（按实际需求修改参数）
alias db_backup='mysqldump -uroot -p stu students > /root/data/mydb_$(date +%Y%m%d).sql'
# 使配置生效
source ~/.bashrc

执行 db_backup 命令后，会自动生成以当天日期命名的备份文件（如 mydb_20250820.sql），避免覆盖历史备份。

进阶用法 2：备份并压缩，节省存储空间

导出的 SQL 文件通常体积较大，可通过管道符结合 gzip 压缩，生成 .sql.gz 格式的压缩文件：

# 修改.bashrc 中的别名
alias db_backup='mysqldump -uroot -p --single-transaction stu students | gzip > /root/data/mydb_$(date +%Y%m%d).sql.gz'

其中 --single-transaction 参数用于 InnoDB 引擎，保证备份过程中数据的一致性，避免锁表。

mysqldump 常用参数补充

• -d：仅导出表结构，不导出数据；
• -t：仅导出数据，不导出表结构；
• --where：导出满足条件的数据，如 mysqldump -uroot -p stu students --where="age>20" > /root/data/age20.sql；
• -h：指定 MySQL 服务器地址（远程备份时使用），如 mysqldump -uroot -p123456 -h192.168.1.100 stu students > /root/data/remote.sql。

二、查看 DDL 与当前数据库：快速掌握表结构与上下文

在操作数据库时，经常需要确认表的创建语句（DDL）或当前所处的数据库，MySQL 提供了简洁的 SQL 语句来完成这些查询。

1. 查看表的 DDL 语句：SHOW CREATE TABLE

SHOW CREATE TABLE 可查看创建表的完整 SQL 语句，包括字段类型、索引、字符集、存储引擎等关键信息，是排查表结构问题的核心命令。语法如下：

SHOW CREATE TABLE 表名;

示例：查看 students 表的 DDL：

USE stu;
SHOW CREATE TABLE students;

输出结果包含两列：Table（表名）和 Create Table（完整的建表语句），示例输出如下：

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `stu_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dateT` date DEFAULT '2025-12-12' COMMENT '日期',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+

通过该命令，可快速确认表的存储引擎（InnoDB）、字符集（utf8）、字段默认值、注释等信息，尤其适合接手他人项目时快速熟悉表结构。

2. 查看当前数据库：SELECT DATABASE ()

在 MySQL 交互模式下，若忘记当前所处的数据库，可通过 SELECT DATABASE() 查询：

SELECT DATABASE();

若未切换到任何数据库，返回 NULL；若已执行 USE stu，则返回 stu。该命令虽简单，但能避免因选错数据库导致的操作错误（如误修改其他库的表）。

三、限制查询结果：LIMIT 关键字的灵活运用

当数据表中有大量数据时，直接执行 SELECT * FROM 表名 会返回所有数据，既占用资源，又不利于快速查看数据。LIMIT 关键字可限制查询结果的行数，是日常数据查看、分页查询的必备工具。

1. 基础语法

# 只返回前 N 行数据
SELECT 字段列表 FROM 表名 LIMIT N;
# 分页查询：从第 M 行开始（索引从 0 开始），返回 N 行数据
SELECT 字段列表 FROM 表名 LIMIT M, N;

2. 实操示例

# 查看 students 表前 10 条数据
SELECT * FROM students LIMIT 10;
# 分页查询：从第 10 行开始（跳过前 10 行），返回 5 行数据（第 11-15 行）
SELECT stu_id, name FROM students LIMIT 10, 5;

3. 应用场景

• 快速预览表数据：无需加载全量表，提升查询效率；
• 分页展示数据：在 Web 开发中，结合 LIMIT M, N 实现分页功能（如第 1 页 LIMIT 0, 10，第 2 页 LIMIT 10, 10）；
• 取 Top N 数据：如查询年龄最大的 5 个学生：SELECT * FROM students ORDER BY age DESC LIMIT 5;。

注意事项

• LIMIT 后的参数必须是非负整数，不能使用负数；
• 在 MySQL 8.0 及以上版本，支持 LIMIT N OFFSET M 的写法（与 LIMIT M, N 等价），可读性更强：SELECT * FROM students LIMIT 5 OFFSET 10;。

四、表结构修改：ALTER TABLE 语句全解析

业务需求变更时，经常需要调整表结构（如新增字段、修改字段类型、重命名字段 / 表），MySQL 通过 ALTER TABLE 语句实现这些操作，以下是常用场景的详细用法。

1. 新增字段（ADD）

基础语法

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [约束/默认值/注释];

• COLUMN 关键字可选，不影响执行结果；
• 可添加的约束包括 NOT NULL、DEFAULT、COMMENT 等。

实操示例

# 新增日期字段 dateT，类型为 date
ALTER TABLE students ADD dateT date;
# 新增日期字段 dateT，设置默认值为 2025-12-12
ALTER TABLE students ADD dateT date DEFAULT "2025-12-12";
# 新增字段并添加注释，指定字段位置（FIRST 表示第一个字段，AFTER 字段名表示在指定字段后）
ALTER TABLE students ADD phone varchar(11) COMMENT '手机号' AFTER name;

2. 修改字段（MODIFY）

MODIFY 用于修改字段的类型、约束、默认值、注释等，核心注意点：无论修改什么属性，都必须明确指定字段类型（哪怕只改注释）。

基础语法

ALTER TABLE 表名 MODIFY 字段名 字段类型 [新约束/新默认值/新注释];

实操示例

# 修改字段类型：将 dateT 从 date 改为 datetime
ALTER TABLE students MODIFY dateT datetime;
# 修改字段注释（必须重新声明类型）
ALTER TABLE students MODIFY dateT date comment "入学日期";
# 修改字段为非空
ALTER TABLE students MODIFY stu_id int NOT NULL;
# 修改字段为允许为空（默认值为 NULL）
ALTER TABLE students MODIFY stu_id int DEFAULT NULL;
# 同时修改类型、默认值和注释
ALTER TABLE students MODIFY age int DEFAULT 18 COMMENT '学生年龄，默认 18 岁';

3. 删除字段（DROP）

删除表中无用的字段，语法简洁，需谨慎操作（删除后数据不可恢复）。

ALTER TABLE 表名 DROP 字段名;

示例：删除 students 表中的 length 字段：

ALTER TABLE students DROP length;

4. 重命名字段（CHANGE）

CHANGE 不仅能修改字段名，还可同时修改字段类型、约束等，语法中需同时指定旧字段名和新字段名。

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段类型 [约束/注释];

示例：将 stu_id 重命名为 sid，并修改类型为 varchar(255)：

ALTER TABLE students CHANGE stu_id sid varchar(255);

5. 修改表的字符集（CHARACTER SET）

若表的字符集与业务需求不匹配（如从 latin1 改为 utf8），可通过以下语句修改：

ALTER TABLE 表名 CHARACTER SET 字符集名称;

示例：将 students 表的字符集改为 utf8：

ALTER TABLE students CHARACTER SET utf8;

注意：该操作仅修改表的默认字符集，已存在的字段字符集需单独修改：ALTER TABLE students MODIFY name varchar(255) CHARACTER SET utf8;。

6. 修改表名（RENAME TO / RENAME TABLE）

有两种方式可修改表名，效果等价，可根据习惯选择。

# 方式 1：ALTER TABLE
ALTER TABLE 旧表名 RENAME TO 新表名;
# 方式 2：RENAME TABLE
RENAME TABLE 旧表名 TO 新表名;

示例：将 students 表重命名为 student：

ALTER TABLE students RENAME TO student;
# 或
RENAME TABLE students TO student;

ALTER TABLE 注意事项

• 执行表结构修改时，若表数据量较大，会锁表（InnoDB 引擎可通过 ALTER TABLE ... ALGORITHM=INPLACE 减少锁表时间）；
• 修改字段类型时，需确保数据兼容（如将 varchar 改为 int，需保证字段内无非数字字符）；
• 操作前建议备份表数据，避免误操作导致数据丢失。

五、表删除：DROP TABLE 语句

当表不再使用时，可通过 DROP TABLE 删除，需特别谨慎（删除后表结构和数据均会被清除，且无法通过常规方式恢复）。

基础语法

DROP TABLE [IF EXISTS] 表名;

• IF EXISTS：可选参数，用于避免删除不存在的表时抛出错误；
• 可同时删除多个表，用逗号分隔：DROP TABLE IF EXISTS students, scores;。

实操示例

# 删除 students 表（若存在）
DROP TABLE IF EXISTS students;

注意事项

• 生产环境中，删除表前务必确认表已无用，或已完成全量备份；
• 若表被外键关联，需先删除外键约束，或使用 DROP TABLE ... CASCADE（部分数据库支持）；
• 建议给数据库账号最小权限，避免普通账号拥有删除表的权限。

总结

本文系统梳理了 MySQL 在 Shell 终端和 SQL 层面的核心操作，核心要点如下：

1. 数据导入导出：LOAD DATA LOCAL INFILE 适合本地数据导入，mysqldump 是备份导出的首选，结合别名和压缩可提升效率；
2. 表结构与上下文查询：SHOW CREATE TABLE 查看完整 DDL，SELECT DATABASE() 确认当前数据库，是日常操作的基础；
3. 查询限制：LIMIT 关键字可限制结果行数，适用于数据预览和分页查询，注意参数的索引规则；
4. 表结构修改：ALTER TABLE 支持新增、修改、删除字段，重命名字段 / 表，修改字符集等，MODIFY 需始终指定字段类型；
5. 表删除：DROP TABLE 需谨慎操作，建议添加 IF EXISTS 避免报错，操作前务必备份数据。

掌握这些操作不仅能提升数据库管理效率，还能减少因操作不当导致的问题。实际使用中，需结合业务场景选择合适的命令，并遵循'操作前备份、操作中验证、操作后检查'的原则，确保数据安全。