记一次 MySql sql_mode 引发的线上问题

发表于

前言

事发

最近有个版本要求修改评论长度限制，由64改为500，在调试过程中，评论始终只能显示出来200字，经过查库，发现字段长度被定为200，插入时被截断。超出长度不是因该报错吗？嗯，应该是sql_mode的问题。

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sql复制代码## 创建表 username 长度为8
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(8) DEFAULT NULL,
  `pwd` varchar(8) DEFAULT NULL,
  `seq` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=utf8;


## 插入 username 长度为9的值
INSERT INTO `test`(`username` , `pwd` , `seq`)
VALUES
	(
		'123456789' ,
		'133aaa' ,
		NULL
	);

## 插入成功
No errors;1 row affected,taking 3.2 ms

## 查询发现值被截断
select username from test;
> 12345678


## 改变sql_mode
set SESSION sql_mode  = 'STRICT_ALL_TABLES';

## 再次插入长度相同的值
INSERT INTO `test`(`username` , `pwd` , `seq`)
VALUES
	(
		'123456789' ,
		'133aaa' ,
		NULL
	);
	
## 报错
Data too long for column 'username' at row 1

SQL_MODE

MySQL服务器可以在不同的模式设置(sql_mode)下运行，并可以对不同的客户端进行不同的设置，通过sql_mode变量设置。

影响点：

  • 语法
  • 数据校验

设置SQL_MODE

默认的sql_mode值为NO_ENGINE_SUBSTITUTION，可在启动时及运行时设置该变量。

启动时设置：

  • 启动时命令行添加 —sql-mode=”A,B,C……”(多值使用“,”隔开)
  • 在my.cnf配置文件中添加sql-mode=”A,B,C……”(多值使用“,”隔开)

运行时设置：

1
2
Sql复制代码SET GLOBAL sql_mode = 'A,B,C……'; ## 全局 重连生效
SET SESSION sql_mode = 'A,B,C……';## 当前会话 即时生效

查看：

1
2
sql复制代码- SELECT @@GLOBAL.sql_mode 
- SELECT @@SESSION.sql_mode

注意：

  • 在表使用分区后，最好不要改变sql_mode，因为不同的sql_mode会影响一些计算结果，对分区策略产生影响
  • 主从服务器，使用相同的sql_mode，以实现数据同步

SQL_MODE可选值

其它几个影响语法及数据校验的值

  • ALLOW_INCALID_DATES

不校验时间的有效性，只会校验月在1-12之间，日在1-31之间，只针对DATE和DATETIME类型有效，对TIMESTAMP无效。

1
2
3
Sql复制代码insert into temp values('2019-02-31 01:01:01');

Incorrect datetime value: '2019-02-31 01:01:01' for column 'dt' at row 1
1
2
3
4
Sql复制代码set SESSION sql_mode  = 'ALLOW_INVALID_DATES';
insert into temp values('2019-02-31 01:01:01');

No errors;1 row affected,taking 3.2 ms
  • ANSI_QUOTES

双引号(“)被用作标识符引用字符，和“`”相同。

1
2
3
Sql复制代码truncate table "temp";

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"temp"' at line 1
1
2
3
4
Sql复制代码set SESSION sql_mode  = 'ANSI_QUOTES';
truncate table "temp";

No errors;0 row affected,taking 0.2 ms
  • ERROR_FOR_DEVISION_BY_ZERO

控制/0及mod(n,0)的验证，该模式开启后可/0及mod(n,0)操作会生成警告，如果此时开启了严格SQL模式(STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES)，会阻止语句执行，报错。不影响select语句。

  • HIGH_NOT_PRECEDENCE

提高逻辑运算NOT优先级

1
2
3
4
5
6
7
8
9
10
11
12
13
Sql复制代码select not 1;
> 0

select 1 between -1 and 2;
> 1

select not 1 between -1 and 2;
> 0

## 设置sql_mode
set SESSION sql_mode  = 'HIGH_NOT_PRECEDENCE';
select not 1 between -1 and 2;
> 0
  • ONLY_FULL_GROUP_BY

影响group by语法

1
2
3
4
5
6
7
8
9
10
11
Sql复制代码select count(0) ct,username from test group by pwd;
> 2 | 12345678




## 设置sql_mode
set SESSION sql_mode  = 'ONLY_FULL_GROUP_BY';

select count(0) ct,username from test group by pwd;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

更多

以上列举了几个对编码影响较大的选项，更多选项见官网说明。

总结

以后关于SQL的写法，和某人抬杠时，除了要确定MySQL版本之外，还要问一句，您是啥sql_mode…….O(∩_∩)O哈！。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%