mysql8.0环境的搭建（Linux）-JZTXT

1.安装前说明

1.1 Linux系统及工具的准备。

安装并启动好两台虚拟机：CentOS 7
安装有远程访问centOS系统的工具
CentOS6和CentOS7在MySQL的使用中的区别
- 默认防火墙：CentOS6是iptables；CentOS7是firewalld；
- 启动服务的命令：CentOS6是 service mysqld start；CentOS7是 systemctl start mysqld；

1.2 查看是否安装过MySQL

检查一下rpm package；

### 方法一：
rpm -qa | grep -i mysql       # -i 忽略大小写
### 方法二：
whereis mysql

检查一下 mysql service；

systemctl status mysqld.service

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

1.3 MySQL卸载

关闭mysql服务

systemctl stop mysqld.service

查看当前mysql的安装状况

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

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

yum remove mysql-XXX mysql-XXX mysql-XXX ...

注意：此处一定要卸载干净，反复执行 rpm -qa | grep -i mysql确认是否有卸载残留。

删除mysql相关文件

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

### 删除上述命令查找出来的文件
rm -rf XXX

### 删除my.cnf
rm -rf /etc/my.cnf

2. MySQL的安装（Linux版）

首先需要将安装程序包上传到环境。

[root@mysql80 ~]# ls
anaconda-ks.cfg  mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar

2.1 检查环境依赖

2.1.1 检查/tmp临时目录权限（必须）

由于MySQL安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行

[root@mysql80 /]# chown -R 777 /tmp

2.1.2 安装前检查依赖

检查是不是存在libaio包和net-tools包，没有的话需要进行安装。

[root@mysql80 /]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64
[root@mysql80 /]# rpm -qa|grep net-tools
net-tools-2.0-0.25.20131004git.el7.x86_64

2.2 MySQL的安装过程

2.2.1 解压安装程序包，将安装程序拷贝到/opt目录下

解压安装程序包

[root@mysql80 ~]# tar -xvf mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar 
[root@mysql80 ~]# ls
anaconda-ks.cfg
mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.32-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
mysql-community-common-8.0.32-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.32-1.el7.x86_64.rpm
mysql-community-devel-8.0.32-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-server-8.0.32-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.32-1.el7.x86_64.rpm
mysql-community-test-8.0.32-1.el7.x86_64.rpm

注意：解压之后会解压出来大量的rpm包，但是这些rpm包不都是必须的。

一般我们能够用到的只有其中的几个，所以我们在安装的时候也先只安装这几个rpm包：

mysql-community-client-8.0.32-1.el7.x86_64.rpm

mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm

mysql-community-common-8.0.32-1.el7.x86_64.rpm

mysql-community-libs-8.0.32-1.el7.x86_64.rpm

mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm

mysql-community-server-8.0.32-1.el7.x86_64.rpm

将需要安装的程序拷贝到/opt目录下

[root@mysql80 ~]# cd /opt/
[root@mysql80 opt]# ls
[root@mysql80 opt]# mv /root/mysql-community-client-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# mv /root/mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# mv /root/mysql-community-common-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# mv /root/mysql-community-libs-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# mv /root/mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# mv /root/mysql-community-server-8.0.32-1.el7.x86_64.rpm .
[root@mysql80 opt]# ls
mysql-community-client-8.0.32-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
mysql-community-common-8.0.32-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-8.0.32-1.el7.x86_64.rpm
mysql-community-server-8.0.32-1.el7.x86_64.rpm

在目录下运行安装程序包（必须按照一定的先后顺序！）

rpm -ivh mysql-community-common-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.32-1.el7.x86_64.rpm

# 在安装5.7版本的时候，需要在执行命令后加上--force --nodeps
# 例如：
rpm -ivh mysql-community-common-5.7.41-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-client-5.7.41-1.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-community-server-5.7.41-1.el7.x86_64.rpm --force --nodeps

