MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』_百度小程序_上海毫米网络优化公司

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

相关推荐recommended

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

作者：mmseoamin日期：2023-12-11

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

MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』,成就一亿技术人,第1张

文章目录

1.创建数据库
2.数据库中的编码问题
- 2.1.字符集与校验集
- 2.3.支持的字符集与校验集
- 2.4.自由设置字符集与校验集
- 2.2.校验集对查询的影响
- 3.查看数据库
- 4.修改数据库
- 5.删除数据库
- 6.数据库的备份与恢复
- 7.查看数据库的连接情况
  
  1.创建数据库
  
  创建数据库的语法为
```
CREATE DATABASE [IF NOT EXISTS] database_name;
```
  [ ] 中的关键字可以选择不加，IF NOT EXISTS 的意思是如果不存在才创建数据库
  
  登录 MySQL 后输入指令进行创建
```
mysql> create database if not exists 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> show charset;
```
  几乎所有主流编码它都支持，接下来看看支持的校验集
```
mysql> show collation;
```
  截图过长，只截取了部分
  
  一种字符集可以有多种校验集，所以这张表会很大，不同的校验集的校验规则有所不同，比如 utf8_general_ci 与 utf8_bin 的区别在于是否忽略大小写
  
  utf8_general_ci 是忽略大小写的，而 MySQL 服务端使用的校验集正是 utf8_general_ci，这也就是解释了为什么在 MySQL 中输入指令可以忽略大小写；至于 utf8_bin 区分大小写，这两种校验集的演示放在后面细谈
  
  2.4.自由设置字符集与校验集
  
  配置文件 my.cnf 中设置的字符集配置信息就像一个 “缺省值”，如果用户不指定，就使用默认的，如果用户指定了，就优先使用用户指定的字符集，校验集也是如此
  
  注意：校验集要能适用于字符集，否则会导致无法创建
  
  创建数据库时指定编码相关信息的语法如下
```
CREATE DATABASE [IF NOT EXISTS] database_name [CHARSET=xxx | CHARACTER SET xxx] [COLLATE xxx]
```
  | 表示存在多种写法，可以任意选择其中一种
  
  接下来演示创建数据库时指定字符集与校验集
  
  这里指定字符集为 gbk，校验集为 gdk_chinese_ci
```
mysql> create database if not exists db2 charset=gbk collate gbk_chinese_ci;
// 或者
mysql> create database if not exists db2 character set gbk collate gbk_chinese_ci;
```
  如果只指定字符集或者校验集可以成功创建吗？
  
  可以的，会根据字符集或者校验集推导出它的另一半
  
  那如果指定毫不相关的字符集与校验集能成功创建数据库吗？
  
  不行，会直接报错的，既然校验规则不适用，创建了也没意义，这就好比你往语文卷子上写英语，语文老师大概率是看不懂的，并且不会批改你的卷子
  
  总之，MySQL 支持在创建数据库时自由设置字符集与校验集，只指定其中一方也能成功创建，因为 MySQL 可以进行推导，但如果指定的字符集与校验集没有关系，是无法创建数据库的，根据实际情况灵活设置
  
  2.2.校验集对查询的影响
  
  一个字符集拥有多个校验集，也就意味着可以使用不同的校验规则，接下来演示 utf8_general_ci 与 utf8_bin 这两种不同的校验集对查询的影响
  
  首先创建一个校验集为 utf8_general_ci 的数据库，随后创建一张表，并向表中插入多条数据，然后条件查询其中的结果
```
mysql> create database if not exists test1 collate utf8_general_ci;
mysql> use test1;
mysql> create table t (name varchar(32));
mysql> insert into t values ('a');
mysql> insert into t values ('A');
mysql> insert into t values ('b');
mysql> insert into t values ('B');
mysql> insert into t values ('c');
mysql> insert into 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> create database if not exists test2 collate utf8_bin;
mysql> use test2;
mysql> create table t (name varchar(32));
mysql> insert into t values ('a');
mysql> insert into t values ('A');
mysql> insert into t values ('b');
mysql> insert into t values ('B');
mysql> insert into t values ('c');
mysql> insert into t values ('C');
mysql> select * from t1 where name='a'; 
```
  可以看到，当校验集为 utf8_bin 时，查询结果是区分大小写的，最终查出了 a 这行数据
  
  这里想说的是不同校验集对查询结果是有差异的，需要结合具体业务场景选择校验集，编码集也是如此，不过一般情况下都不需要指定，使用默认的 utf8 和 utf8_general_ci 就行了
  
  3.查看数据库
  
  可以查看当前 MySQL 中已经创建了哪些数据库
```
mysql> show databases;
```
  当然也可以直接去 /var/lib/mysql 目录中查看
```
cd /var/lib/mysql
ll
```
  存在这么多数据库，如何知道自己当前处于哪个数据库中？
  
  通过 database() 函数查看，这是一个函数，可以用于查看当前所处数据库（个人猜测是调用了显示当前所处路径的相关接口实现）
```
mysql> select databases();
```
  可以看到当前处于 test2 数据库中
  
  如何查看数据库创建时的详细信息？
  
  可以通过 show 进行查看，这里看看 test2 的创建信息
