Ubuntu 下 MySQL 数据库基础操作与字符集配置

注意： 如果是在指定数据库下使用该 SQL，则查看的是该数据库对应的字符集。

查看系统默认的字符集校验规则

通过查看 MySQL 系统变量 variables 中的 collation_database，可以得知系统默认的字符集校验规则。如下：

查看数据库支持的字符集

使用 show charset SQL 可以查看数据库支持的字符集。如下：

说明一下： 字符集主要是控制用什么语言，比如 utf8 就可以使用中文。

查看数据库支持的字符集校验规则

查看数据库支持的字符集校验规则

使用 show collation SQL 可以查看数据库支持的字符集校验规则。如下：

校验规则对数据库的影响

字符集编码格式和字符集校验规则的区别

• 字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
• 字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行比对。

比如我们存储数据的时候是按照 utf8mb4 的格式进行存储的，那么将来在对数据做比对时也必须按照 utf8mb4 的格式进行比对，因为'存数据'和'取数据'的方式必须保持一致。

注意：在对数据库当中的数据进行增删查改时，不可避免的需要进行数据的比对，因为在对数据做增删查改之前，都需要先通过比对的方式找到目标数据。

校验规则对数据库的影响

现在我们知道了，为了保证读取的数据和存储的数据是一致的，因此字符集编码格式和校验规则必须是对应的。但实际同一中字符集编码格式一般会有多种可选择的校验规则，比如 utf8 编码格式的校验规则有 utf8_general_ci、utf8_bin、utf8_unicode_ci 等校验规则。

使用不同的校验规则操作数据库中的数据可能会得到不同的结果，比如 utf8mb4_general_ci 校验规则在比对数据时是不区分大小写的，而 utf8mb4_bin 校验规则在对比数据时则是区分大小写的。

我们以两个库 test1 和 test2 来比对两种校验规则

不区分大小写用 utf8mb4_general_ci

创建一个数据库 test1，校验规则使用 utf8_general_ci[不区分大小写] 如下：

区分大小写用 utf8mb4_bin

创建一个数据库，校验规则使用 utf8mb4_bin[区分大小写] 如下：

进行查询

• 不区分大小写的查询以及结果

• 区分大小写的查询以及结果

结果排序

• 不区分大小写排序以及结果：

• 区分大小写排序以及结果：

注：A 的 ASCII 码为 65，a 的 ASCII 码值为 97

操纵数据库

查看数据库

使用 show database SQL 可以查看系统中所有的数据库。如下：

显示创建语句

使用 show create database 数据库名 SQL 可以查看对应数据库的创建语句。如下：

说明一下：

• MySQL 建议 SQL 中的关键字使用大写，但不是必须的。
• 数据库的名字加上反引号，是为了防止使用的数据库名与关键字冲突。
• 80016 指的是 MySQL 版本号：8.0.16。
• /*!80016 DEFAULT ENCRYPTION='N' */不是注释，作用是告诉 MySQL 8.0.16+，建表时默认不开启表级加密；而低版本 MySQL 不支持表级加密，所以直接忽略。

修改数据库

ALTER DATABASE db_name [alter_spacification [,alter_spacification]...] alter_spacification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name

说明：对数据库的修改主要指的是修改数据库的字符集，校验规则

比如将 db3 数据库字符集改成 gbk

删除数据库

删除数据库的 SQL 如下：

DROP DATABASE [IF EXISTS] db_name;

删除数据库后该数据库对应的文件夹就被删除了。比如：

并且删除数据库后，该数据库下的所有表也都会被级联删除，因此不要随意删除数据库。

备份和恢复

数据库备份

使用如下命令即可对指定数据库 (helloworld) 进行备份：

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock helloworld > ~/back/helloworld.sql

• mysqldump MySQL 官方'逻辑备份'工具，导出表结构 + 数据为可执行的 SQL。
• -u root 指定 MySQL 用户名为 root。
• -S /var/run/mysqld/mysqld.sock 指定 UNIX Socket 文件连接 MySQL（免密登录，本机直连，不走 TCP）

1. 创建目录

mkdir -p ~/back

家目录下就创建了一个 back 目录

back 的拥有者和所属组都是 root，后面重定向时用户无法读写该目录所以要修改权限

sudo chown -R "$USER":"$USER" ~/back
chmod u+rwx ~/back

这两条命令的作用就是把你家目录下的 back 文件夹（以及里面的内容）改成归你自己所有、并确保你自己有读写执行权限

2. 进行备份

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock helloworld > ~/back/helloworld.sql

打开 helloworld.sql 文件即可看到，文件中的内容实际就是我们在该数据库中执行的各种 SQL 命令，包括创建数据库、创建表、插入数据等 SQL 语句。如下：

数据库恢复

为了演示数据库恢复，我们先将刚才创建的数据库删除。如下：

这时让 MySQL 服务器执行如下命令即可对数据库进行恢复。如下：

# 1) 建库（MySQL 8.0 可用 0900 排序规则；若是 5.7/MariaDB 用 utf8mb4_general_ci）
sudo mysql -S /var/run/mysqld/mysqld.sock -e "CREATE DATABASE IF NOT EXISTS helloworld CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
# 2) 导入备份（把路径替换成你的绝对路径更稳妥）
sudo mysql -S /var/run/mysqld/mysqld.sock helloworld < /home/lzg/back/helloworld.sql

同时该数据库下的表，以及表当中的数据也都恢复出来了。如下：

当然还有省事的办法

备份

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock -B helloworld > ~/back/helloworld.sql

恢复

sudo mysql -S /var/run/mysqld/mysqld.sock < ~/back/helloworld.sql

查看连接情况

使用 show processlist SQL 即可查看当前连接 MySQL 的用户。比如：

说明一下：

• Id 列：一个标识，可以在 MySQL 中通过 kill id 杀死指定 id 的线程。
• User 列：显示当前用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句。
• Host 列：显示这个语句是从哪个 IP 的哪个端口上发出的，可用来追踪出问题语句的用户。
• db 列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为 NULL。
• Command 列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
• Time 列：表示该线程处于当前状态的时间，单位是秒。
• State 列：显示使用当前连接的 SQL 语句的状态。
• Info 列：一般记录的是线程执行的语句，默认只显示前 100 个字符，如果要看全部信息，需要使用 show full processlist。

show processlist 可以告诉我们当前有哪些用户连接到我们的 MySQL，如果查出某个用户不是你正常登录的，那么很有可能你的数据库被人入侵了，以后如果发现自己的数据库比较慢时，可以用这个 SQL 来查看数据库连接情况。