在安装libs包的时候，会提示如下报错：

[root@mysql80 opt]# rpm -ivh mysql-community-libs-8.0.32-1.el7.x86_64.rpm 
warning: mysql-community-libs-8.0.32-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
error: Failed dependencies:
	mariadb-libs is obsoleted by mysql-community-libs-8.0.32-1.el7.x86_64

是因为系统里已经默认安装了mariadb-libs包，这个包与mysql-libs包可以说师出同门，会产生安装冲突，所以这里我们需要先卸载mariadb-libs包。

[root@mysql80 opt]# yum remove -y mysql-libs

2.2.2 检查是否安装成功

查看安装的mysql版本

mysql --version
# 或
mysqladmin --version

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

[root@mysql80 opt]# rpm -qa|grep -i mysql
mysql-community-client-8.0.32-1.el7.x86_64
mysql-community-common-8.0.32-1.el7.x86_64
mysql-community-libs-8.0.32-1.el7.x86_64
mysql-community-icu-data-files-8.0.32-1.el7.x86_64
mysql-community-client-plugins-8.0.32-1.el7.x86_64
mysql-community-server-8.0.32-1.el7.x86_64

2.2.3 服务初始化

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

mysqld --initialize --user=mysql

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

在日志中查看初始密码：

[root@mysql80 opt]# cat /var/log/mysqld.log 
2023-04-21T22:25:57.727530Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.32) initializing of server in progress as process 80488
2023-04-21T22:25:57.754501Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-21T22:25:58.741961Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-21T22:26:00.167915Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Or3),omtofmL

从日志文件的最后一行中，我们可以获得mysql的初始密码是：Or3),omtofmL

2.2.4 启动MySQL，查看状态

启动：systemctl start mysqld.service

关闭：systemctl stop mysqld

重启：systemctl restart mysqld

查看状态：systemctl status mysqld

设置/取消开机自启动：systemctl enable/disable mysqld

这里mysqld和mysqld.service都可以，是一个可执行文件，表示mysql的服务程序。

查看MySQL服务进程

[root@mysql80 ~]# ps -ef|grep -i mysql
mysql     81061      1  1 08:05 ?        00:00:02 /usr/sbin/mysqld
root      81125  80994  0 08:07 pts/0    00:00:00 grep --color=auto -i mysql

查看自启动是否设置成功

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

3. mysql登录

首次登录时，使用mysql -hlocalhost -P3306 -uroot -p进行登录，回车之后输入初始化密码。

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

Copyright (c) 2000, 2023, 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>

3.1 修改密码

因为登录系统之后，初始密码默认就是过期状态，此时无法进行任何操作，必须进行密码修改。
修改密码语句：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password ';

注意：5.7之后的版本，引入了全新的安全密码机制，设置的新密码过于简单可能会进行报错。但是在设置本人在设置时，没有报错。所以这条可以忽略，可以作为一个报错时解决的思路。

3.2 设置远程登录

首先要确保网络环境的通常
- ping IP，确保网络的通常；
- telnet IP port，确保端口号是开放的；
关闭防火墙或者开放指定端口号；
CentOS 7开放端口号的方式

### 查看所有开放的端口号
firewall-cmd --list-all

### 设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent

### 重启防火墙
firewall-cmd --reload

MySQL内的设置调整

修改用户连接地址

## 进入mysql库
use mysql;

## 查看user表中host字段和user字段
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，即只允许本机连接登录，想要远程登录，就要在这里进行修改；

## 设置远程登录，对某个网段开放，不做限制的话可以使用'%'
mysql> update user set host = '10.0.0.%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| 10.0.0.%  | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

## 刷新操作，使更改生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4. 字符集相关操作

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

查看默认使用的字符集

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

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.00 sec)

5.7版本的字符集显示

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)

可以看到5.7版本的character_set_database 和 character_set_server 的字符类型则是 latin1。我们安装完成后，首先需要做的就是将它们的值改成 utf8 类型的。

