第 A 章 Linux 下 MySQL 的安装与使用

作者: Brinnatt 分类: mysql 经验专题 发布时间: 2023-01-13 16:39

A1、安装前说明

A1.1、Linux 系统及工具的准备

  • 安装并启动好两台虚拟机：CentOS 7

    • 掌握克隆虚拟机的操作
    • mac 地址
    • 主机名
    • ip 地址
    • UUID

  • 安装有 Xshell 和 Xftp 等访问 CentOS 系统的工具

  • CentOS 6 和 CentOS 7 在 MySQL 的使用中的区别

    1. 防火墙：6是iptables，7是firewalld
2. 启动服务的命令：6是service，7是systemctl

A1.2、查看是否安装过 MySQL

如果你是用 rpm 安装，检查一下 RPM PACKAGE：

rpm -qa | grep -i mysql  # -i 忽略大小写

检查 mysql service：

systemctl status mysqld.service

如果存在 mysql-libs 的旧版本包，显示如下：

[root@master1 ~]# rpm -qa|grep -i mysql
mysql-community-client-plugins-8.0.31-1.el7.x86_64
mysql-community-libs-8.0.31-1.el7.x86_64
mysql-community-server-8.0.31-1.el7.x86_64
mysql80-community-release-el7-7.noarch
mysql-community-common-8.0.31-1.el7.x86_64
mysql-community-client-8.0.31-1.el7.x86_64
mysql-community-icu-data-files-8.0.31-1.el7.x86_64
mysql-community-libs-compat-8.0.31-1.el7.x86_64

如果不存在 mysql-lib 的版本，显示如下：

[root@master1 ~]# rpm -qa|grep -i mysql
[root@master1 ~]#

A1.3、MySQL 的卸载

  1. 关闭 mysql 服务

    systemctl stop mysqld.service

  2. 查看当前 mysql 安装状况

    rpm -qa | grep -i mysql
# 或
yum list installed | grep mysql

  3. 卸载上述命令查询出的已安装程序

    yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

    务必卸载干净，反复执行 rpm -qa | grep -i mysql 确认是否有卸载残留

  4. 删除 mysql 相关文件

    # 查找相关文件
find / -name mysql

# 删除上述命令查找出的相关文件
rm -rf xxx

  5. 删除 my.cnf

    rm -rf /etc/my.cnf

A2、MySQL 的 Linux 版安装

A2.1、MySQL 的 4 大版本

MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。

MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用 30 天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。

MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个 MySQL Server 封装成一个 Server。需要在社区版或企业版的基础上使用。

MySQL Cluster CGE 高级集群版，需付费。

  • 截止目前，官方最新版本为 8.0.31 。此前，8.0.0 在 2016.9.12日就发布了。
  • 本课程中主要使用 8.0.31 版本 。同时为了更好的说明 MySQL8.0 新特性，还会安装 MySQL5.7 版本，作为对比。

此外，官方还提供了 MySQL Workbench（GUITOOL）一款专为 MySQL 设计的 ER/数据库建模工具。它是著名的数据库设计工具 DBDesigner4 的继任者。MySQLWorkbench 又分为两个版本，分别是 社区版（MySQL Workbench OSS）、商用版（MySQL WorkbenchSE）。

A2.2、下载 MySQL 指定版本

  1. 下载地址，官网：https://www.mysql.com

  2. 打开官网，点击 DOWNLOADS，然后，点击 MySQL Community(GPL) Downloads

    mysql_downloads

  3. 点击 MySQL Community Server

    mysql_downloads_1

  4. 在 General Availability(GA) Releases 中选择适合的版本

    • 如果安装 Windows 系统下 MySQL，推荐下载 MSI 安装程序；点击 Go to Download Page 进行下载即可
    • Windows 下的 MySQL 安装有两种安装程序
      • mysql-installer-web-community-8.0.31.0.msi 下载程序大小：2.4M；安装时需要联网安装组件。
      • mysql-installer-community-8.0.31.0.msi 下载程序大小：435.7M；安装时离线安装即可。推荐。

  5. Linux 系统下安装 MySQL 的几种方式

    • 方式 1：rpm 命令，使用 rpm 命令安装扩展名为 .rpm 的软件包。
    • 方式 2：yum 命令，需联网，从互联网获取的 yum 源，直接使用 yum 命令安装。

    • 方式 3：编译安装源码包，针对 tar.gz 这样的压缩格式，要用 tar 命令来解压；如果是其它压缩格式，就使用其它命令。

      Linux 系统下安装 MySQL，官方给出多种安装方式：

      安装方式 特点
      rpm 安装简单，灵活性差，无法灵活选择版本、升级
      rpm repository 安装包极小，版本安装简单灵活，升级方便，需要联网安装
      通用二进制包 安装比较复杂，灵活性高，平台通用性好
      源码包 安装最复杂，时间长，参数设置灵活，性能好

      这里不能直接选择 CentOS 7 系统的版本，所以选择与之对应的 Red Hat Enterprise Linux 7 直接点 Download 下载 RPM Bundle 全量包。包括了所有下面的组件。不需要一个一个下载了。

      mysql_downloads_2

      下载的 tar 包，用压缩工具打开，直接在 centos7 命令行下执行 yum localinstall *.rpm 安装。

