admin管理员组

文章数量:1651574

Linux安装和基本一些使用

基本环境搭建

Mysql 4大版本说明

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

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

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

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

CentOS7.6 中安装 Mysql8

Linux 中安装Mysql的几种方式

rpm命令

使用rpm命令安装扩展名为".rpm"的软件包。

.rmp包的格式如下

yum命令

yum命令安装的话, 需要联网, 一般来说国内都需要配置yum源, 不然容易下载失败, 关于yum配置请移步到Linux笔记中

源码编译

针对 tar.gz 这样的压缩格式，要用tar命令来解压；如果是其它压缩格式，就使用其它命令。

解压之后需要执行一些命令进行一个编译

Mysql安装

Mysql官方提供了多种的安装方式 下述是各种方式之间的对比

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

我们采用rpm的方式

MySQL8 下载 和 上传服务器

访问mysql官网, 点击download

将MySQL8.0.25 rpm文件上传到/opt/mysql, 如果没有该目录就新建

MySQL的安装

检查/tmp临时目录(必不可少)

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

chmod -R 777 /tmp
安装前检查依赖
rpm -qa | grep libaio

如果存在libaio包如下：

rpm -qa | grep net-tools

如果存在net-tools包如下：

如果不存在需要到centos安装盘里进行rpm安装。安装linux如果带图形化界面，这些都是安装好

的。

使用rpm(rpm 和 yum选一个即可)
新建目录mysql8目录
cd /opt/mysql/mysql8
mkdir /opt/mysql/mysql8
上传安装包

将mysql-8.0.34-1.el8.x86_64.rpm-bundle.tar文件上传到mysql8目录

如下图, 我已经将安装包上传了

解压安装包

在安装包所在的位置执行解压, 其中

tar -xvf mysql-8.0.34-1.el8.x86_64.rpm-bundle.tar

执行安装

其中被红框圈起来的, 才我们需要的组件

按顺序执行下述命令!!! 等一条执行往才执行下一个条!!!

rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
-i, --install 安装软件包
-v, --verbose 提供更多的详细信息输出
-h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm


rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm


rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm

安装下述依赖后再次执行命令安装

  • 作用: yum remove mysql-libs 解决，清除之前安装过的依赖即可
yum remove mysql-libs


rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm


rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

使用yum命令安装

进入到 /opt/mysql/mysql8目录, 并安装MySQL数据库

  • yum install 表示使用yum包管理器进行安装
  • mysql-community 表示MySQL社区版,是开源免费的MySQL版本
  • {server,client,common,libs} 表示要安装的组件包,其中:
    • server: MySQL数据库服务器
    • client: MySQL客户端和工具程序
    • common: MySQL数据库的公共文件
    • libs: MySQL库
  • * 表示安装这些组件包的所有版本
  • -y 表示自动确认安装,不需要用户输入yes
yum install mysql-community-{server,client,common,libs}-* -y

安装完成

开启数据库服务

# 启动mysql服务
systemctl start mysqld
# 设置MySQL为⾃动启动
systemctl enable mysqld
# 查看MySQL的状态
systemctl status mysqld

# 重新启动MySQL
systemctl status mysqld

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

查看进程
ps -ef | grep -i mysql

查看MySQL是否开启自启动
systemctl is-enabled mysqld

或者使用

查所有自启动程序, 过滤MySQL的

systemctl list-unit-files | grep mysqld

可以观察到, mysqld.service已经设置成了自启动

如果不希望自启动的话, 那么就运行如下命令

systemctl disable mysqld
查看mysql版本
mysql --version
#或
mysqladmin --version

查看是否安装成功

使用下述两条命令进行查看, 任意一条都行(即rpm安装的, 也可以使用yum查看)

rpm -qa | grep -i mysql


yum list installed | grep mysql

修改Mysql密码

服务的初始化

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

行下面的命令初始化：

mysqld --initialize --user=mysql

说明：

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

使用临时密码登录
# 获取root初始密码, 初始密码是MySQL自动生成的, 存在在mysqld.log文件中
# root@localhost: 后面就是初始化的密码
grep 'temporary password' /var/log/mysqld.log

# 使⽤初始密码登录root账号, 然后输入密码登录
mysql -hlocalhost -P3306 -uroot -p

获取密码

每次安装生成的密码都不一致, 以自己生成的为主