4.1 5.7版本修改字符集

## 修改配置文件 /etc/mysql.cnf
## 在【mysqld】部门最后加上utf8的字符集配置
[mysqld]
...
character_set_server=utf8
...

## 修改完成后保存退出，重启服务。
systemctl restart mysqld

修改完成后，进入数据库查看现在的字符集情况

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

注意：虽然默认字符集已经改过来了，但是之前创建的数据库和数据表，其下的内容依然是latin1字符类型的，即使是在其中新建的数据表和插入的数据，其格式也依然是原来的字符类型。

对已有库和表的字符集格式的变更

## 这里就需要使用sql语句进行变更
## 修改已创建数据库的字符集格式
alter database dbtest1 character set 'utf8';

## 修改已创建数据表的字符集格式
alter table t_emp convert to character set 'utf8';

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

4.2 各个级别的字符集

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

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

### 查看字符集
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)

character_set_server：服务器级别的字符集
character_set_database：当前数据库的字符集
character_set_client：服务器解码请求时使用的字符集
character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results：服务器向客户端返回数据时使用的字符集

4.2.1 服务器级别

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

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

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

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

4.2.2 数据库级别

character_set_database：当前数据库的字符集

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

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

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

其中的 DEFAULT 可以省略，并不影响语句的语义。比如:

mysql> CREATE DATABASE charset_demo_db
	-> CHARACTER SET gb2312
	-> COLLATE gb2312_chinese_ci;
Query OK,1 row affected (0.81 sec)

数据库的创建语句中也可以不指定字符集和比较规则，比如这样:

CREATE DATABASE 数据库名;

这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

4.2.3 表级别

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

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

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

比方说我们在刚刚创建的 charset_demo_db 数据库中创建一个名为 t 的表，并指定这个表的字符集和比较规则:

mysql> CREATE TABLE t(
	-> col VARCHAR(10)
	-> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query oK,8 rows affected (0.83 sec)

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

4.2.4 列级别

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

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

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

比如我们修改一下表 t 中列 col 的字符集和比较规则可以这么写:

mysql> ALTER TABLE t MODIFY col VARCHAR(18)CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query oK,e rows affected (0.84 sec)
Records: 0 Duplicates: 0 warnings: 0

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

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

4.3 字符集和比较规则

4.3.1 utf8与utf8mb4

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

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

在MysQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情，那请使用utf8mb4。

此外，通过如下指令可以查看MySQL支持的字符集:

SHOW CHARSET;
#或者
SHOW CHARACTER SET;

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.06 sec)

4.3.2 比较规则

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

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

最后一列 Maxlen，它代表该种字符集表示一个字符最多需要几个字节。这里把常见的字符集和对应的 Maxlen显式如下:

常见操作方式：

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

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

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

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

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

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

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

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

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

utf8_unicode_ci 和 utf8_general_ci 对中、英文来说没有实质的差别。utf8_general_ci 校对速度快,但准确度稍差。utf8_unicode_ci 准确度高，·但校对速度稍慢。一般情况，用 utf8_general_ci就够了，但如果你的应用有德语、法语或者俄语，请一定使用 utf8_unicode_ci。

修改了数据库的默认字符集和比较犯则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需要,那么需单独修改。

4.4 请求到响应过程中字符集的变化

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量，我们先把它们写出来看一下：

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

mysql> set character_set_connection = gbk ;
Query OK,a rows affected (0.80 sec)

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

SELECT * FROM t WHERE S ='我';

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

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

客户端发送请求所使用的字符集。一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，类Unix系统使用的是utf8；windows使用的是gbk。当客户端使用的是utf8字符集，字符 '我' 在发送给服务器的请求中的字节形式就是: exE68891

提示：如果使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集。