A2.3、CentOS 7 下 MySQL 安装过程

  1. 将安装程序拷贝到 /opt 目录下

    [root@guest opt]# ls
mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar

    解压 mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar 包，查看一下包含哪些 rpm 包。

    [root@guest opt]# tar xf mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar 
[root@guest opt]# ls
mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.31-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm
mysql-community-common-8.0.31-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.31-1.el7.x86_64.rpm
mysql-community-devel-8.0.31-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.31-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm
mysql-community-libs-8.0.31-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm
mysql-community-server-8.0.31-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.31-1.el7.x86_64.rpm
mysql-community-test-8.0.31-1.el7.x86_64.rpm

  2. 直接使用 yum localinstall 安装，yum 自动解决安装依赖

    [root@guest opt]# yum localinstall *.rpm -y
......
Installed:
 mysql-community-client.x86_64 0:8.0.31-1.el7                                               
 mysql-community-client-plugins.x86_64 0:8.0.31-1.el7                                       
 mysql-community-common.x86_64 0:8.0.31-1.el7                                               
 mysql-community-debuginfo.x86_64 0:8.0.31-1.el7                                            
 mysql-community-devel.x86_64 0:8.0.31-1.el7                                                
 mysql-community-embedded-compat.x86_64 0:8.0.31-1.el7                                      
 mysql-community-icu-data-files.x86_64 0:8.0.31-1.el7                                       
 mysql-community-libs.x86_64 0:8.0.31-1.el7                                                 
 mysql-community-libs-compat.x86_64 0:8.0.31-1.el7                                          
 mysql-community-server.x86_64 0:8.0.31-1.el7                                               
 mysql-community-server-debug.x86_64 0:8.0.31-1.el7                                         
 mysql-community-test.x86_64 0:8.0.31-1.el7                                                 

Dependency Installed:
 keyutils-libs-devel.x86_64 0:1.5.8-3.el7      krb5-devel.x86_64 0:1.15.1-55.el7_9         
 libaio.x86_64 0:0.3.109-13.el7                libcom_err-devel.x86_64 0:1.42.9-19.el7     
 libkadm5.x86_64 0:1.15.1-55.el7_9             libselinux-devel.x86_64 0:2.5-15.el7        
 libsepol-devel.x86_64 0:2.5-10.el7            libverto-devel.x86_64 0:0.2.5-4.el7         
 openssl-devel.x86_64 1:1.0.2k-25.el7_9        pcre-devel.x86_64 0:8.32-17.el7             
 perl-Data-Dumper.x86_64 0:2.145-3.el7         perl-JSON.noarch 0:2.59-2.el7               
 perl-Test-Harness.noarch 0:3.28-3.el7         perl-Test-Simple.noarch 0:0.98-243.el7      
 zlib-devel.x86_64 0:1.2.7-20.el7_9           

Dependency Updated:
 krb5-libs.x86_64 0:1.15.1-55.el7_9              openssl.x86_64 1:1.0.2k-25.el7_9          
 openssl-libs.x86_64 1:1.0.2k-25.el7_9           zlib.x86_64 0:1.2.7-20.el7_9              

Complete!

A2.4、查看 MySQL 版本

执行如下命令，如果成功表示安装 mysql 成功。类似 java -version 如果打出版本等信息。

[root@guest ~]# mysql --version
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

