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

·  阅读 564

前言

事发

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

验证

## 创建表 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……"(多值使用“,”隔开)

运行时设置:

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

查看:

- 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无效。

    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
    复制代码
    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

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

    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
    复制代码
    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优先级

    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语法

    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哈!。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改