MySQL 常用操作指南（Shell 环境）

一、Shell 中导入/导出数据

1. 本地导入数据（LOAD DATA LOCAL INFILE）

mysql -u root -p -e "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE stu.students FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' IGNORE 1 ROWS;"

注意：需确保 MySQL 启用了 local_infile（默认可能关闭）：

SHOW VARIABLES LIKE 'local_infile'; — 查看
SET GLOBAL local_infile = 'ON'; — 临时开启（需权限）

• 客户端连接时也要加 –local-infile=1：

  mysql –local-infile=1 -u root -p

• IGNORE 1 ROWS 跳过 CSV 的标题行。
• 文件路径是客户端所在机器的路径（不是数据库服务器）。

2. 导出数据（mysqldump）

基础导出表结构+数据：

mysqldump -u root -p123456 stu students > /root/data/mydb.sql

⚠️ 安全警告：密码明文写在命令行会泄露（可通过 history 查看）。 推荐做法：

• 使用配置文件（~/.my.cnf）：

  [client]
  user=root
  password=123456 然后： mysqldump stu students > /root/data/mydb.sql

• 或交互式输入密码（不写 -p123456，只写 -p）

导出仅数据（无建表语句）：

mysqldump -u root -p –no-create-info stu students > data_only.sql

导出仅结构（无数据）：

mysqldump -u root -p –no-data stu students > schema_only.sql

备份并压缩（推荐）：

alias db_backup='mysqldump -u root -p –single-transaction –quick –lock-tables=false stu students | gzip > /root/data/mydb_$(date +%Y%m%d).sql.gz'

参数说明：

• –single-transaction：InnoDB 一致性快照（避免锁表）
• –quick：逐行读取，防内存溢出
• –lock-tables=false：配合 –single-transaction 避免锁

二、查看 DDL 与当前数据库

— 查看建表语句（修正拼写错误：show create table）
SHOW CREATE TABLE students\\G

— 查看当前数据库
SELECT DATABASE();
— 或
STATUS; — 显示更多信息，包括当前库

三、LIMIT 限制查询

— 前10条
SELECT * FROM students LIMIT 10;

— 分页：跳过前20条，取10条（第3页）
SELECT * FROM students LIMIT 20, 10;
— 或
SELECT * FROM students LIMIT 10 OFFSET 20;

注意：LIMIT 无 ORDER BY 时结果顺序不确定！

四、ALTER TABLE 表结构修改（扩展与规范）

增加字段（ADD）

— 推荐指定位置（如 FIRST / AFTER）
ALTER TABLE students ADD COLUMN enroll_date DATE DEFAULT '2025-12-12' AFTER name;

修改字段（MODIFY vs CHANGE）

— MODIFY：只改类型和注释
ALTER TABLE students MODIFY dateT DATETIME COMMENT '入学时间';

— CHANGE：改名 + 改类型
ALTER TABLE students CHANGE stu_id student_id VARCHAR(50) NOT NULL;

🔔 重要：MODIFY 必须重写完整字段定义（类型、是否为空、默认值等），否则会被重置！

删除字段（DROP）

ALTER TABLE students DROP COLUMN length;

修改字符集

— 修改表字符集（不影响已有数据编码！）
ALTER TABLE students CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

— 更彻底（同时转数据）：
ALTER TABLE students CONVERT TO CHARACTER SET utf8mb4;

 建议使用 utf8mb4（支持 emoji），而非 utf8（MySQL 的伪 utf8）

重命名表

RENAME TABLE students TO student_info;
— 或
ALTER TABLE students RENAME TO student_info;

五、删除表（DROP）

— 删除表（结构+数据全丢）
DROP TABLE students;

— 安全写法：先检查是否存在
DROP TABLE IF EXISTS students;

六、补充：实用 Shell 脚本技巧

1. 自动备份脚本（带日期、压缩、日志）

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M)
BACKUP_DIR="/backup/mysql"
DB="stu"
TABLE="students"
USER="root"

mkdir -p $BACKUP_DIR

mysqldump –single-transaction -u$USER $DB $TABLE | gzip > $BACKUP_DIR/${DB}_${TABLE}_$DATE.sql.gz

echo "$(date): Backup completed: ${DB}_${TABLE}_$DATE.sql.gz" >> /var/log/db_backup.log

2. 设置定时任务（crontab）

# 每天凌晨2点备份
0 2 * * * /root/scripts/db_backup.sh

七、安全与最佳实践总结