MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』

✨个人主页：****北 海
🎉所属专栏：****MySQL 学习
🎃操作环境：****CentOS 7.6 阿里云远程服务器
🎁软件版本：****MySQL 5.7.44

---

文章目录

---

1.创建数据库

创建数据库的语法为

CREATEDATABASE[IFNOTEXISTS] database_name;

[ ]

中的关键字可以选择不加，

IF NOT EXISTS

的意思是如果不存在才创建 数据库

登录

MySQL

后输入指令进行创建

mysql>createdatabaseifnotexists db1;

MySQL

中的语法不区分大小写，语法中通常 将关键字写成大写，方便学习和查看

注意：**如果数据库已存在，再次创建会报错，可以通过

if not exists

关键字将报错降低为警告**

在

Linux

中创建数据库的本质就是 **在

/var/lib/mysql

目录下创建了一个目录**

进入

db1

目录，可以看到里面只有一个后缀为

.opt

的配置文件

通过

cat

指令查看其中的类型，可以发现默认只有 **字符

character

** 和 **校对

collation

** 相关的配置信息，这俩其实就是 字符集 和 检验集，用于设置 数据库 中的编码规则

为什么要设置编码？

因为 数据库 是用来存放 表 的，而 表 是用来存放内容的，内容又是以不同编码格式存储的，所以需要先确定 数据库 中的编码规则，才能确保后续能正常使用

---

2.数据库中的编码问题

2.1.字符集与校验集

MySQL

中与编码相关的主要是 字符集 与 校验集

字符集 用于规定未来存储时的编码格式

校验集 则是规定数据查询时的校验规则

查看

MySQL

所有关于默认 字符集 与 校验集 的使用情况

mysql>show variables like'character_%';

mysql>show variables like'collation_%';

注：**

%

在

MySQL

表示匹配一个或任意多个字符，类似于

Linux

中的

*

**

其中 字符集 的使用涵盖了 客户端、连接、数据库、文件系统、结果、服务端、系统、目录，校验集 则主要用于 连接、数据库、服务端

主要都是使用

utf8

作为 字符集，

utf8_general_ci

作为 校验集

**校验集

utf8_general_ci

** 是适用于

utf8

的一种校验规则，除此之外还有其他校验规则（后面说）

---

接下来查看 数据库 默认使用的 字符集 与 编码集

mysql>show variables like'character_set_database';

mysql>show variables like'collation_database';

当前

MySQL

对于数据库的默认 字符集 是

utf8

，这是因为之前安装

MySQL

时在配置文件

my.cnf

设置的默认字符集就是

utf8

；默认的 校验集 则是

utf8_general_ci

以上是创建 数据库 时默认使用的 字符集 与 校验集，如果想要查看具体某个 数据库 的编码使用情况，可以

cd

到

/var/lib/mysql

目录中，在进入具体的数据库目录，查看后缀为

.opt

的配置文件信息即可

2.3.支持的字符集与校验集

MySQL

支持很多 字符集，通过指令查看

mysql>showcharset;

几乎所有主流 编码 它都支持，接下来看看支持的 校验集

mysql>show collation;

截图过长，只截取了部分

一种 字符集 可以有多种 校验集，所以这张表会很大，不同的 校验集 的校验规则有所不同，比如

utf8_general_ci

与

utf8_bin

的区别在于 是否忽略大小写

utf8_general_ci

是忽略大小写的，而

MySQL

服务端使用的 校验集 正是

utf8_general_ci

，这也就是解释了为什么在

MySQL

中输入指令可以忽略大小写；至于

utf8_bin

区分大小写，这两种 校验集 的演示放在后面细谈

2.4.自由设置字符集与校验集

配置文件

my.cnf

中设置的 字符集 配置信息就像一个 “缺省值”，如果用户不指定，就使用默认的，如果用户指定了，就优先使用用户指定的 字符集，校验集 也是如此

注意：校验集要能适用于字符集，否则会导致无法创建

创建 数据库 时指定编码相关信息的语法如下

CREATEDATABASE[IFNOTEXISTS] database_name [CHARSET=xxx |CHARACTERSET xxx][COLLATE xxx]

**

|

表示存在多种写法，可以任意选择其中一种**

