02-MySQL 库级操作

💬 ：如果你在阅读过程中有任何疑问或想要进一步探讨的内容，欢迎在评论区畅所欲言！我们一起学习、共同成长~！

👍 ：如果你觉得这篇文章还不错，不妨顺手点个赞、加入收藏，并分享给更多的朋友噢~！

1. 创建数据库

1.1 核心语法

CREATE DATABASE IF NOT EXISTS db_name charset=... collate ...;

• 关键字解读（面试可能问作用）：
  • 其中大写内容为关键字，关键字大小写不影响执行——开发中推荐大写关键字，这是 MySQL 开发的通用习惯，能让代码更易读、易维护。
  • CREATE DATABASE：创建数据库；
  • IF NOT EXISTS：可选但强烈建议加—— 如果数据库已存在，没有这个会报错，加了就会 安静跳过创建；
  • db_name：数据库名，比如 db1、user_db（注意不能用 MySQL 关键字，比如 “create”“select”，后面讲怎么避免冲突）；
  • charset=… （可选）：指定 “字符集”—— 字符集是数据库 “存储文字的编码规则”，utf8能存中文，而默认的latin1存中文会乱码；
  • collate … （可选）：指定 “校验规则”—— 校验规则是 “比较文字的规则”，比如判断 “a” 和 “A” 是不是同一个字符、排序时谁在前，后面详细讲。

1.2 必掌握案例（面试常考手写）

1.2.1 创建默认配置数据库（测试场景，无需特殊配置时）

create database db1;

• 注意：未指定字符集和校验规则时，MySQL 会默认为my.ini中character-set-server和collation-server设置的字符集和校验规则。
• 避坑：如果之前已创建过 db1，再执行此命令会报错，所以正式场景要用 create database if not exists db1;。

• 如果已按 进行操作全链路打通中文支持，则默认字符集utf8mb4、校验规则utf8mb4_general_ci。
• MySQL 的 utf8 是 utf8mb3（仅支持 3 字节中文），遇到 emoji 或生僻字会存乱码，utf8mb4 才是真正的 UTF-8（支持 4 字节）。

实际开发中：

1. 通全链路：解决 “全局编码不一致” 的底层问题，减少隐性 bug。
2. 显式指定字符集：锁定库表编码，避免全局配置变动的影响（比如切换到一台 “没配置全链路” 的服务器，显式指定能避免 “继承服务器默认的 latin1 编码” 导致的乱码）。
3. 选对校验规则：适配业务场景（是否区分大小写）。

1.2.2 指定字符集（解决中文存储问题）

create database if not exists db2 charset=utf8mb4;

• 作用：避免中文乱码；
• 验证：创建后可通过查看数据库创建语句（show create database 数据库名;）确认字符集是否符合所设。

1.2.3 指定字符集 + 校验规则

create database if not exists db3 charset=utf8mb4 collate utf8mb4_general_ci;

• 作用：不区分大小写，用utf8mb4_general_ci；区分大小写，用utf8mb4_bin。

2. 字符集与校验规则（高频坑点）

2.1 查看系统默认配置

面试常问 “如何验证当前数据库的默认字符集”

2.1.1 查看默认字符集

show variables like 'character_set_database';

执行后如果显示utf8mb4，说明默认能存中文；如果是latin1，则默认存中文会乱码，创建数据库时必须手动设charset=utf8mb4。

2.1.2 查看默认校验规则

show variables like 'collation_database';

执行后如果显示utf8mb4_general_ci，说明默认不区分大小写；如果是utf8mb4_bin，则默认区分大小写。

2.2 校验规则对查询 / 排序的影响（核心考点）

2.2.1 不区分大小写（utf8mb4_general_ci，用户名登录等场景）

create database if not exists test1 collate utf8mb4_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排序 

2.2.2 区分大小写（utf8mb4_bin，密码存储等场景）

按 ASCII 码比较，大写字母ASCII < 小写。

create database if not exists test2 collate utf8mb4_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; 
-- 结果：A → B → a → b——按ASCII码大小排（大写字母ASCII比小写小，所以大写在前）

• 避坑：C++ 程序里如果查不到数据，先排查 “校验规则”—— 比如你存的是 “A”，查的时候用 “a”，如果数据库是utf8mb4_bin，就会查不到。

3. 数据库操纵（面试必问）

创建数据库后，要 “查看配置”“改字符集”“删无用库”，这部分是 “日常维护操作”，新手要掌握 “怎么验证”（避免配置没生效）和 “怎么安全操作”（避免误删）。

3.1 查看数据库

3.1.1 查看所有数据库

show databases;

• 执行后会列出系统自带数据库和所有已建数据库。

（information_schema、mysql、performance_schema、sys是 MySQL 安装后自动生成的数据库，负责存储 MySQL 自身的配置、权限、性能监控等信息，删了会导致 MySQL 无法正常运行。）

3.1.2 查看数据库创建语句（确认字符集）

-- 语法：show create database 数据库名;
show create database test1;

输出结果解读（面试要能解释）：

1. 反引号：库名加反引号是为了避免库名与关键字冲突，库名同关键字又不用反引号会报错 —— 起名时尽量避开关键字（比如用 select_db 代替 select）；
2. /*!40100 ... */：MySQL 的 “版本兼容语法”，意为 “如果当前 MySQL 版本大于 4.0.100，就执行里面的配置”——新手不用管这个，重点看里面的字符集和校验规则是否正确——这里只显示了字符集（utf8mb4），没显示校验规则，因为创建数据库test1时未明确指定校验规则，MySQL 会自动用my.ini中设置的默认校验规则。

3.2 修改数据库（新手别乱改）

数据库创建后，只能改 “字符集” 和 “校验规则”，不能改库名（改库名需删了重建，不推荐）。面试常考 “怎么改字符集” 和 “改后影响范围”。

