MySQL 数据导入导出常用 LOAD DATA LOCAL INFILE 和 mysqldump 命令,支持本地文件导入及备份压缩。查看表结构使用 SHOW CREATE TABLE 获取完整 DDL,通过 SELECT DATABASE() 确认当前库。查询结果限制采用 LIMIT 关键字实现分页或 Top N 取数。表结构修改依赖 ALTER TABLE 语句,涵盖新增、修改、删除字段及重命名等操作,需注意 MODIFY 需指定类型。表删除使用 DROP TABLE,建议加 IF EXISTS 避免报错并提前备份。掌握这些核心操作可提升数据库管理效率与安全性。
修罗2 浏览
在日常的数据库管理工作中,导入导出数据、查看表结构、限制查询结果、调整表结构乃至删除表,都是开发者和运维人员高频接触的操作。MySQL 作为主流的关系型数据库,提供了丰富的 Shell 命令和 SQL 语句来完成这些工作。本文将系统梳理 MySQL 数据导入导出、DDL 查看、查询限制、表结构修改及表删除的核心操作,结合实际场景讲解用法、注意事项和最佳实践,帮助读者高效掌握这些必备技能。
一、MySQL 数据导入导出:Shell 终端实操指南
数据的导入导出是数据库备份、迁移、批量更新的核心环节。MySQL 支持在 Shell 终端中通过命令完成这些操作,相比在 MySQL 客户端内执行,终端操作更适合自动化脚本、批量处理等场景。
1. 数据导入:LOAD DATA LOCAL INFILE
LOAD DATA LOCAL INFILE 是 MySQL 中高效导入本地文件数据到数据表的命令,需在 Shell 终端的 MySQL 交互模式下执行(先登录 MySQL,再执行命令)。其核心语法如下:
# 登录 MySQL(终端执行)
mysql -uroot -p
# 进入目标数据库
use stu;
# 本地导入数据
load data local infile '文件绝对路径/相对路径' into table 表名 fields terminated by '分隔符';
关键参数说明
local:表示读取客户端本地的文件,而非 MySQL 服务器端的文件;若省略该参数,MySQL 会读取服务器端指定路径的文件,需确保服务器有该文件的访问权限。
fields terminated by '分隔符':指定文件中字段的分隔符,常见的有逗号(,)、制表符(\t)、竖线(|)等,需与导入文件的格式匹配。
要将该数据导入 stu 数据库的 students 表(字段为 stu_id、name、age),操作如下:
mysql -uroot -p123456
use stu;
load data local infile '/root/data/students.txt' into table students fields terminated by '\t';
注意事项
执行该命令前,需确保 MySQL 客户端开启了 local-infile 权限,可在登录时指定:mysql -uroot -p --local-infile=1。
导入文件的字段数量、顺序需与目标表一致,否则会导致数据错位或导入失败。
若目标表有主键、唯一索引等约束,需确保导入数据不违反约束,否则导入会中断。
2. 数据导出:mysqldump 命令
mysqldump 是 MySQL 官方提供的备份工具,可在 Shell 终端直接执行,用于导出数据库、表的结构和数据,是数据库备份的首选方式。
基础语法:导出指定表
mysqldump -u 用户名 -p 密码 数据库名 表名 > 导出文件路径
示例:导出 stu 数据库的 students 表到 /root/data/mydb.sql:
mysqldump -uroot -p123456 stu students > /root/data/mydb.sql
进阶用法 1:添加别名实现一键备份
手动输入长命令容易出错,可将备份命令添加到 ~/.bashrc 文件中,设置别名,实现一键备份:
# 编辑.bashrc 文件
vim ~/.bashrc
# 添加别名(按实际需求修改参数)
alias db_backup='mysqldump -uroot -p stu students > /root/data/mydb_$(date +%Y%m%d).sql'
# 使配置生效
source ~/.bashrc
# 修改字段类型:将 dateT 从 date 改为 datetime
ALTER TABLE students MODIFY dateT datetime;
# 修改字段注释(必须重新声明类型)
ALTER TABLE students MODIFY dateT date comment "入学日期";
# 修改字段为非空
ALTER TABLE students MODIFY stu_id intNOT NULL;
# 修改字段为允许为空(默认值为 NULL)
ALTER TABLE students MODIFY stu_id intDEFAULTNULL;
# 同时修改类型、默认值和注释
ALTER TABLE students MODIFY age intDEFAULT18 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 TABLEALTER 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 删除,需特别谨慎(删除后表结构和数据均会被清除,且无法通过常规方式恢复)。
基础语法
DROPTABLE [IF EXISTS] 表名;
IF EXISTS:可选参数,用于避免删除不存在的表时抛出错误;
可同时删除多个表,用逗号分隔:DROP TABLE IF EXISTS students, scores;。
实操示例
# 删除 students 表(若存在)
DROPTABLE IF EXISTS students;
注意事项
生产环境中,删除表前务必确认表已无用,或已完成全量备份;
若表被外键关联,需先删除外键约束,或使用 DROP TABLE ... CASCADE(部分数据库支持);
建议给数据库账号最小权限,避免普通账号拥有删除表的权限。
总结
本文系统梳理了 MySQL 在 Shell 终端和 SQL 层面的核心操作,核心要点如下:
数据导入导出:LOAD DATA LOCAL INFILE 适合本地数据导入,mysqldump 是备份导出的首选,结合别名和压缩可提升效率;