登录

修改密码
为什么需要修改密码?
  • 因为初始化密码默认是过期的，所以查看数据库会报错, 如下图, 在执行该语句之前，必须使用ALTER USER语句重置密码。

MySQL密码安全机制的变化

尝试修改密码

# 启动 new_password替换成你想要设置的密码
alter user 'root'@'localhost' identified by 'new_password';

5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错

改为更复杂的密码规则之后，设置成功，可以正常使用数据库了

设置一个简单的密码

alter user 'root'@'localhost' identified by '12345678';

如下图, 提示 你的密码不符合当前的安全策略要求, 也就说太简单了, 没有通过MySQL内置的密码等价校验

修改密码安全策略 和 修改密码

如果我就想要修改密码安全等级策略呢? 非要将密码设置成简单的密码呢?

可以的, 修改密码安全策略即可(生产中千万不要设置简单密码)

修改密码, 以及开启远程访问

MySQL8.0默认密码策略是：至少8位，其中包含，至少有一位大写字母，至少有一位小写字母，至少有一位数字，至少有一位特殊字符，通俗说，就是【大小写字母】、【数字】和【特殊符号】必须同时存在，例如：Root123！，就是满足默认策略的密码

密码策略
set global validate_password.policy=MEDIUM;

密码至少要包含的小写字母个数和大写字母个数
set global validate_password.mixed_case_count=1;

密码至少要包含的数字个数。
set global validate_password.number_count=1;

密码至少要包含的特殊字符数
set global validate_password.special_char_count=1;

密码最短的长度
set global validate_password.length=8;

修改root用户的的新的密码, 不修改不然执行其它操作

而且第一次密码必须符合规则才行, 否则不给修改, 不给修改, 那么没法就没法修改密码策略

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

查看 mysql 初始的密码策略

show variables like 'validate_password%';

修改密码策略, MySQL7 和 MySQL8 略有不同
策略解析, 生成环境中不能使用0级别的密码!!!

  • 0 or LOW 只验证长度
  • 1 or MEDIUM 验证长度、数字、大小写、特殊字符
  • 2 or STRONG 验证长度、数字、大小写、特殊字符、字典文件
set global validate_password.policy=LOW;
# 修改密码最短长度 为 1
set global validate_password.length=1;
# 最少包含 0 个 大/小写字母
set global validate_password.mixed_case_count=0;
# 最少包含0 个特殊字符
set global validate_password.special_char_count=0;

修改后再查看当前的密码策略

show variables like 'validate_password%';

再次修改密码

alter user 'root'@'localhost' identified by '12345678';

此时发现修改成功了

远程连接

当前问题

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

程连接引起的。

排查步骤(多思考)
确认网络

  1. 在远程机器上使用ping ip地址 保证网络畅通

    • 注意是Linux中ping本机的地址

    • 发现Linux ping Window是通过的

    • Window ping Linux也是可以的

    • Window 和 Linux之间可以互相ping通, 说明了主机之间的通信是没有问题的

  2. 在远程机器上使用telnet命令 保证端口号开放 访问

    • telnet 命令的格式如下

      • telnet ip地址 端口号

    • 在window使用telnet往Linux的端口尝试连接

    • telnet 192.168.132.17 3306

    • 发现执行结果如下, 说明了问题出现在, 端口

拓展： telnet命令开启 :

  1. 打开控制面板
  2. 点击"程序与功能"
  3. 点击 “启动或关闭Windows功能”
  4. 将"Telnet客户端"勾选上
  5. 点击确定

关闭防火墙 或 开放端口
# 不允许防火墙开机自启动
systemctl disable firewalld
# 关闭防火墙
systemctl stop firewalld
# 查看防火墙
systemctl status firewalld

再次在windos段执行telnet

telnet 192.168.132.17 3306

发现还是不行

修改Mysql的登录IP的限制

查看用户登录ip的限制

use mysql;
select Host,User from user;

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

Host表示该用户允许登录IP, 如果用户不是在这IP内进行登录的, 那么就拒绝连接

原来如此, 也就是之前登录不上就是因为root用户只需本机登录, 外界连接的时, MySQL就会进行拒绝连接

修改Host为通配符%

%是个 通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果 Host=%，表示所有IP都有连接权限。

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

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

Host修改完成后记得执行flush privileges使配置立即生效