3.2.1 语法

ALTER DATABASE db_name [alter_specification [, alter_specification] ...];

• alter_specification：和创建时一样，只能填CHARACTER SET 字符集名或COLLATE 校验规则名。

3.2.2 必掌握案例（修改字符集，兼容旧系统场景）

-- 需求：把test1的字符集从utf8改成gbk（比如旧系统用gbk，需要兼容）
alter database test1 charset=gbk;

-- 验证：改完后必须查创建语句，确认是否生效
show create database test1;
-- 生效结果：会显示“DEFAULT CHARACTER SET gbk”

• 避坑：修改只影响 “后续新建的表”，已存在的表字符集不会变！比如 person 表原来用 utf8，改完 test1 的字符集为 gbk 后，person 表还是 utf8—— 所以做项目时，一定要提前确定字符集，避免后期修改造成 “新旧表字符集不一致” 。

3.3 删除数据库（高危操作）

删除数据库后其中所有数据不可恢复，面试常考 “风险与规避措施”。

3.3.1 语法（必加IF NOT EXISTS避免报错）

DROP DATABASE [IF NOT EXISTS] db_name;

• 案例：删除没用的 test2 库

  drop database if not exists test2;

• 避坑：IF NOT EXISTS必须加 —— 如果 test2 已删，不加这个会报错，加了就 “安静跳过”，C++ 程序里要保证这种 “容错性”。

3.3.2 规避措施（新手必记）

1. 删除前一定要备份；
2. 生产环境（比如公司项目）绝对不能直接执行drop—— 必须走审批流程，确认没人用了再删；
3. 新手练习时，尽量删 “测试库”（比如 test1），别删重要的库。
4. 用IF NOT EXISTS避免误删不存在的库。

4. 备份与恢复

C++ 后端项目需保证数据不丢失，备份恢复是面试必问的 “如何保障数据安全” 的核心答案。面试常考 “怎么备份整个库”“没加 - B 参数怎么恢复”，新手要掌握 “命令执行环境” 和 “关键参数”。

4.1 备份（用mysqldump）

mysqldump是 MySQL 自带的备份工具，不是在 MySQL 终端里执行，而是在操作系统的命令行（Windows 的 CMD、Linux 的终端）里执行 —— 新手易错在 “在 MySQL 里输 mysqldump”，导致报错。

4.1.1 核心语法

# 完整格式（面试需记参数含义）
mysqldump -P端口 -u用户名 -p密码 -B 数据库名 > 备份文件路径

• -P端口（大写）：MySQL 的端口号默认是 3306，-P3306省略不写时默认为3306端口；
• -u用户名：登录 MySQL 的用户名，比如root（新手练习一般用 root）；
• -p密码（小写）：登录密码，注意**-p和密码之间无空格**，若其间有空格，需在执行命令后手动输入密码；
• -B（关键参数）：表示 “备份时包含创建数据库的语句”—— 如果不加 - B，备份文件里没有建库语句create database…，恢复时要手动建库；
• > 备份文件路径：把备份内容存到指定文件里，Windows 系统用/（如D:/xxx.sql），避免用\（易被识别为转义字符）；Linux 系统用/home/xxx.sql。。

4.1.2 必掌握案例

4.2 恢复（用source命令）

恢复就是把备份文件里的语句重新执行一遍，新手要注意 “路径正确” 和 “是否需要手动建库”。

恢复后需通过show databases;（验证库存在）、use 库名; select * from 表名;（验证数据存在）确认恢复成功。

5. 查看数据库连接情况（C++ 服务调优考点）

C++ 服务（比如后端接口）会和 MySQL 建立 “连接” 来操作数据，如果连接太多或有异常连接，会导致服务变慢甚至崩溃。新手要掌握 “怎么看连接” 和 “怎么排查问题”，面试常考 “如何判断数据库是否被入侵”“怎么优化连接”。

5.1 语法（必记，在 MySQL 终端执行）

show processlist;

• 作用：列出当前所有和 MySQL 建立的连接，包括 “谁连的”“连来做什么”“连了多久”。

5.2 输出解读与面试考点

典型输出：

| Id   | User   | Host        | db    | Command | Time  | State | Info           |
| 2    | root   | localhost   | test1 | Sleep   | 1386  |       | NULL           |
| 3    | root   | localhost   | NULL  | Query   | 0     | NULL  | show processlist |

5.2.1 关键字段解读（新手必懂）

1. User：登录 MySQL 的用户名（比如 root）—— 如果看到陌生用户名，可能数据库被入侵了；
2. Host：连接来源的主机（比如 是 “本地连接”，192.168.1.100 是 “远程电脑连接”）—— 如果看到陌生 IP，要警惕入侵；
3. Command：连接的状态（新手记以下两种）：
   • Sleep：连接 “空闲中”（没在执行查询、插入等操作）——C++ 项目里如果有大量 Sleep 连接（比如几十上百个），会浪费资源，需要优化 “连接池”（连接池是管理连接的工具）；
   • Query：连接正在执行 SQL 语句—— 如果某个 Query 的Time很长（比如超过 60 秒），说明这个 SQL 可能写得不好，会拖慢服务；
4. Time：连接持续的时间（单位：秒）—— 比如 Sleep 状态的连接 Time=1386，说明这个连接空闲了1386秒，要关掉。

5.2.2 应用场景

1. 排查异常连接：C++ 服务没报错，但数据库访问慢，执行show processlist，看是否有陌生 User/Host，或者长时间的 Query；
2. 排查性能问题：如果服务报 “连接数满”，执行show processlist，看是否有大量 Sleep 连接，优化连接池参数（比如减少最大连接数；或设置超时，比如让 10 分钟不用的连接自动关闭）。