MySQL数据库库级操作｜创建、字符集、修改删除、备份恢复

前言

在上一篇文章中，我们入门了MySQL的基础概念、SQL分类、数据库层级关系。本篇将聚焦MySQL核心库级操作，也是后端开发、数据库运维的基础必学内容。

本文将从零讲解数据库的创建、字符集与校验规则核心原理、数据库查看/修改/删除、数据备份还原、连接状态监控等全套实操命令，搭配完整案例、效果演示、避坑技巧，零基础也能轻松看懂，可直接作为面试、开发常备笔记。

一、数据库创建：标准语法与实战案例

1.1 完整创建语法

MySQL提供标准化的数据库创建语句，支持容错创建、自定义字符集、自定义校验规则，语法结构如下：

2. create database [if not exists] db_name
3. [default character set charset_name]
4. [default collate collation_name];

复制代码

1.2 核心参数详解

• IF NOT EXISTS：可选容错参数，仅当数据库不存在时创建，避免重复创建数据库报错，开发必备

• CHARACTER SET：指定数据库字符集，决定数据库可存储的文字类型（中文、英文、特殊符号等）

• COLLATE：指定字符集校验规则，决定数据的查询匹配、排序规则

• SQL关键字推荐大写（行业规范），数据库名、字段名小写，可读性更强

1.3 分层实战案例

默认情况下，MySQL新建数据库未指定参数时，默认字符集为 utf8，默认校验规则为 utf8_general_ci。

2. # 案例1：最简创建数据库（使用系统默认配置）
3. create database db1;
5. # 案例2：指定utf8字符集创建数据库
6. create database db2 character set utf8;
8. # 案例3：同时指定字符集+校验规则（企业常用规范写法）
9. create database db3 character set utf8 collate utf8_general_ci;

复制代码

二、字符集与校验规则：90%新手踩坑的核心点

字符集和校验规则是数据库的底层基础配置，字符集负责“存什么数据”，校验规则负责“怎么比数据、怎么排序数据”，直接影响业务数据查询结果。

2.1 常用查询命令

2. # 查看数据库默认字符集
3. show variables like 'character_set_database';
5. # 查看数据库默认校验规则
6. show variables like 'collation_database';
8. # 查看MySQL支持的所有字符集
9. show charset;
11. # 查看MySQL支持的所有校验规则
12. show collation;

复制代码

2.2 两种核心校验规则实战对比

日常开发最常用两种utf8校验规则，行为完全不同，适配不同业务场景：

• utf8_general_ci：不区分大小写（ci = case insensitive），适用于普通文本、昵称、文章内容

• utf8_bin：二进制精准匹配，区分大小写，适用于账号、验证码、密钥、密码等敏感数据

场景1：不区分大小写（utf8_general_ci）

2. # 创建不区分大小写的数据库
3. create database test1 collate utf8_general_ci;
4. use test1;
6. # 创建测试表并插入大小写数据
7. create table person(name varchar(20));
8. insert into person values('a'),('A'),('b'),('B');
10. # 查询：匹配小写a，会同时查出 a 和 A
11. select * from person where name = 'a';
13. # 排序：忽略大小写，a/A 优先于 b/B
14. select * from person order by name;

复制代码

执行结果：查询命中两条数据，排序结果为 a、A、b、B。

场景2：区分大小写（utf8_bin）

2. # 创建区分大小写的数据库
3. create database test2 collate utf8_bin;
4. use test2;
6. # 新建表并插入相同数据
7. create table person(name varchar(20));
8. insert into person values('a'),('A'),('b'),('B');
10. # 查询：精准匹配小写a，仅查出 a
11. select * from person where name = 'a';
13. # 排序：按二进制编码排序，大写字母优先
14. select * from person order by name;

复制代码

执行结果：查询仅命中小写a，排序结果为 A、B、a、b。

三、数据库常规操作：查看、修改、删除

3.1 查看所有数据库

2. show databases;

复制代码

3.2 查看数据库创建详情

2. # 语法
3. show create database 数据库名;
5. # 示例
6. show create database mytest;

复制代码

关键知识点解析：

1. 结果中的 反引号 ``：包裹数据库名，避免数据库名与SQL关键字冲突

2. /*!40100 ... */：不是注释，是版本兼容语法，MySQL版本大于4.01时自动执行该配置

3.3 修改数据库配置

重要限制：MySQL不支持直接修改数据库名称，数据库修改仅能修改 字符集、校验规则。

语法

2. alter database db_name
3. [default] character set charset_name
4. [default] collate collation_name;

复制代码

实战示例

2. # 将mytest数据库字符集修改为gbk
3. alter database mytest character set gbk;

复制代码

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

语法

2. # 容错删除，数据库存在才删除，避免报错
3. drop database if exists db_name;

复制代码

删除后果（必记）

• MySQL服务中该数据库直接消失

• 磁盘上对应的数据库文件夹被彻底删除

• 级联删除：库内所有数据表、所有数据全部清空，不可恢复

生产环境禁忌：严禁随意执行 drop database，删除前必须完整备份数据！

四、数据库备份与恢复（运维必备）

备份恢复命令为系统命令，需退出MySQL客户端，在电脑/服务器终端执行，不可在mysql> 命令行运行。

4.1 数据库备份（mysqldump）

基础语法

2. # 备份单个完整数据库
3. mysqldump -u用户名 -p密码 -B 数据库名 > 备份文件.sql

复制代码

实战示例

2. # 备份mytest数据库，生成mytest.sql备份文件
3. mysqldump -uroot -p -B mytest > mytest.sql

复制代码

备份文件内部包含：完整的建库、建表、插入数据SQL语句，可完整还原数据库。

拓展备份场景

2. # 仅备份数据库中的指定数据表
3. mysqldump -uroot -p mytest 表1 表2 > table_backup.sql
5. # 同时备份多个数据库
6. mysqldump -uroot -p -B db1 db2 db3 > multi_db_backup.sql

复制代码

4.2 数据库还原

提供两种通用还原方式，适配不同场景：

方式1：终端直接还原（推荐）

2. mysql -uroot -p < mytest.sql

复制代码

方式2：MySQL客户端内还原

2. source 完整文件路径/mytest.sql;

复制代码

4.3 备份还原核心注意事项

• 带 -B 参数备份：备份文件自带建库语句，还原时无需手动创建数据库，直接还原即可

• 不带 -B 参数备份：备份文件无建库语句，还原必须先手动创建空数据库，进入数据库后再执行source还原

五、查看数据库连接状态（排查卡顿、入侵）

当数据库访问卡顿、响应缓慢，或怀疑数据库被非法访问时，可通过命令查看实时连接情况。

执行命令

2. show processlist;

复制代码

核心作用

1. 查看当前所有连接MySQL的用户、登录IP、连接状态、正在执行的SQL

2. 快速识别非法陌生连接，排查数据库入侵风险

3. 排查数据库卡顿原因，定位慢查询、过量连接问题

六、全文核心总结

1. 数据库创建核心是字符集+校验规则，普通业务用utf8_general_ci，账号密码等精准匹配场景用utf8_bin

2. 数据库仅支持修改字符集和校验规则，不支持直接改名，删除操作为高危不可逆操作

3. 备份mysqldump命令的 -B 参数 是还原便捷性的关键，生产备份优先携带

4. SHOW PROCESSLIST 是数据库运维高频命令，可快速排查性能、安全问题

5. 所有库级操作遵循规范：容错创建、谨慎删除、操作必备份