接下来演示创建 数据库 时指定 字符集 与 校验集

这里指定字符集为

gbk

，校验集为

gdk_chinese_ci

mysql>createdatabaseifnotexists db2 charset=gbk collate gbk_chinese_ci;// 或者

mysql>createdatabaseifnotexists db2 characterset gbk collate gbk_chinese_ci;

---

如果只指定 字符集 或者 校验集 可以成功创建吗？

可以的，会根据 字符集 或者 校验集 推导出它的另一半

那如果指定毫不相关的 字符集 与 校验集 能成功创建 数据库 吗？

不行，会直接报错的，既然校验规则不适用，创建了也没意义，这就好比你往语文卷子上写英语，语文老师大概率是看不懂的，并且不会批改你的卷子

总之，

MySQL

支持在创建 数据库 时自由设置 字符集 与 校验集，只指定其中一方也能成功创建，因为

MySQL

可以进行推导，但如果指定的 字符集 与 校验集 没有关系，是无法创建 数据库 的，根据实际情况灵活设置

2.2.校验集对查询的影响

一个 字符集 拥有多个 校验集，也就意味着可以使用不同的校验规则，接下来演示

utf8_general_ci

与

utf8_bin

这两种不同的 校验集 对查询的影响

首先创建一个 校验集 为

utf8_general_ci

的 数据库，随后创建一张 表，并向 表 中插入多条数据，然后条件查询其中的结果

mysql>createdatabaseifnotexists test1 collate utf8_general_ci;

mysql>use test1;

mysql>createtable t (name varchar(32));

mysql>insertinto t values('a');
mysql>insertinto t values('A');
mysql>insertinto t values('b');
mysql>insertinto t values('B');
mysql>insertinto t values('c');
mysql>insertinto t values('C');

mysql>select*from t1 where name='a';

注意：**在对数据库进行操作前，需要先

use database_name

指明数据库**

执行结果如下

注：**

where

是条件查询语句，可以用来指定查看规则，这里的

where name='a'

是查询

t

表中

name

为

a

的行信息**

可以看到最终查询结果为

a

、

A

，明明只要求

name='a'

，但最终查出来

A

，这是因为

utf8_general_ci

校验集 在进行校验时，是 不区分大小写 的

接下来看看

utf8_bin

校验集 对查询的影响

同样的创建数据库、创建表、插入数据、查询结果

mysql>createdatabaseifnotexists test2 collate utf8_bin;

mysql>use test2;

mysql>createtable t (name varchar(32));

mysql>insertinto t values('a');
mysql>insertinto t values('A');
mysql>insertinto t values('b');
mysql>insertinto t values('B');
mysql>insertinto t values('c');
mysql>insertinto t values('C');

mysql>select*from t1 where name='a';

可以看到，当 校验集 为

utf8_bin

时，查询结果是 区分大小写 的，最终查出了

a

这行数据

这里想说的是 **不同校验集对查询结果是有差异的，需要结合具体业务场景选择校验集，编码集也是如此，不过一般情况下都不需要指定，使用默认的

utf8

和

utf8_general_ci

就行了**

---

3.查看数据库

可以查看当前

MySQL

中已经创建了哪些 数据库

mysql>showdatabases;

当然也可以直接去

/var/lib/mysql

目录中查看

cd /var/lib/mysql

ll

存在这么多 数据库，如何知道自己当前处于哪个数据库中？

通过

database()

函数查看，这是一个函数，可以用于查看当前所处 数据库（个人猜测是调用了显示当前所处路径的相关接口实现）

mysql>selectdatabases();

可以看到当前处于

test2

数据库中

如何查看 数据库 创建时的详细信息？

可以通过

show

进行查看，这里看看

test2

的创建信息

mysql>showcreatedatabase test2;// 或者

mysql>showcreatedatabase test2 \G

在后面加上

\G

是为了格式化显示，让显示结果更加清晰

可以看到创建

test2

时的详细信息，比如指定的 字符集 和 编码集

/*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */

是什么意思？
**这是句话是为了确保兼容性，因为在

MySQL 4.1

之前，是无法在创建数据库时指定字符集和校验集的。所以这句话的意思是 如果

MySQL

版本高于

4.1