flush privileges;

再次查看

select Host,User from user;

发现已经修改了

使用Navicat 远程访问MySQL8

如果还是报错请看这里

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

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

配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。

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

修改密码加密方式

alter user 'root'@'%' identified with mysql_native_password BY '12345678';

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

设置MySQL的编码和时区

在/etc/myf的配置⽂件中的[mysqld]下⾯的配置项

# 配置字符编码为utf8
character_set_server=utf8
# 配置时区
default-time_zone='+8:00'

重启MySQL 服务

systemctl restart mysqld

Mysql8 密码强度评估规则

Mysql中不同版本设置密码

  • MySQL5.7中：成功

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

  • Mysql8.0: 失败

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

MySQL8之前的安全策略

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

注意了注意了, 下述操作都是在MySQL5.7版本操作的!!!

安装插件方式一: 在参数文件myf中添加参数

安装/启动参见的方式1: 在参数文件myf中添加参数

[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运行时的时候被卸载。当你卸载插件时就会报错。

默认的选项时on

实践

开启插件

重启MySQL

systemctl restart mysqld

查询插件, 如下图, 发现此时的load_option变成了强制使用插件会提示该插件永久强制使用,无法卸载

SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME = 'validate_password';

卸载插件, 强制使用模式下的, 如果卸载该插件的话,

uninstall plugin validate_password

安装插件方式二: 运行时, 使用命令安装(推荐)

注意了!注意了!请将安装插件方式一中所做的操作进行一个还原, 避免影响后续的操作, 这里就不给执行的命令了

  1. 删除myf中添加的插件
  2. 重新启动MySQL
  3. 此时查看插件发现是空的

登录MySQL中, 执行下述命令进行一个添加

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

install plugin validate_password soname 'validate_password.so';

执行查看插件命令, 发现启动了

SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME = 'validate_password';

MySQL之后的安全策略

注意了!注意了!下述使用都是MySQL8的, 请不要在MySQL5.7中进行一个测试

validate_password说明

MySQL 8.0，引入了服务器组件（Components）这个特性，validate_password插件已用服务器组件重新实

现。8.0.25版本的数据库中，默认自动安装validate_password组件。

执行如下两个指令

select * from mysqlponent;


show variables like 'validate_password%';

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

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

提示: 组件和插件的默认值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的默认值为OFF。

修改安全策略

修改密码验证安全强度

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
密码强度测试函数VALIDATE_PASSWORD_STRENGTH()使用

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

之前密码太简单就是因为没有通过这里的密码强度验证

select validate_password_strength('12345678');


select validate_password_strength('yFD/RPNSA5#^');

注意：如果没有安装validate_password组件或插件的话，那么这个函数永远都返回0。

关于密码复杂度评分 对应的 密码复杂度策略

Password TestReturn Value
Length < 40
Length ≥ 4 and < validate_password.length25
Satisfies policy 1 (LOW)50
Satisfies policy 2 (MEDIUM)75
Satisfies policy 3 (STRONG)100

字符集相关设置

修改MySQL5.7的字符集

MySQL5.7 和 MySQL8.0版本字符集之间的差异

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。

网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

查看MySQL5.7的默认字符集

注意了, 请在MySQL5.7下执行!!!如果在MySQL8中执行, 那就别怪我没有提醒你

show variables like '%character%';

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

查看MySQL8的默认字符集

MySQL8中执行

show variables like '%character%';

MySQL5.7无法添加中文字符

问题描述

添加中文的时候报错 Incorrect string value: xxxx… for column ‘xxx’ at row xxx

问题复现

测试MySQL5.7中使用latin1中保存中文

# 创建一个名为dbtest1的数据库
create database dbtest1;
# 使用dbtest1数据库
use dbtest1;
# 创建一个名为t_emp的表
create table t_emp(id int, name varchar(20));
# 插入两条数据, 一条中文, 一条英文
insert into t_emp(id, name) values(1,'whiteBrocade');
insert into t_emp(id, name) values(2, '小牛马');

发现, 在执行添加含中文的语句时就报错了

产生原因

为什么?

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

查看建表语句, 发现默认使用字符集就是latin1

show create table t_emp;

解决方案

修改字符集

有两种修改方式

  1. 临时修改: 单次有效, 当MySQL重启时候, 又会变成latin1
  2. 永久修改: 永久有效, 即是MySQL重新启动也不会失效(推荐)
临时修改
永久修改

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

character_set_server=utf8

重新启动MySQL, 不启动是不生效的!!

systemctl restart mysqld

查看修改后的字符集

show variables like '%character%';

补充说明: 但是原库、原表的字符集不会随这这里改变发生变化，参数修改只对日后新建的数据库生效

查看之前创建的表, 发现没有变化

show create table t_emp;

新建一个表, 查看建表语句

# 创建一个名为dbtest2的数据库, 注意了, 一定要新建库, 如果还是在原来的dbtest1的话, 那么建表如果没有指定字符集, 那么就会使用dbtest1的字符集, 然而dbtest1的字符集是latin1
create database dbtest2;
# 使用dbtest2
use dbtest2;
# 创建一个名为t_emp的表
create table t_emp2(id int, name varchar(20));
# 查看建表语句
show create table t_emp2;

插入两条数据, 进行查询

# 插入两条数据, 一条中文, 一条英文
insert into t_emp2(id, name) values(1,'whiteBrocade');
insert into t_emp2(id, name) values(2, '小牛马');
# 执行查询
select *from t_emp2;

各级别的字符集解析

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

  • 服务器级别: character_set_server
  • 数据库级别: character_set_database
  • 表级别:
  • 列级别
show variables like 'character%';

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

character_set_server 服务器级别的字符集

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

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

重启

systemctl restart mysqld

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

查看字符集

show variables like '%character%';

发现虚表的中的字符集是gbk, 也就说局部会覆盖全局的设置, 在这里面, character-set-server=utf8是全局, character_set_server=gbk是局部

character_set_database 数据库级别

创建和修改数据库的时候可以指定该数据库的字符集和比较规则

创建的时候进行指定
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

例如现在创建一个数据库dbtest3并指定字符集

create database dbtest3 default character set utf8
修改指定数据库的字符集
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

例如修改dbtest3的字符集

alter database dbtest3 default character set utf8

表级别

创建和修改表的时候指定表的字符集和比较规则

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

为该表的字符集和比较规则。

创建表指定
CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]
修改指定表的字符集
ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