执行如下命令，查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到。

[root@guest ~]# rpm -qa|grep -i mysql
mysql-community-common-8.0.31-1.el7.x86_64
mysql-community-client-8.0.31-1.el7.x86_64
mysql-community-server-8.0.31-1.el7.x86_64
mysql-community-test-8.0.31-1.el7.x86_64
mysql-community-embedded-compat-8.0.31-1.el7.x86_64
mysql-community-client-plugins-8.0.31-1.el7.x86_64
mysql-community-icu-data-files-8.0.31-1.el7.x86_64
mysql-community-devel-8.0.31-1.el7.x86_64
mysql-community-libs-compat-8.0.31-1.el7.x86_64
mysql-community-libs-8.0.31-1.el7.x86_64
mysql-community-server-debug-8.0.31-1.el7.x86_64
mysql-community-debuginfo-8.0.31-1.el7.x86_64

A2.5、服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：

mysqld --initialize --user=mysql

说明：--initialize 选项默认以安全模式来初始化，则会为 root 用户生成一个密码并将 该密码标记为过期，登录后你需要设置一个新的密码。生成的 临时密码 会往日志中记录一份。

这一步可以不用专门执行，直接 systemctl start mysqld 即可自动初始化。

查看密码：

[root@guest ~]# cat /var/log/mysqld.log 
2022-12-31T03:21:52.514059Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31) initializing of server in progress as process 13375
2022-12-31T03:21:52.521950Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-31T03:21:52.837124Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-31T03:21:53.897098Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /xQRI:B,i4wk

root@localhost：后面就是初始化的密码。

A2.6、启动 MySQL，查看状态

# 加不加.service后缀都可以
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service

mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

