MySQL UPDATE执行异常：首次更新正常后续操作灯光状态意外变更

异常原因 #

• 全表更新风险：你使用的UPDATE pmth.control SET fan ='on', water='off',light='on' WHERE 1语句没有指定具体的设备行过滤条件，WHERE 1会匹配表中所有行，只要存在其他客户端、定时任务、设备上报脚本等对同表同字段执行修改操作，就会覆盖你设置的值
• 日志覆盖不全：你当前贴出的日志仅记录了两次执行light='on'的操作成功的日志，没有覆盖到异常变OFF时间段的数据库操作日志，无法排除其他未被日志捕获的UPDATE语句执行了light='off'写入的可能
• 字段类型不匹配：如果light字段为枚举（ENUM）类型、布尔（BOOLEAN）类型，大小写不敏感配置不符合预期，或者存在字段长度限制截断了写入值，可能导致写入'on'时被自动转为0（对应OFF状态）
• 事务异常回滚：如果你的更新逻辑开启了事务但没有正确提交，或者执行过程中触发了事务回滚条件，会导致表面看起来执行成功的UPDATE操作最终未被持久化到数据库
• 数据库内置逻辑触发修改：如果表上配置了修改触发器、或者数据库存在定时执行的EVENT事件，可能在你的UPDATE执行后自动触发修改light字段为OFF的操作

解决方案 #

• 首先修改UPDATE语句的过滤条件，新增唯一设备ID作为WHERE条件，避免全表更新，示例修改为UPDATE pmth.control SET fan ='on', water='off',light='on' WHERE device_id = '你的设备唯一标识'
• 开启数据库的通用查询日志（General Log），记录所有到达数据库的SQL语句，当灯光状态异常变为OFF时，直接查询日志定位执行修改的SQL来源以及执行客户端
• 检查control表的字段定义，确认light字段的类型是否和写入值匹配，如果是ENUM类型确保'on'在枚举值列表中，如果是布尔类型统一使用1/0而非字符串写入避免隐式转换异常
• 检查你的业务代码逻辑，增加按钮防抖处理，避免短时间内多次触发按钮事件执行状态翻转逻辑，同时确认所有涉及light字段修改的代码分支都添加了对应日志输出
• 检查数据库是否存在定时事件（EVENT）、触发器（TRIGGER），是否有其他关联业务逻辑会自动修改light字段的值

内容的提问来源于stack exchange，提问作者JPX