Mysql字符集
在mysql8.0之前,server默认字符集为latin1,utf8字符集指向的是utf8mb3。在mysql8.0开始,数据库的默认编码就改为utf8mb4。
1. 修改字符集
查看默认使用的字符集
show variables like 'character%';
8.0
5.7
可以看到character_set_server
和character_set_database
不同,在未指定字符集创建数据库和数据表时就使用该字符集。
修改默认字符集
在linux中,修改/etc/my.cnf
配置文件,在【mysqld】最后加上character_set_server=utf8
,即可让服务启动时默认字符集为utf8。但是已有数据库和表的字符集不会变化。
修改已创建数据库字符集
alter database 数据库名 character set 'utf8'
修改已创建数据表字符集
alter table 表名 convert to character set 'utf8'
需要注意的是原有数据如果是用非utf8编码的话,数据本身编码不会改变,已有数据需要导出或删除,然后重写插入。
2. 各级别字符集
MySQL有4个级别的字符集和比较规则,分别是:
- 服务器级别
- 数据库级别
- 表级别
- 列级别
- character_set_server:服务器级别的字符集
- character_set_database:数据库级别的字符集
- character_set_client:服务器解码认为客户端的字符集
- character_set_connection:服务器处理请求时会将character_set_client转为character_set_connection
- character_set_results:服务器向客户端返回数据时的字符集
3. 请求到响应过程中字符集的变化
假设客户端发送请求为
select * from t where s = “我”
1.客户端发送请求
一般情况下客户端所使用的字符集和当前操作系统一致,不同操作系统使用的字符集可能不一样:
- 类unix系统使用的是utf8
- windows使用的是gbk
如果使用了可视化工具,比如navicat之类的,这些工具可能会使用自定义的字符集来编码发送到服务器。
当客户端使用的是utf8字符集,字符’我’在发送给服务器请求中的字节形式为:0xE68891
2.服务器接收
服务器接收客户端发送过来的一串二进制字节,他会认为这串字节采用的字符串是character_set_client
,然后会把这串字节转为character_set_connection
字符集编码的字符。
比如character_set_connection
如果设置为gbk,character_set_client
设置为utf8。服务器首先会按照ut8对请求的数据进行解码,得到字符串‘我’,然后按照gbk进行编码,得到:0xCED2
3. 应为表t的列采用的是gbk字符集,与character_set_connection一致,所以直接到列中找到字节值为0xCED2
的记录,最后找到了一条记录。
如果某个列使用的字符集和character_set_connection代表的字符集不一致的话,还需要进行一次字符集转换。
4 上一步找到的是0xCED2
,会先采用gbk进行解码得到字符串‘我’,然后再把这个字符串再使用utf8编码得到0xE68891,发送给客户端。
5 应为客户端的字符集是utf8,所以对0xE68891解码得到‘我’,从而显示出来。
开发中通常把character_set_server、character_set_connection和character_set_results这三个系统变量设置成一样的情况,这样减少了很多无谓的字符集转换。为了方便设置,mysql提供了一条非常简便的语句:
set NAMES 字符集名;
这一条语句的效果和执行这三条是一样的:
set character_set_client = 字符集名;
set character_set_connection = 字符集名;
set character_set_results = 字符集名;
如果想在启动客户端的时候就把这三个系统变量设置成一样的,那我们可以在配置文件中配置:
[client]
default-character-set=utf8
Mysql大小写规范
1. windows和linux平台的区别
在sql中,关键字和函数名是不区分字母大小写的,比如select、where、order、group by等关键字以及abs、mod、round、max等函数名。
不过在sql中,还是要确定大小写的规范。windows系统默认大小写不敏感,linux系统大小写铭感。
通过如下命令查看:
show variables like '%lower_case_table_names%'
在windows平台下该变量值为1,linux值为0。
- 0,大小写敏感。
- 1,大小写不敏感。创建的表、数据库都是以小写形式存放在磁盘上,对于sql语句都是转换为小写对表和数据库进行查找
- 2,创建的表和数据库依据语句上格式存放,凡是查找都是转换为小写进行。
MySQL在linux下数据库、表名、列名、别名大小写规则如下:
- 数据库名、表名、表别名、变量名严格区分大小写;
- 关键字、函数名在sql中不区分大小写;
- 字段和字段别名在所有情况下都忽略大小写;
2. 大小写规则设置
当想设置为大小写不敏感时,要在my.cnf配置文件的[mysqld]中加入lower_case_table_names=1
,然后重启服务器。需要注意的是:
- 但是重启数据库实例之前需要将原来的数据库和表转换为小写,否则找不到数据库名。
- 此参数适用于mysql5.7。在mysql8下禁止该操作。如果非要设置,具体步骤为:
1.停止mysql服务
2.删除数据目录,即删除/var/lib/mysql目录
3.在配置文件中添加lower_case_table_names=1
4.然后重启
3. sql命名建议
- 关键字和函数名全部大写;
- 数据库名、表名、表别名、字段名、字段别名等全部小写;
sql_mode
1.介绍
sql_mode会影响mysql支持的sql语法以及它执行的数据验证检查。通过设置该参数,可以完成不同严格程度的数据校验。
2. 宽松模式vs严格模式
3. 模式查看和设置
select @@session.sql_mode
select @@global.sql_mode
或者
show variables like 'sql_mode'