[root@guest ~]# systemctl start mysqld.service
[root@guest ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-12-31 11:25:46 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 13448 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 13473 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─13473 /usr/sbin/mysqld

Dec 31 11:25:45 guest systemd[1]: Starting MySQL Server...
Dec 31 11:25:46 guest systemd[1]: Started MySQL Server.

查看进程：

[root@guest ~]# ps -ef | grep -i mysql
mysql     13473      1  2 11:25 ?        00:00:00 /usr/sbin/mysqld
root      13524  11165  0 11:26 pts/1    00:00:00 grep --color=auto -i mysql

A2.7、查看 MySQL 服务是否自启动

[root@guest ~]# systemctl list-unit-files|grep mysqld.service
mysqld.service                                enabled

默认是 enabled。

  • 如不是 enabled 可以运行如下命令设置自启动

    systemctl enable mysqld.service

  • 如果希望不进行自启动，运行如下命令设置

    systemctl disable mysqld.service

A3、MySQL 登录

A3.1、首次登录

通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在 Enter password：录入初始化密码。

[root@guest ~]# mysql -hlocalhost -P3306 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

A3.2、修改密码

因为初始化密码默认是过期的，所以查看数据库会报错，修改密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'WelC0me168!';
Query OK, 0 rows affected (0.00 sec)
  • 5.7 版本之后（不含 5.7），mysql 加入了全新的密码安全机制。设置新密码太简单会报错。
  • 改为更复杂的密码规则之后，设置成功，可以正常使用数据库了。

A3.3、设置远程登录

A3.3.1、当前问题

在用 SQLyog 或 Navicat 中配置远程连接 Mysql 数据库时遇到如下报错信息，这是由于 Mysql 配置了不支持远程连接引起的。

mysql_navicat_disallowed

A3.3.2、确认网络

在远程机器上使用 ping ip 地址 保证网络畅通，在远程机器上使用 telnet 命令 保证端口号开放 访问。

[C:\~]$ telnet 192.168.95.88 3306
Connecting to 192.168.95.88:3306...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
EHost '192.168.95.1' is not allowed to connect to this MySQL serverConnection closing...Socket close.

Connection closed by foreign host.

Disconnected from remote host(192.168.95.88:3306) at 19:51:14.
  • 很明显网络是通的，只是不让远程连接。

A3.3.3、关闭防火墙

# CentOS6：
service iptables stop

# CentOS7：
systemctl start firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service
# 设置开机启用防火墙
systemctl enable firewalld.service
# 设置开机禁用防火墙
systemctl disable firewalld.service

A3.3.4、Linux 下修改配置

在 Linux 系统 MySQL 下测试：

mysql> use mysql;
Database changed
mysql> select Host,User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

可以看到 root 用户的当前主机配置信息为 localhost。

修改 Host 为通配符 %

Host 列指定了允许用户登录所使用的 IP，比如 user=root Host=192.168.1.1。这里的意思就是说 root 用户只能通过 192.168.1.1 的客户端去访问。

user=root Host=localhost，表示只能通过本机客户端去访问。而 % 是个通配符，如果 Host=192.168.1.%，那么就表示只要是 IP 地址前缀为 192.168.1. 的客户端都可以连接。如果 Host=%，表示所有 IP 都有连接权限。

注意：在生产环境下不能为了省事将 host 设置为 %，这样做会存在安全问题，具体的设置可以根据生产环境的 IP 进行设置。

update user set host = '%' where user ='root';

Host 设置了 % 后便可以允许远程访问。Host 修改完成后记得执行 flush privileges 使配置立即生效：

flush privileges;

A3.3.5、测试

如果是 MySQL5.7 版本，接下来就可以使用 SQLyog 或者 Navicat 成功连接至 MySQL 了。

如果是 MySQL8 版本，连接时还会出现如下问题：

错误号码2058
Plugin caching_sha2_password could not be loaded:xxxxxx

分析是 mysql 密码加密方法变了。

解决方法：Linux 下 mysql -u root -p 登录你的 mysql 数据库，然后执行这条 SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

然后在重新配置 SQLyog 的连接，则可连接成功了，OK。

A4、MySQL8 的密码强度评估

A4.1、MySQL 不同版本设置密码(可能出现)

MySQL 5.7 中：成功

mysql> alter user 'root' identified by 'abcd1234';
Query OK, 0 rows affected (0.00 sec)

MySQL 8.0 中：失败

mysql> alter user 'root' identified by 'abcd1234';  # HelloWorld_123
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

A4.2、MySQL 8 之前的安全策略

在 MySQL 8.0 之前，MySQL 使用的是 validate_password 插件检测、验证账号密码强度，保障账号的安全性。

安装/启用插件方式 1：在参数文件 my.cnf 中添加参数

[mysqld]
plugin-load-add=validate_password.so
# ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用)
validate-password=FORCE_PLUS_PERMANENT

说明 1：plugin library 中的 validate_password 文件名的后缀名根据平台不同有所差异。 对于 Unix 和 Unix-like 系统而言，它的文件后缀名是 .so，对于 Windows 系统而言，它的文件后缀名是 .dll。

说明 2：修改参数后必须重启 MySQL 服务才能生效。

说明 3：参数 FORCE_PLUS_PERMANENT 是为了防止插件在 MySQL 运行时的时候被卸载。当你卸载插件时就会报错。如下所示。

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME = 'validate_password';
+-------------------+----------------------+---------------+----------------------+
| PLUGIN_NAME       | PLUGIN_LIBRARY       | PLUGIN_STATUS | LOAD_OPTION          |
+-------------------+----------------------+---------------+----------------------+
| validate_password | validate_password.so | ACTIVE        | FORCE_PLUS_PERMANENT |
+-------------------+----------------------+---------------+----------------------+
1 row in set (0.00 sec)
mysql> UNINSTALL PLUGIN validate_password;
ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be unloaded
mysql>

安装/启用插件方式 2：运行时命令安装（推荐）

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.11 sec)

此方法也会注册到元数据，也就是 mysql.plugin 表中，所以不用担心 MySQL 重启后插件会失效。

A4.3、MySQL 8 的安全策略

A4.3.1、validate_password 说明

MySQL 8.0，引入了服务器组件（Components）这个特性，validate_password 插件已被服务器组件取代。8.0.31 版本的数据库中，默认自动安装 validate_password 组件。

未安装插件和组件，执行如下两个指令，执行效果：

mysql> show variables like 'validate_password%';
Empty set (0.04 sec)
mysql> SELECT * FROM mysql.component;
ERROR 1146 (42S02): Table 'mysql.component' doesn't exist

安装插件和组件后，执行如下两个指令，执行效果：

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|       1      |          1         | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

关于 validate_password 组件对应的系统变量说明：

