前段时间我接手了一个后台管理系统的迭代需求：统计周期结束后，需要把一张业务表里的同一个字段做一次翻转。我心想这还不简单，一条UPDATE就收工。于是写了一句UPDATE account SET available = ~available扔到预发环境，结果直接报错。查了很久才发现，这个字段是TINYINT UNSIGNED，而MySQL里~运算产出的结果是一个超大无符号整数，根本写不回去。
后来我把这个需求重新拆了一遍，发现"数据库表中对同一字段取反"这件事，远不止一种写法。数值正负取反、状态位翻转、按位取反，三种语义对应完全不同的SQL姿势，坑也各不相同。这篇就借着我的实际经历，把字段取反处理的常见写法、边界条件、生产环境批量更新方案一次性捋清楚，适合正在写后台状态切换、批量上下架、余额方向调整这类需求的同学参考。
1. 先说清楚：你要的"取反"到底是哪一种
取反听起来是一个动作，但在MySQL里至少分为三种完全不同的语义。开写之前不先对齐语义，后面所有判断都是空中楼阁。
1.1 数值方向取反：正负互换的业务场景
第一种是真正意义上的"相反数"，把正数变负数、负数变正数。这类需求在财务、库存、积分模块里很常见：
- 账户余额做冲正，原记录是 +100，现在要变成 -100；
- 库存调整单方向填反了，需要把调整量从 +5 改成 -5；
- 积分流水做撤销，加积分变成减积分。
它的核心SQL就是最朴素的写法：
UPDATE account_balance SET change_amount = -change_amount WHERE biz_id = 20240501;这条语句对INT、DECIMAL、FLOAT、DOUBLE等有符号数值类型都能正常工作。但有一个前提：字段本身必须允许负数。一旦字段是UNSIGNED，这条语句立刻变成雷。
1.2 状态位取反：0/1翻转的真实需求
第二种是我在实际项目里遇到最多的：状态字段在 0 和 1 之间做切换。上架/下架、启用/禁用、关注/取消关注、已读/未读，本质上都是把布尔语义的字段翻个面。
这类需求的词眼是"切换"，而不是"求相反数"。所以很多人习惯套用数值取反的写法SET field = -field，这在 0/1 场景下其实也能实现翻转：0 变 0（因为 -0 还是 0），1 变 -1。对，问题就出在这里——-0和0结果一样，状态根本没变；1变成-1，状态字段出现了第三种值。这类字段通常还会带索引、会被后台页面按 0/1 过滤，一旦出现 -1，查询结果立刻错乱。
正确做法是下面这样的显式翻转：
-- 最简洁的01翻转 UPDATE product SET on_sale = 1 - on_sale WHERE id = 10086; -- 兼容性更好的写法 UPDATE product SET on_sale = IF(on_sale = 1, 0, 1) WHERE id = 10086;这两行才是真正的状态位取反：1 变 0，0 变 1。
1.3 按位取反：位图字段的复杂场景
第三种是位运算层面的~，按二进制位逐位取反。这种操作通常用在位图字段上：一个INT字段里用不同bit位标记多种权限或特性，比如 bit0 表示是否允许发消息、bit1 表示是否允许建群，取反意味着所有标志一起翻转。
UPDATE user SET feature_flags = ~feature_flags WHERE id = 888;这个写法在程序语言里很自然，但在MySQL里要格外小心，因为MySQL对~的处理和大多数编程语言不完全一样。后面专门讲坑。
2. 五种取反SQL写法盘点：写法决定结果
我整理了一张对照表，可以覆盖绝大多数取反需求，先看再选。
| 写法 | 语义 | 适用字段 | 典型坑 |
|---|---|---|---|
SET f = -f | 数值相反数 | 有符号数值字段 | UNSIGNED溢出，0取反还是0 |
SET f = 1 - f | 0/1翻转 | TINYINT(1)、BOOL | 字段中出现其他值时结果异常 |
SET f = IF(f=1,0,1) | 0/1显式翻转 | 任意数值字段 | 无，最稳 |
SET f = NOT f | 逻辑取反 | 0/1字段 | NULL返回NULL，2会变成0 |
SET f = ~f | 按位取反 | 位图字段 | 结果可能为超大无符号数 |
下面把每种写法展开说。
2.1 为什么减法/IF写法才是状态取反的稳妥选择
对于状态位翻转，我个人的首选是1 - field，然后是IF写法。原因很简单：
1 - f在 f 只能是 0 或 1 的前提下，结果一定还是 0 或 1，不需要额外的运算开销，走索引也不受影响，一条普通UPDATE就能瞬间完成。它的问题只有一个：如果字段被脏数据污染，比如某行状态因为历史bug变成了2，1 - 2 = -1，取反之后又多了一种状态。所以在严格要求字段值域的业务里，我更推荐IF(field = 1, 0, 1)：
UPDATE product SET on_sale = IF(on_sale = 1, 0, 1) WHERE id = 10086;它可以明确把"不是1的所有情况都变成1"，即使原值是2、3、-1，也会被拉回0，相当于顺带做了数据规整。IF写法在MySQL里又不会做隐式类型转换去猜你的意图，语义上完全自解释。
2.2 少见的XOR写法与其他冷门姿势
除了上面几种，MySQL还支持XOR逻辑运算符，0/1翻转同样可以用行号去替代：
UPDATE product SET on_sale = on_sale XOR 1 WHERE id = 10086;0 XOR 1结果是1，1 XOR 1结果是0，效果和一减一完全一样。但我要泼盆冷水：这个写法看起来巧妙，实际排错的时候，维护的人大概率要想好几秒才反应过来这是在翻转状态。生产环境里代码是写给人看的，除非你明确知道接手的人对这类运算符有同样敏感度，否则不建议用。
类似冷门写法还有ABS(field - 1)、(field + 1) % 2，都能实现0/1切换，但它们都额外引入了函数计算，对索引判断和代码可读性都更差。知道有这回事就行，线上别用。
2.3 数值取反的正确姿势与边界提醒
数值正负取反的场景没法用减法或IF替代，老老实实用SET f = -f。但写这条SQL之前，务必先确认三件事：
- 字段是
SIGNED，用SHOW CREATE TABLE或DESC确认； - 业务允许负值出现，上一条取反后的数据不能被其他逻辑误判；
- 如果有
CHECK约束（MySQL 8.0.16+），取反后的结果不能违反约束。
-- 先确认字段类型 SHOW FULL COLUMNS FROM account_balance LIKE 'change_amount'; -- 再预览结果 SELECT id, change_amount, -change_amount AS new_amount FROM account_balance WHERE biz_id = 20240501;我在实际项目里吃过一次亏：字段带CHECK (change_amount > 0)约束，取反后直接违反约束无法更新。预览这一步如果放在事务里先跑一边，能提前发现问题。
3. 三种字段类型与三种数据值：取反的隐含边界
就算你已经选对了SQL写法，字段本身的数据类型和存量数据值也可能会让结果失控。这一节全是实战里踩出来的。
3.1 无符号整数：报错还是溢出
先说最典型的现象：对INT UNSIGNED字段执行SET field = -field。在严格SQL模式下，MySQL会直接给出：
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range原因很好理解：无符号字段的语义是"我不接受任何负数"，你偏要往它里面写负数，它只能拒绝。如果关闭了严格模式，MySQL会把超范围的值做一次隐式转换，有些版本会变成0，有些版本会变成类型边界值，结果完全不可预测。
~按位取反也一样：MySQL对~0的结果是18446744073709551615，这是一个64位无符号整数，赋值给TINYINT或INT字段时，大概率触发Data truncation警告，严格模式下直接报错。
提示：准备写取反SQL之前，先确认字段是否带 UNSIGNED。如果字段必须保留无符号属性，那请改用
IF或CASE这类显式逻辑翻转，不要碰-field和~field。
3.2 NULL值处理：取反不一定是反转
几乎所有数值运算符遇到NULL，结果都是NULL。取反运算也不例外：
-- 如果字段是NULL，下面这条的结果仍然是NULL UPDATE product SET on_sale = 1 - on_sale WHERE id = 10086;这一行的效果是：原来是0或1的行正常翻转，原来是NULL的行翻转完还是NULL。如果业务不允许状态为空，等于把NULL行漏掉了，后续查出来既不是上架也不是下架，后台列表直接显示异常。
稳妥的做法是在更新前显式决定NULL去向：
-- NULL当成0处理，非NULL的正常翻转 UPDATE product SET on_sale = IF(on_sale IS NULL, 1, IF(on_sale = 1, 0, 1)) WHERE id IN (10086, 10087);或者更简单：把NULL行排除在本次更新之外，单独用一条SQL修正：
UPDATE product SET on_sale = 1 WHERE on_sale IS NULL;关键是想清楚业务语义：NULL是否是一个合法状态？它不是的话，就别让取反操作把它继续留着。
3.3 字符串字段与隐式转换的意外
有些历史表设计得比较随意，状态字段用的是VARCHAR，里面存着字符串 "0"、"1" 甚至 "true"、"false"。对这种字段执行取反，MySQL会先把字符串转成数字参与计算，再写回字符串。
-- 假设 status 是 VARCHAR(10) UPDATE product SET status = 1 - status; -- MySQL内部先把'1'转成数字1看起来能跑，但存在两个隐患：第一，非数字字符串转数字后是0，取反后变成1，原本的 "true" 直接被改成了 "1"，数据风格变了；第二，如果字段值很长，转数值时可能触发告警Truncated incorrect DOUBLE value。
我的建议是，字符串字段不要走数值取反写法。如果存量数据必须做转换，先写UPDATE把字符串映射成标准数字，再建一个严格的0/1状态字段，最后再取反。该补的数据结构债，不应该在SQL技巧层面省。
4. 生产环境批量取反：一条SQL不是最优解
前面讨论的示例都是单行/少量行更新。真实生产环境里，"把所有符合条件的老数据取反一遍"才是常态。这时候如果直接甩一条全表UPDATE，会踩到比SQL语法更深的问题。
4.1 全表UPDATE的锁与主从延迟
假设有一张几百万行的订单表，需要把所有某个客户的状态位翻转：
UPDATE order_table SET is_valid = 1 - is_valid WHERE customer_id = 88888;如果customer_id上有索引，MySQL会扫出这批记录并逐行加锁。批量大时，锁的范围随之变大，事务持续时间变长，其他业务对这个表的写入就会排队等待。更麻烦的是基于行的主从复制：每行变更都会生成一个binlog事件，大批量一旦发生，从库的SQL线程会明显追不上主库，从库上的读请求就会查到老数据，连带影响一系列报表和缓存。
所以在大表上做全量取反，不是为了省事用一条SQL，而是要考虑怎么把影响面控制住。
4.2 分批取反的脚本化实现
我常用的方案有两种，按场景选。
第一种方案，适合"把当前符合条件的行翻转"且业务上允许新数据不参与本次翻转的场景，用LIMIT循环：
-- 每批处理1000行 UPDATE product SET on_sale = IF(on_sale = 1, 0, 1) WHERE on_sale = 1 LIMIT 1000;然后用客户端脚本循环执行这行SQL，直到受影响行数为0。注意MySQL的UPDATE ... LIMIT本身是支持的，但一定要配合WHERE条件，否则LIMIT只是限制"更新到第几行就停"，语义不对。
第二种方案更稳：先把需要翻转的主键捞进临时表，再按主键范围分批更新，避免LIMIT循环中因为条件不断变化而出现无限循环或漏数据。
-- 1. 先固定要更新的ID清单 CREATE TEMPORARY TABLE tmp_toggle_ids AS SELECT id FROM product WHERE on_sale IN (0, 1); ALTER TABLE tmp_toggle_ids ADD PRIMARY KEY (id); -- 2. 按主键范围分批执行，每批5000行 SET @min_id = (SELECT MIN(id) FROM tmp_toggle_ids); SET @max_id = (SELECT MAX(id) FROM tmp_toggle_ids); SET @step = 5000; WHILE @min_id <= @max_id DO UPDATE product p SET p.on_sale = IF(p.on_sale = 1, 0, 1) WHERE p.id >= @min_id AND p.id < @min_id + @step AND EXISTS (SELECT 1 FROM tmp_toggle_ids t WHERE t.id = p.id); SET @min_id = @min_id + @step; END WHILE;这个逻辑在存储过程、Python脚本、或直接借助数据库客户端执行都可以。核心思想是：先把需要操作的ID集合冻结下来，然后按主键范围游标推进，每批只锁一部分行，binlog也能匀速产生，从库压力小很多。
4.3 先验证再执行的三步检查法
无论哪种方案，我在生产执行前都会走一遍这个检查流程，能劝退90%的线上事故。
第一步，预览结果。先不更新，只查询：
SELECT id, on_sale AS old_value, IF(on_sale = 1, 0, 1) AS new_value FROM product WHERE on_sale IN (0, 1) LIMIT 20;肉眼看一眼新旧值是否真的符合预期，特别是NULL行有没有混进来。
第二步，确认执行计划。对批量UPDATE啊，尽量用EXPLAIN看一下WHERE条件能不能用上索引。大批量更新一个低区分度字段（比如状态只有0/1），优化器可能选择全表扫描，这时你按ID分批反而更可控。
EXPLAIN SELECT id FROM product WHERE on_sale IN (0, 1);第三步，备份关键数据。线上表比较庞大时，不一定整表mysqldump，可以先把受影响的ID和新旧值导成CSV，或者建一张备份表存主键维度快照：
CREATE TABLE product_toggle_bak_20240520 AS SELECT id, on_sale AS old_value, NOW() AS backup_time FROM product WHERE on_sale IN (0, 1);一旦发现取反结果有误，用product_toggle_bak_20240520就能精准恢复原值，不至于对着binlog焦头烂额。
5. 取反操作背后的联动与异常兜底
取反本身只是UPDATE一句，但业务上往往还牵动缓存、通知、日志和后续校验。这一节聊几个容易忽略的延伸点。
5.1 触发器自动取反的适用边界
有人会想：能不能在表上建个触发器，满足某个条件就自动取反？比如插入时如果状态为1，就自动改成0。技术上可行：
CREATE TRIGGER trg_product_auto_toggle BEFORE UPDATE ON product FOR EACH ROW BEGIN IF NEW.on_sale = 1 THEN SET NEW.on_sale = 0; END IF; END;但我要劝一句：触发器会让业务逻辑变得隐性。哪天排查线上问题，没人会想到还有一层隐藏规则在改写字段，定位问题的时间会成倍增加。我的原则是——触发器只做最基础的约束兜底，比如时间戳自动更新、格式规整，不做业务状态翻转这种高频、有明确业务语义的操作。高频表上一旦建了触发器，每次UPDATE都多一次隐性开销，批量更新时这个开销会被放大。
5.2 缓存失效与业务通知的顺序
状态字段取反后，最常见的联动是清缓存、发消息。这里有个顺序问题。
假设商品状态从0改成1，业务上需要通知下游"商品重新上架"。正确的步骤是：
- 在事务里完成UPDATE；
- 事务提交后，删除Redis缓存或标记失效；
- 再通过消息队列发送上架事件。
绝对不能把发消息放在事务提交之前。否则事务回滚了，消息已经发出，下游业务拿着一个"上架成功"的状态去做下一步操作，实际数据根本没变，这种不一致极难修复。我在项目里就见过因为顺序写反导致的消息补偿逻辑，来回对账了半天才定位到。
5.3 误操作后的恢复逻辑
取反有一个天然特性：同一个字段连续取反两次，会恢复原值。所以对于纯状态翻转类误操作，理论上可以把原来的SQL再执行一次。但这里有个前提：两次操作之间，不能有新的写入覆盖这个字段。一旦有其他业务在这期间更新了同一行，反向执行就会把别人的数据也一起翻转，反而造成二次事故。
所以我的经验是：误操作后，第一时间先锁表或断开应用写入，再用备份表数据比对恢复，不要简单反向执行。上面建的product_toggle_bak_20240520备份表就能派上用场：
-- 用备份表精确恢复 UPDATE product p JOIN product_toggle_bak_20240520 b ON p.id = b.id SET p.on_sale = b.old_value;如果连备份都没有，才考虑从binlog里解析出对应时段的UPDATE事件，用mysqlbinlog把取反前的值捞出来。整个过程很费时间，所以"先备份再操作"永远是批量取反的第一条纪律。
回到开头那个让我翻车的场景，现在的我会先分清取反语义、检查字段类型、预览结果、按ID分批执行，再补上备份与缓存联动。整个过程不再是最开始那一行看着很酷的~field，而是一套能落地、敢交付的操作方案。数据库里同一个字段的取反处理，看似简单，但凡是能影响到线上状态的更新操作，都值得多花三分钟把边界条件过一遍。
