MySQL 数据库核心操作：创建、修改、备份与连接排查

查看所有支持的字符集和校验规则

SHOW CHARSET;
SHOW COLLATION;

提示：除非有历史遗留需求，否则优先选择 utf8，避免使用 latin1 等不支持中文的字符集。

校验规则的实际影响

以 utf8_general_ci（不区分大小写）和 utf8_bin（区分大小写）为例：

案例 1：不区分大小写 (utf8_general_ci)

CREATE DATABASE test1 COLLATE utf8_general_ci;
USE test1;
CREATE TABLE person(name VARCHAR(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
SELECT * FROM person WHERE name='a';
SELECT * FROM person ORDER BY name;

结果：查询 name='a' 会返回 a 和 A；排序不分大小写。

案例 2：区分大小写 (utf8_bin)

CREATE DATABASE test2 COLLATE utf8_bin;
USE test2;
CREATE TABLE person(name VARCHAR(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
SELECT * FROM person WHERE name='a';
SELECT * FROM person ORDER BY name;

结果：查询 name='a' 只返回 a；排序时大写字母在前。

结论：用户信息等需区分大小写的场景用 utf8_bin，普通业务数据推荐 utf8_general_ci。

操纵数据库：查看、修改、删除

查看数据库信息

-- 查看所有数据库
SHOW DATABASES;

-- 查看指定数据库的创建语句（含字符集等配置）
SHOW CREATE DATABASE mytest;

细节理解：

• 反引号 `：防止库名与关键字冲突。
• /*!40100 DEFAULT CHARACTER SET utf8 */：版本兼容指令，非注释。

修改数据库配置

仅支持修改字符集和校验规则，不支持直接改库名（改库名建议迁移数据）。

ALTER DATABASE db_name [alter_spacification ...];
-- 示例：修改字符集为 gbk
ALTER DATABASE mytest CHARSET=gbk;

删除数据库

高危操作，执行后数据不可恢复。

DROP DATABASE [IF EXISTS] db_name;

提醒： 生产环境严禁直接删除，务必先备份并审批；开发环境确认无用后再删。

数据库的备份与恢复

数据是核心资产，mysqldump 和 source 是标准工具。

备份工具：mysqldump

需在命令行（bash/CMD）中执行，退出 MySQL 连接。

# 备份单个数据库
mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名 > 备份文件路径/文件名.sql

参数说明：

• -P：端口号，默认 3306。
• -u：用户名。
• -p：密码（紧接 -p 无空格）。
• -B：关键参数，包含建库语句。
• >：重定向写入文件。

多场景备份实战

1. 备份单个完整数据库

mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

2. 备份指定表

mysqldump -u root -p123456 mytest person user > D:/mytest_tables.sql

3. 批量备份多个数据库

mysqldump -u root -p123456 -B mytest db1 db2 > D:/multi_db.sql

恢复数据库

在 MySQL 连接中使用 source 命令。

SOURCE D:/mytest.sql;

避坑指南：

• -B 参数：若未加，恢复时需手动建库并 USE 指定库。
• 路径：建议使用绝对路径。
• 权限：备份用户需查询权限，恢复用户需创建及插入权限。
• 编码：确保备份文件编码与数据库一致。
• 策略：生产环境建议定时自动备份并异地存储。

查看数据库连接情况

遇到卡顿或疑似入侵时，使用 show processlist 排查。

SHOW PROCESSLIST;

关键字段：

应用场景：

• 排查卡顿：关注 Query 状态且 Time 大的连接，定位慢 SQL。
• 排查入侵：检查陌生 User 或非内网 Host。
• 释放无效连接：大量 Sleep 且 Time 大时，可用 kill Id; 释放。

核心避坑指南与最佳实践

1. 创建必加 IF NOT EXISTS：避免重复报错。
2. 禁止直接删生产库：先备份，做权限管控。
3. 备份必加 -B 参数：确保恢复时有建库语句。
4. 字符集选 utf8：避免乱码。
5. 规范编码：关键字大写，库名表名小写，加反引号。
6. 最小权限原则：开发用户不给删除、建库权限。
7. 定期查连接：及时释放无效连接，优化性能。