Ubuntu 下 MySQL 数据库基础操作与字符集配置
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification: [] charset_name [] collation_name
Ubuntu 环境下 MySQL 数据库的创建、字符集校验规则设置、增删改查及备份恢复操作。涵盖系统默认字符集查看、collation 影响分析、mysqldump 逻辑备份命令及 processlist 连接监控。重点说明 utf8mb4 编码与不同排序规则对大小写敏感性的差异,以及表级加密等版本特性。

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification: [] charset_name [] collation_name
说明:
注意: 如果创建数据库时未指明数据库的编码格式或校验规则,则默认使用 MySQL 配置文件中对应的编码格式或校验规则。
采用默认的编码格式和校验规则创建数据库
创建数据库时不指明数据库的编码格式和校验规则即可。如下:
注意: 如果没有对 MySQL 的配置文件进行过修改,则默认的编码格式是 utf8,默认的校验规则是 utf8_general_ci。
指定 utf8 编码格式创建数据库

注意: SQL 中的 charset=utf8,也可以写成 character set=utf8 或 character set utf8。
指定 utf8 编码格式和 utf8_general_ci 校验规则创建数据库
创建数据库时通过 charset 和 collate 分别指明数据库的编码格式和校验规则即可。如下:

注意: SQL 中的 collate utf8_general_ci 也可以写成 collate=utf8_general_ci。
查看系统默认字符集以及校验规则
通过查看 MySQL 系统变量 variables 中的 character_set_database,可以得知系统默认的字符集。如下:

注意: 如果是在指定数据库下使用该 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 可以查看对应数据库的创建语句。如下:

说明一下:
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)mkdir -p ~/back
家目录下就创建了一个 back 目录

back 的拥有者和所属组都是 root,后面重定向时用户无法读写该目录所以要修改权限
sudo chown -R "$USER":"$USER" ~/back
chmod u+rwx ~/back
这两条命令的作用就是把你家目录下的 back 文件夹(以及里面的内容)改成归你自己所有、并确保你自己有读写执行权限

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 的用户。比如:

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

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online