MySQL字符集配置

igxiaoshan Lv5
  2023-10-30 14:23:46 2023-10-30 14:23:46 创建   2023-10-30 14:25:58 2023-10-30 14:25:58 更新      2.6k 字  9 分钟  

修改MySQL5.7字符集

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库

设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL

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

  • 查看默认使用的字符集

    1
    2
    3
    SHOW VARIABLES LIKE '%character%';
    或
    SHOW VARIABLES LIKE '%char%';

    重要的关注返回的数据

    character_set_server

    character_set_database

如果使用默认5.7的latin1字符集,保存中文的时候会报错

1
2
3
4
5
-- 查看数据库的创建
SHOW CREATE DATABASE '数据库名';

-- 查看数据表的创建
SHOW CREATE TABLE '数据表名';

  • 修改默认字符集

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    vi /etc/my.cnf

    ## 在MySQL5.7或之前版本中,在文件中加上中文字符集的设置
    character_set_server=utf8

    ### 注意:
    #### 添加配置项在[mysqld]之后

    ##### 修改后重启数据库服务
    systemctl restart mysqld

    ###### 注意: 修改字符集后,新创建的数据库,数据表会生效;但是原来的数据库,数据表不会发生变化

已有库&表字符集变更

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

需要对数据库,数据表进行修改

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

    1
    ALTER DATABASE '数据库名' CHARACTER SET 'utf8';

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

    1
    ALTER TABLE '数据表名' CHARACTER SET 'utf8';

注意: 原有的数据如果是用非 ‘utf8’ 编码的话,数据本身编码不会发生变化;已有数据需要导出或者删除,然后重新插入.

各级别的字符集

MySQL有四个级别的字符集和比较规则

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别
1
2
-- 查看数据库字符集
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: 服务器向客户端返回数据时使用的字符集

服务器级别的字符集

我们可以在启动服务器程序时通过启动选项或者在服务器运行过程中使用set语句修改

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

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

数据库级别的字符集

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

1
2
3
4
5
6
7
8
-- 举例
CREATE DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];
	
ALTER DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

表级别的字符集

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

1
2
3
4
5
6
7
8
-- 举例
CREATE TABLE 表名称 (列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称];
	
ALTER TABLE 表名称
	[[DEFALUT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]

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

列级别的字符集

对于存储字符串的列,用一个表中的不同列也可以有不同的字符集和比较规则.在创建和修改列定义的时候可以指定该列的字符集和比较规则

1
2
3
4
5
6
CREATE TABLE 表名(
	列名 数据类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列 ...
);

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

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

  • 注意: 在转换列的字符集时候,需要注意,如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误.例如;之前列使用的字符集是utf8,列中存储了一些文字,现在要把列转换成ASCII,就会报错,因为ASCII字符集并不能表示汉字字符

总结:

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

SQL大小写规范

在不同环境下,规则不一样;windows系统默认大小写不敏感但是linux系统是大小写敏感的

可以通过lower_case_table_names命令查看

1
SHOW VARIABLES LIKE '%lower_case_table_names%';
  • lower_case_table_names 参数值设置
    • 默认是0,大小写敏感
    • 设置成1,大小写不敏感

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

  • 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写,否则将找不到数据库名
  • 此参数适用于MySQL5.7,在MySQL8.0下禁止在重启MySQL服务时将lower_case_table_names设置成不同于初始化MySQL服务时设置的lower_case_table_names值.如果非要将MySQL设置为大小写不敏感的,具体操作如下
    • 1 停止MySQL服务
    • 2 删除数据目录, 即删除/var/lib/mysql目录
    • 3 在MySQL配置文件(/etc/my.cnf)中添加lower_case_table_names=1
    • 4 启动MySQL服务
1
2
3
4
## MySQL在linux下数据库名,表名,列名,别名大小写规则是这样的
### 数据库名, 表名, 表的别名, 列的别名是严格区分大小写的
### 关键字, 函数名称在sql中不区分大小写
### 列名(或字段名)与列的别名(或字段别名)在所有的情况下均是忽略
1
2
3
4
## 命名规范建议
### 关键字和函数全部大写
### 数据库名,表名,表别名,字段名,字段别名全部小写
### sql语句必须以分号结束

sql_mode的合理设置

宽松模式 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等数据库总想把关于数据的所有操作都自己包揽下来,包括数据的校验,其实开发中,我们应该自己开发的项目程序级别将这些校验给做了,虽然写项目的时候麻烦了一些步骤,但是这样做了之后,我们在数据库迁移或者项目迁移的时候,会方便很多

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

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

宽松模式例子

案例一

1
2
3
4
5
select * from employees group by department_id limit 10;

set sql_mode = ONLY_FULL_GROUP_BY;

select * from employees group by department_id limit 10; -- 执行报错

案例二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查看表的结构
desc test1;

-- 设置sql_mode
set sql_mode = '';

-- 故意将 int 字段插入 文字
insert into test1 (t_name, t_age)
values('tom','aaa');

-- 插入成功,查看警告
show warnings; -- Incorrect integer value: 'aaa' for column 'age' at row 1

-- 查看记录
select * from test1; -- "name = tom; age = 0"

-- 设置sql_mode模式为 STRICT_TRANS_TABLES ,然后插入数据
set sql_mode = 'STRICT_TRANS_TABLES';
insert into test1 values ('jerry','bbb'); -- 执行报错: ERROR 1366(HY000): Incorrect integer value ;'bbb' for column 'age' at row 1

模式查看和设置

  • 查看当前的sql_mode

    1
    2
    3
    4
    5
    6
    7
    -- 全局
    SELECT @@global.sql_mode;
    -- 会话
    SELECT @@session.sql_model;

    或者
    SHOW VARIABLES LIKE 'sql_mode';

  • 临时设置: 设置当前窗口sql_mode

    1
    2
    3
    4
    -- 全局
    SET GLOBAL sql_mode = 'modes ...';
    -- 当前会话
    SET SESSION sql_mode = 'modes ...';

  • 永久设置: 在/etc/my.cnf中配置sql_mode

    1
    2
    3
    4
    5
    6
    -- 在 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服务,也会永久生效
此页目录
MySQL字符集配置
  1. 修改MySQL5.7字符集
  2. 已有库&表字符集变更
  3. 各级别的字符集
    1. 服务器级别的字符集
    2. 数据库级别的字符集
    3. 表级别的字符集
    4. 如果创建和修改表的语句汇总没有指明字符集的比较规则,将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则
    5. 列级别的字符集
    6. 对于某个列来说,如果在创建和修改的语句中没有指定字符集和比较规则,将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则
  4. 注意: 在转换列的字符集时候,需要注意,如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误.例如;之前列使用的字符集是utf8,列中存储了一些文字,现在要把列转换成ASCII,就会报错,因为ASCII字符集并不能表示汉字字符
    1. 总结:
  5. SQL大小写规范
  6. sql_mode的合理设置
    1. 宽松模式 vs 严格模式
    2. 改为严格模式可能会存在的问题
    3. 宽松模式例子
    4. 模式查看和设置