```
mysql> show create database test2;
// 或者
mysql> show create database test2 \G
```
  在后面加上 \G 是为了格式化显示，让显示结果更加清晰
  
  可以看到创建 test2 时的详细信息，比如指定的字符集和编码集
  
  /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ 是什么意思？
  这是句话是为了确保兼容性，因为在 MySQL 4.1 之前，是无法在创建数据库时指定字符集和校验集的。所以这句话的意思是如果 MySQL 版本高于 4.1 就加上这句话（创建字符集和校验集），否则就不加
  
  4.修改数据库
  
  数据库可以修改很多东西（不只是编码格式），修改相关的语法如下
  
  修改数据库名（MySQL 5.1.23 之前的版本适用）
```
RENAME DATABASE old_name TO new_name;
```
  出于安全考虑，这个语法在当前的 MySQL 中已经不被支持了，因为数据库更名后，上层应用在使用数据库时会受到影响，比较稳妥的更名方法是备份数据、新建数据库（更名）、导入数据（还是不推荐改名）
  
  数据库轻易不要删除，数据库轻易不要改名
  
  修改编码格式
```
ALTER DATABASE database_name [alter_spacification [,alter_spacification]...];
```
  之前的 test2 数据使用的是 utf8 和 utf8_bin，现在将其修改为 gbk 和 gbk_chinese_ci
```
mysql> alter database test2 charset=gbk collate gbk_chinese_ci;
```
  除此之外，还可以修改数据库所有者、启用/禁用数据库的自动提交等，这些东西需要结合后面的相关知识理解，这里就不再阐述
  
  5.删除数据库
  
  在 Linux 中，数据库的本质就是一个目录，因此数据库也是可以删除的，具体语法如下
```
DROP DATABASE [IF EXISTS] database_ name;
```
  在这里 IF EXISTS 表示检查数据库，存在才删除，删除不存在的数据库是会报错的，加了 IF EXISTS 后可以避免报错，转为警告
  
  删除之前创建的数据库
  
  执行删除之后的结果:
  - 数据库内部看不到对应的数据库
  - 对应的数据库文件夹被删除，级联删除（里面的数据表全部被删）
    注意：不要轻易删除数据库，数据库删除后很难恢复
    
    为了避免误删数据库，通常需要将数据库进行备份
    
    6.数据库的备份与恢复
    
    有一种简单粗暴的备份方式：直接将对应的目录打包，需要使用时解压至指定目录即可
    
    这种方式严重不推荐，原因是恢复时可能会出现问题，假设恢复至版本更低的 MySQL 中，会出现各种奇怪的问题，毕竟老版本没有新特性，还有就是这种做法太暴力了
    
    推荐使用 MySQL 提供的备份工具 mysqldump 进行备份，具体语法为
```
mysqldump -u 用户 -p -P 端口 -B 待备份的数据库 > 目标路径(含目标文件)
```
    比如把之前创建的 test2 数据库进行备份
    
    注意：如果有人在使用该数据库，需要先断开连接
```
mysqldump -u root -p -P 8080  -B test2 > /home/Yohifo/MySQL/test2.sql
```
    回车并输入密码后，对应的数据库就备份成功了
    
    看看备份文件是什么内容
```
cat test2.sql
```
    其实就是一些数据库的配置信息 + 执行过的 SQL 语句
    
    有备份就有恢复，恢复的语法如下
```
mysql> SOURCE 路径; 
```
    接下来先删除 MySQL 中的 test2 数据库
```
mysql> show databases;
mysql> drop database if exists test2;
mysql> show databases;
```
    然后从 Linux 中读取备份文件，并进行恢复
```
mysql> source /home/Yohifo/MySQL/test2.sql
```
    一瞬间执行了很多条语句，并且都是成功状态，接下来查看是否存在 test2 数据库
    
    数据库 test2 已经成功恢复，包括其中的表和表中的数据都已经恢复了
    
    所以备份与恢复数据库还是比较简单的，只需要从 MySQL 中获取备份后的 sql 文件，可将该备份文件进行传输，其他 MySQL 客户端只需要 source 即可轻松恢复整个数据库
    
    可以只备份数据库中的表，语法如下
```
mysqldump -u root -p -P 端口 数据库名 表名1 > 目标路径(含文件)
```
    注意：备份表时无需加 -B 选项
    
    也可以同时备份多个数据库
```
mysqldump -u 用户 -p -P 端口 -B 数据库1 数据库2 数据库3 > 目标路径(含目标文件)
```
    语法中的 -B 含义是什么？
    -B 表示备份时，将创建数据库的语句也进行了备份，恢复时可以一键恢复；但如果不加 -B 选项，恢复时就需要先创建数据库，再 use 数据库，然后才能 source
    
    7.查看数据库的连接情况
    
    MySQL 支持多用户访问，可以通过指令查看当前的使用情况
```
mysql> show processlist;
```
    支持本地连接和远程连接（需要进行设置）

上一篇：org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyR

下一篇：SpringBoot+mysql+vue实现大学生健康档案管理系统前后端分离