选项 默认值 参数描述
validate_password_check_user_name ON 设置为ON的时候表示能将密码设置成当前用户名。
validate_password_dictionary_file 用于检查密码的字典文件的路径名，默认为空。
validate_password_length 8 密码的最小长度，也就是说密码长度必须大于或等于 8
validate_password_mixed_case_count 1 如果密码策略是中等或更强的，validate_password 要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。
validate_password_number_count 1 密码必须包含的数字个数
validate_password_policy MEDIUM 密码强度检验等级，可以使用数值0、1、2 或相应的符号值 LOW、MEDIUM、STRONG 来指定。0/LOW：只检查长度。1/MEDIUM：检查长度、数字、大小写、特殊字符。2/STRONG：检查长度、数字、大小写、特殊字符、字典文件。
validate_password_special_char_count 1 密码必须包含的特殊字符个数

提示：组件和插件的默认值可能有所不同。例如，MySQL 5.7 validate_password_check_user_name 的默认值为 OFF。另外，插件已废弃，不需要安装，默认 MySQL8.0 自动安装组件。

A4.3.2、修改安全策略

修改密码验证安全强度

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_policy=MEDIUM;
SET GLOBAL validate_password_policy=STRONG;
SET GLOBAL validate_password_policy=0;  # For LOW
SET GLOBAL validate_password_policy=1;  # For MEDIUM
SET GLOBAL validate_password_policy=2;  # For HIGH
# 注意，如果是插件的话,SQL为set global validate_password_policy=LOW

此外，还可以修改密码中字符的长度

set global validate_password_length=1;

A4.3.3、密码强度测试

如果你创建密码是遇到 Your password does not satisfy the current policy requirements，可以通过函数组件去检测密码是否满足条件： 0-100。当评估在 100 时就是说明使用上了最基本的规则：大写+小写+特殊字符+数字组成的 8 位以上密码。

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium');
+--------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('medium') |
+--------------------------------------+
|                                   25 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') |
+-------------------------------------------+
|                                       100 |
+-------------------------------------------+
1 row in set (0.00 sec)

注意：如果没有安装 validate_password 组件或插件的话，那么这个函数永远都返回 0。 关于密码复杂度对应的密码复杂度策略。如下表格所示：

Password Test Return Value
Length < 4 0
Length ≥ 4 and < validate_password.length 25
Satisfies policy 1 (LOW) 50
Satisfies policy 2 (MEDIUM) 75
Satisfies policy 3 (STRONG) 100

A4.3.4、卸载插件、组件

卸载插件

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected, 1 warning (0.01 sec)

卸载组件

mysql> UNINSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.02 sec)

A5、字符集的相关操作

A5.1、修改 MySQL 5.7 字符集

A5.1.1、修改步骤

在 MySQL 8.0 版本之前，默认字符集为 latin1，utf8 字符集指向的是 utf8mb3。网站开发人员在数据库设计的时候往往会将编码修改为 utf8 字符集。如果遗忘修改默认的编码，就会出现乱码的问题。

从 MySQL 8.0 开始，数据库的默认编码将改为 utf8mb4，从而避免上述乱码的问题。

操作 1：查看默认使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';

MySQL 8.0 中执行：

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.04 sec)

MySQL 5.7 中执行：MySQL 5.7 默认的客户端和服务器都用了 latin1，不支持中文，保存中文会报错。

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

在 MySQL 5.7 中添加中文数据时，报错：

mysql> CREATE DATABASE d_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE d_test;
Database changed
mysql> 
mysql> CREATE TABLE t_test(id int, name varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO t_test VALUES (1, "张三");
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1
mysql>

因为默认情况下，创建表使用的是 latin1。如下：

mysql> SHOW CREATE TABLE t_test\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

操作 2：修改字符集

vim /etc/my.cnf

在 MySQL5.7 或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

操作 3：重新启动 MySQL 服务

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

A5.1.2、已有库&表字符集的变更

MySQL5.7 版本中，以前创建的库，创建的表字符集还是 latin1。

mysql> SHOW CREATE TABLE t_test\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

修改已创建数据库的字符集：

mysql> ALTER DATABASE d_test character set 'utf8';
Query OK, 1 row affected (0.00 sec)

修改已创建数据表的字符集：

mysql> ALTER TABLE t_test convert to character set 'utf8';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在插入中文记录试试：

mysql> INSERT INTO t_test VALUES (1, "张三");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_test VALUES (1, "李四");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    1 | 李四   |
+------+--------+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE t_test\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意：原有的数据如果是用非 'utf8' 编码的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入。

A5.2、各级别的字符集

MySQL 有 4 个级别的字符集和比较规则，分别是：

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

执行如下 SQL 语句：

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)
  • character_set_server：服务器级别的字符集。
  • character_set_database：当前数据库的字符集。
  • character_set_client：服务器解码请求时使用的字符集。
  • character_set_connection：服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection。
  • character_set_results：服务器向客户端返回数据时使用的字符集。

