首页 > 资讯 > 数据库 >MySQL默认字符集设置详情

733

分享到

MySQL默认字符集设置详情

2024-04-02 19:04:59 733人浏览泡泡鱼

摘要

目录查看各版本默认字符集修改Mysql5中的默认字符集已有库&表字符集的变更各级别的字符集字符集与比较规则utf8 与 utf8mb4比较规则请求到响应过程中字符集的变化sql大小写规范windows和linux

查看各版本默认字符集

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

使用以下命令可以查看MySQL的默认字符集：

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

MySQL:8.0.30 中执行：

MySQL默认字符集设置详情

MySQL:5.7.29 中执行

MySQL默认字符集设置详情

参数解释：

#服务器级别的默认字符集，创建数据库没有显示的指定字符集就使用该级别的默认字符集
character_set_server=latin1
#数据库级别的默认字符集，创建表没有显示的指定字符集就使用该级别的默认字符集
character_set_database=latin1

修改MySQL5中的默认字符集

因为我的MySQL是Docker部署，所以修改方式略微有一些不一样

先查看容器ID：

docker ps

MySQL默认字符集设置详情

根据容器ID进入容器：

docker exec -it e14 /bin/bash

进入 /etc/mysql/mysql.conf.d/ 目录

cd /etc/mysql/mysql.conf.d/

因为docker容器内未安装vim，所以需要先安装vim

#使用国内镜像

cp /etc/apt/sources.list /etc/apt/sources.list.bak

echo "" > /etc/apt/sources.list
echo "deb Http://mirrors.aliyun.com/debian buster main" >> /etc/apt/sources.list ;
echo "deb http://mirrors.aliyun.com/Debian-security buster/updates main" >> /etc/apt/sources.list ;
echo "deb http://mirrors.aliyun.com/debian buster-updates main" >> /etc/apt/sources.list;

更新apt-get指令：

apt-get update

安装vim：

 apt-get  install vim

在上面的目录下编辑mysqld.cnf：

vim mysqld.cnf

MySQL默认字符集设置详情

插入如下内容：

character_set_server=utf8

重启容器
再次进入容器查看默认编码

MySQL默认字符集设置详情

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

已有库&表字符集的变更

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

MySQL默认字符集设置详情

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

alter database dbtest1 character set 'utf8';

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

alter table t_emp convert to character set 'utf8';

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

各级别的字符集

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

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

执行如下SQL语句：

show variables like 'character%';

MySQL默认字符集设置详情

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

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

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

字符集与比较规则

utf8 与 utf8mb4

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

utf8mb3 ：阉割过的utf8 字符集，只使用1～3个字节表示字符（表情符号等会乱码）
utf8mb4 ：正宗的utf8 字符集，使用1～4个字节表示字符

比较规则

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

MySQL默认字符集设置详情

常用操作：

#查看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';

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

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

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

MySQL默认字符集设置详情

字符集在请求处理过程中的变化：

MySQL默认字符集设置详情

SQL大小写规范

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默认字符集设置详情

Linux系统下：

MySQL默认字符集设置详情

lower_case_table_names 参数值的设置：

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

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

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

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

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

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

Linux下大小写规则设置：

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

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名
此参数适用于MySQL5.7 ，在MySQL8 下禁止在重新启动 MySQL 服务时将lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的lower_case_table_names 值

SQL编写建议

如果你的变量名命名规范没有统一，就可能产生错误。

这里有一个有关命名规范的建议：

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

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

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

sql_mode的合理设置

宽松模式

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

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

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