服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是
character_set_client ，然后把这串字节转换为 character_set_connection 字符集编码的字符。由于我的计算机上 character_set_client 的值是utf8，首先会按照utf8字符集对字节串 0xE68891 进行解码，得到的字符串就是'我’，然后按照 character_set_connection 代表的字符集，也就是gbk进行编码，得到的结果就是字节串是：0xCED2。
因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。

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

上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2 ，col列是采用 gbk 进行编码的，所以首先会将这个字节串使用gbk进行解码，得到字符串'我’，然后再把这个字符串使用 character_set_results 代表的字符集，也就是utf8进行编码，得到了新的字节串: 0xE68891，然后发送给客户端。
由于客户端是用的字符集是utf8，所以可以顺利的将 0xE68891 解释成字符 我，从而显示到我们的显示器
上，所以我们人类也读懂了返回的结果。

从这个分析中我们可以得出这么几点需要注意的地方:

服务器认为客户端发送过来的请求是用 character_set_client 编码的。假设你的客户端采用的字符集和 character_set_client 不一样的话，这就会出现识别不准确的情况。比如我的客户端使用的是utf8 字符集，如果把系统变量 character_set_client 的值设置为 ascii 的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。

服务器将把得到的结果集使用 character_set_results 编码后发送给客户端。假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是 utf8 字符集，如果把系统变量character_set_results 的值设置为 ascii 的话，可能会产生乱码。

character_set_connection 只是服务器在将请求的字节串从 character_set_client 转换为
character_set_connection 时使用，一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用 character_set_connection 代表的字符集进行编码。

所以，开发中通常把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句:

SET NAMES 字符集名;

这一条语句产生的效果和我们执行这3条的效果是一样的:

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

比方说我的客户端使用的是utf8字符集，所以需要把这几个系统变量的值都设置为utf8:

mysql>SET NAMES utf8;

另外，如果你想在启动客户端的时候就把 character_set_client、 character_set_connection ,character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set 的启动选项，比如在配置文件里动以这么写:

[client]
default-character-set=utf8

它起到的效果和执行一遍 SET NAMES utf8 是一样一样的，都会将那三个系统变量的值设置成utf8。

5. SQL大小写规范

5.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%'

windows 系统环境下

mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set, 1 warning (0.02 sec)

Linux 系统环境下

mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set, 1 warning (0.02 sec)

lower_case_table_names参数值的设置:
- 默认为0，大小写敏感。
- 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
- 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。
两个平台上SQL大小写的区别具体来说:

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

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

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

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

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

5.2 Linux下大小写规则设置

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服务
```

注意：在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置。

5.3 SQL编写建议

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

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

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

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

6. sql_mode的合理设置

6.1介绍

sql_mode 会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置 sql_mode ，可以完成不同严格程度的数据校验，有效地保障数据准确性。

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。

MySQL5.6 和 MySQL5.7默认的 sql_mode 模式参数是不一样的：

5.6的mode默认值为空（即: NO_ENGINE_SUBSTITUTION ），其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。
5.7的mode是 STRICT_TRANS_TABLES，也就是严格模式。用于进行数据的严格校验，错误数据不能插入，报error(错误)，并且事务回滚。

6.2 宽松模式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-0000:00:00'(零时间戳)，这显然是不满足 sql_mode 中的 NO_ZERO_DATE 而报错。

6.3 模式查看和设置

查看当前的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服务，也会永久生效了。

6.4 sql_mode常用值

下面列出MySQL中最重要的3种模式:

sql_mode值	解释
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。
NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为零。
NO_ZERO_DATE	设置该值,mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。
PIPES_AS_CONCAT	将"\|\|"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数concat相类似
ANSl_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。

centos7 centos mysql8 linux

版本mysql8系统linux

windows环境mysql8 mysql

mysql8 linux mysql

mysql环境docker mysql8

mysql8 redis7 linux mysql

服务器指南mysql8 linux

二进制mysql8 linux mysql