MySQL隐式转换问题修复

最新推荐文章于 2024-11-11 12:58:20 发布

小艳爱程序

最新推荐文章于 2024-11-11 12:58:20 发布

阅读量505

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql隐式转换 sql_mode

本文链接：https://blog.youkuaiyun.com/qq_40012791/article/details/96429659

MySQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一次因SQL语句中的隐式转换问题导致大量数据被误更新的事故。原本仅计划更新一条记录，但由于隐式转换的影响，最终错误地更新了80多万条数据。文中详细分析了问题的原因，并提供了通过调整SQL_MODE和利用binlog进行数据恢复的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、背景
某业务一条UPDATE SQL where条件中存在隐式转换，预期要更新1条数据，实际由于隐式转换更新了80W条数据，更新后数据异常。

二、问题SQL
UPDATE `xm_user` SET `status`=0 WHERE `user_number`=0;

三、表结构
表数据有一千多万；user_number为用户的唯一标识，其中有M159632/45841315两种格式的数据；

CREATE TABLE `xm_user` (
  `user_number` varchar(20) NOT NULL COMMENT '主键；用户唯一标识',
  `status` int(11) unsigned NOT NULL COMMENT '0：不可使用；1：可使用',
  PRIMARY KEY (`user_number`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基本信息表'

四、查询执行 
select count（0）from `xm_user`  WHERE `user_number`=0; 查出80多万数据；
select count（0）from `xm_user`  WHERE `user_number`='0'; 查询0条数据；

五、SQL_MODE避免
当sql_mode设置为严格模式（STRICT_TRANS_TABLES）时，更新将会报错：ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'xxxxxx'。
查看配置：show global variables like '%sql_mode%';
sql_mode加上严格模式限制set sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

六、数据修复
1）找到第一次异常更新的binlog 起始和截止postion，用binlog2sql生成回滚语句
2) 由于等于0的数据进行了多次更新，导致字段的时间戳发生了变化，binlog2sql生成的语句where条件包含所有字段，所以无法匹配。另外生成的回滚json当做where条件也匹配不到数据。
3）去掉其他where条件，只保留主键进行更新。awk -F"AND" '{print $1"AND"$6}'|awk '{for(i=1;i<=NF-3;i++){printf $i" ";}{print $NF";";}}'