A5.2.1、服务器级别

character_set_server：服务器级别的字符集。

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk  # 默认字符集
collation_server=gbk_chinese_ci  # 对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

A5.2.2、数据库级别

character_set_database：当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
 [[DEFAULT] CHARACTER SET 字符集名称]
 [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
 [[DEFAULT] CHARACTER SET 字符集名称]
 [[DEFAULT] COLLATE 比较规则名称];

A5.2.3、表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
 [[DEFAULT] CHARACTER SET 字符集名称]
 [COLLATE 比较规则名称]]

ALTER TABLE 表名
 [[DEFAULT] CHARACTER SET 字符集名称]
 [COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

A5.2.4、列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
 其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

提示：

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是 utf8，列中存储了一些汉字，现在把列的字符集转换为 ascii 的话就会出错，因为 ascii 字符集并不能表示汉字字符。

A5.2.5、小结

我们介绍的这 4 个级别字符集和比较规则的联系如下：

  • 如果 创建或修改列 时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则。
  • 如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则。
  • 如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规则。

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 t_test 中插入一条记录：

mysql> SHOW CREATE TABLE t_test\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> INSERT INTO t_test VALUES(3, "我们");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    1 | 李四   |
|    3 | 我们   |
+------+--------+
3 rows in set (0.00 sec)

首先表 t_test 使用的字符集是 gbk，一个字符 '我' 在 gbk 中的编码为 0xCED2，占用两个字节，两个字符的实际数据就占用 4 个字节。如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用 6 个字节。

A5.3、字符集与比较规则

A5.3.1、utf8 与 utf8mb4

utf8 字符集表示一个字符需要使用 1～4 个字节，但是我们常用的一些字符使用 1～3 个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计 MySQL 的设计者偷偷的定义了两个概念：

  • utf8mb3：阉割过的 utf8 字符集，只使用 1～3 个字节表示字符。
  • utf8mb4：正宗的 utf8 字符集，使用 1～4 个字节表示字符。

A5.3.2、比较规则

MySQL 版本一共支持 41 种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀 英文释义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

最后一列 Maxlen，它代表该种字符集表示一个字符最多需要几个字节。

常用操作 1：

# 查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';

# 查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

常用操作 2：

# 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';

# 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';

# 查看具体数据库的字符集
SHOW CREATE DATABASE d_test;

# 修改具体数据库的字符集
ALTER DATABASE d_test DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

常用操作 3：

# 查看表的字符集
show create table employees;

# 查看表的比较规则
show table status from atguigudb like 'employees';

# 修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

A5.4、请求到响应过程中字符集的变化

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

实验数据：

mysql> CREATE DATABASE d_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE d_test;
Database changed
mysql>
mysql> CREATE TABLE t_test (id int, name varchar(10)) CHARACTER SET gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_test VALUES (1, "我");
Query OK, 1 row affected (0.03 sec)

为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

mysql> SET character_set_connection=gbk;
Query OK, 0 rows affected (0.00 sec)

现在假设我们客户端发送的请求是下边这个字符串：

SELECT * FROM t_test WHERE name = '我';

为了方便大家理解这个过程，我们只分析字符 我 在这个过程中字符集的转换。

现在看一下在请求从发送到结果返回过程中字符集的变化：

  1. 客户端发送请求所使用的字符集

    一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：

    • 类 Unix 系统使用的是 utf8
    • Windows 使用的是 gbk

    当客户端使用的是 utf8 字符集，字符 我 在发送给服务器的请求中的字节形式就是：0xE68891。

    提示：

    如果你使用的是可视化工具，比如 navicat 之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用命令行窗口）。

  2. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client，然后把这串字节转换为 character_set_connection 字符集编码的字符。

    由于我的计算机上 character_set_client 的值是 utf8，首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串就是 我，然后按照 character_set_connection 代表的字符集，也就是 gbk 进行编码，得到的结果就是字节串 0xCED2。

  3. 因为表 t_test 的列 name 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。

    提示：

    如果某个列使用的字符集和 character_set_connection 代表的字符集不一致的话，还需要进行一次字符集转换。

  4. 上一步骤找到的记录中的 name 列其实是一个字节串 0xCED2， name 列是采用 gbk 进行编码的，所以首先会将这个字节串使用 gbk 进行解码，得到字符串 我，然后再把这个字符串使用 character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串：0xE68891，然后发送给客户端。

  5. 由于客户端是用的字符集是 utf8，所以可以顺利的将 0xE68891 解释成字符 我，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

总结图示如下：

mysql_code

A6、SQL 大小写规范

A6.1、Windows 和 Linux 平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。

不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。 windows 系统默认大小写不敏感 ，但是 linux 系统是大小写敏感的。

通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%'

lower_case_table_names 参数值的设置：

  • 默认为 0，大小写敏感。
  • 设置 1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于 sql 语句都是转换为小写对表和数据库进行查找。
  • 设置 2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

MySQL 在 Linux 下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL 在 Windows 的环境下全部不区分大小写。

A6.2、Linux 下大小写规则设置

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入 lower_case_table_names=1，然后重启服务器。

  • 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。

  • 此参数适用于 MySQL5.7。在 MySQL 8 下禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。如果非要将 MySQL8 设置为大小写不敏感，具体步骤为：

    1、停止MySQL服务
2、删除数据目录，即删除 /var/lib/mysql 目录
3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1
4、启动MySQL服务

A6.3、SQL 编写建议

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

  1. 关键字和函数名称全部大写；
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。

虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

A7、sql_mode 的合理设置

A7.1、宽松模式 vs 严格模式

宽松模式：

如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

举例：我在创建一个表时，该表中有一个字段为 name，给 name 设置的字段类型时 char(10)，如果我在插入数据的时候，其中 name 这个字段对应的有一条数据的 长度超过了10，例如 1234567890abc，超过了设定的字段长度 10，那么不会报错，并且取前 10 个字符存上，也就是说你这个数据被存为了 1234567890，而 abc 就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且 mysql 自行处理并接受了，这就是宽松模式的效果。

应用场景：通过设置 sql mode 为宽松模式，来保证大多数 sql 符合标准的 sql 语法，这样应用在不同数据库之间进行 迁移 时，则不需要对业务 sql 进行较大的修改。

严格模式：

出现上面宽松模式的错误，应该报错才对，所以 MySQL5.7 版本就将 sql_mode 默认值改为了严格模式。所以在 生产等环境 中，我们必须采用的是严格模式，进而 开发、测试环境 的数据库也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用的 MySQL5.6，也应该自行将其改为严格模式。

开发经验：MySQL 等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

改为严格模式后可能会存在的问题：

若设置模式中包含了 NO_ZERO_DATE，那么 MySQL 数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段 TIMESTAMP 列（如果未声明为 NULL 或显示 DEFAULT 子句）将自动分配 DEFAULT '0000-00-00 00:00:00'（零时间戳），这显然是不满足 sql_mode 中的 NO_ZERO_DATE 而报错。

A7.2、模式查看和设置

查看当前的 sql_mode

select @@session.sql_mode;
select @@global.sql_mode;
# 或者
show variables like 'sql_mode';

临时设置方式：设置当前窗口中设置 sql_mode

SET GLOBAL sql_mode = 'modes...'; # 全局
SET SESSION sql_mode = 'modes...'; # 当前会话

举例：

# 改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。
set SESSION sql_mode='STRICT_TRANS_TABLES';
# 改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。
set GLOBAL sql_mode='STRICT_TRANS_TABLES';

永久设置方式：在 /etc/my.cnf 中配置 sql_mode

在 my.cnf 文件(windows 系统是 my.ini文件)，新增：

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然后 重启MySQL。

当然生产环境上是禁止重启 MySQL 服务的，所以采用 临时设置方式 + 永久设置方式 来解决线上的问题，那么即便是有一天真的重启了 MySQL 服务，也会永久生效了。

更多阅读
标签云