创建一个表, 默认的字符集为utf8

create database dbtest4 default character set utf8;
use dbtest4;
show variables like '%character%';

不指定字符集创建一个表, 查看该表的建表语句, 观察字符集, 发现确实和charactrer_set_database一致

create table t_emp(id int, name varchar(20));
show create table t_emp;

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列

定义的时候可以指定该列的字符集和比较规则

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

和比较规则作为该列的字符集和比较规则。

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生

错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的

话就会出错，因为ascii字符集并不能表示汉字字符。

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

use dbtest4;
create table t_emp2(
    id int,
    name varchar(20) character set utf8);
修改指定表下的指定列的字符集
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

修改列的字符集

alter table t_emp2 modify name varchar(20) character set gbk

四个字符集与比较规则

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

  • 如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则

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

字符姐和比较规则

utf8 与 utf8mb4的关系

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

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

  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

也就说我们平常所谓的utf8就是指utf8mb3

比较规则

MySQL中一个共支持41中字符集, 其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀英文释义描述
_aiaccent insensitive不区分重音
_asaccent sensitive区分重音
_cicase insensitive不区分大小写
_cscase sensitive区分大小写
_binbinary以二进制方式比较

最后一列 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';

# 创建库
create database dbtest5;
# 使用库
use dbtest5;
# 创建表
create table employees(id int(11), name varchar(20));
#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from dbtest5 like 'employees';
#修改表的字符集和比较规则
ALTER TABLE employees DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

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

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

查看mysql8的字符集

show variables like '%character%';

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

set character_set_connection = gbk;

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

select * from t where s = '我';

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

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

  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 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。

    • 提示: 如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行

      一次字符集转换。

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

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

sql_mode的合理设置

宽松模式

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

并且不报错。

举例 ：我在创建一个表时，该表中有一个字段为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而报错。

模式的查看和设置

查看当前的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...'; #当前会话
永久设置方式：在/etc/myf中配置sql_mode

在myf文件(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

systemctl restart mysqld

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

本文标签： mysql

版权声明：本文标题：MySQL安装和基本使用 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/dianzi/1729565252a1206483.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。