就加上这句话（创建字符集和校验集），否则就不加**

---

4.修改数据库

数据库 可以修改很多东西（不只是编码格式），修改相关的语法如下

修改数据库名（

MySQL 5.1.23

之前的版本适用）

RENAMEDATABASE old_name TO new_name;

出于安全考虑，这个语法在当前的

MySQL

中已经不被支持了，因为 数据库 更名后，上层应用在使用 数据库 时会受到影响，比较稳妥的更名方法是 备份数据、新建数据库（更名）、导入数据（还是不推荐改名）

数据库轻易不要删除，数据库轻易不要改名

修改编码格式

ALTERDATABASE database_name [alter_spacification [,alter_spacification]...];

之前的

test2

数据使用的是

utf8

和

utf8_bin

，现在将其修改为

gbk

和

gbk_chinese_ci

mysql>alterdatabase test2 charset=gbk collate gbk_chinese_ci;

除此之外，还可以修改 数据库所有者、启用/禁用数据库的自动提交等，这些东西需要结合后面的相关知识理解，这里就不再阐述

---

5.删除数据库

在

Linux

中，数据库 的本质就是一个 目录，因此 数据库 也是可以删除的，具体语法如下

DROPDATABASE[IFEXISTS] database_ name;

在这里

IF EXISTS

表示检查 数据库，存在才删除，删除不存在的 数据库 是会报错的，加了

IF EXISTS

后可以避免报错，转为警告

删除之前创建的数据库

执行删除之后的结果:

• 数据库内部看不到对应的数据库
• 对应的数据库文件夹被删除，级联删除（里面的数据表全部被删）

注意：不要轻易删除数据库，数据库删除后很难恢复

为了避免误删 数据库，通常需要将 数据库 进行备份

---

6.数据库的备份与恢复

有一种简单粗暴的备份方式：直接将对应的目录打包，需要使用时解压至指定目录即可

这种方式 严重不推荐，原因是恢复时可能会出现问题，假设恢复至版本更低的

MySQL

中，会出现各种奇怪的问题，毕竟老版本没有新特性，还有就是这种做法太暴力了

推荐使用

MySQL

提供的备份工具

mysqldump

进行备份，具体语法为

mysqldump -u 用户 -p-P 端口 -B 待备份的数据库 > 目标路径(含目标文件)

比如把之前创建的

test2

数据库进行备份

注意：如果有人在使用该数据库，需要先断开连接

mysqldump -u root -p-P8080-B test2 > /home/Yohifo/MySQL/test2.sql

回车并输入密码后，对应的数据库就备份成功了

看看备份文件是什么内容

cat test2.sql

其实就是一些 数据库 的配置信息 + 执行过的

SQL

语句

---

有备份就有恢复，恢复的语法如下

mysql> SOURCE 路径;

接下来先删除

MySQL

中的

test2

数据库

mysql>showdatabases;

mysql>dropdatabaseifexists test2;

mysql>showdatabases;

然后从

Linux

中读取备份文件，并进行恢复

mysql> source /home/Yohifo/MySQL/test2.sql

一瞬间执行了很多条语句，并且都是成功状态，接下来查看是否存在

test2

数据库

数据库

test2

已经成功恢复，包括其中的 表 和 表中的数据 都已经恢复了

所以备份与恢复 数据库 还是比较简单的，只需要从

MySQL

中获取备份后的

sql

文件，可将该备份文件进行传输，其他

MySQL

客户端只需要

source

即可轻松恢复整个 数据库

---

可以只备份 数据库 中的 表，语法如下

mysqldump -u root -p-P 端口 -B 数据库名 表名1 > 目标路径(含文件)

也可以同时备份多个 数据库

mysqldump -u 用户 -p-P 端口 -B 数据库1 数据库2 数据库3 > 目标路径(含目标文件)

语法中的

-B

含义是什么？
**

-B

表示备份时，将创建数据库的语句也进行了备份，恢复时可以一键恢复；但如果不加

-B

选项，恢复时就需要先创建数据库，再

use

数据库，然后才能

source

**

---

7.查看数据库的连接情况

MySQL

支持多用户访问，可以通过指令查看当前的使用情况

mysql>show processlist;

支持 本地连接 和 远程连接（需要进行设置）