2025-09-28发表2025-09-28更新MySQL16 分钟读完 (大约2397个字)0次访问

MySQL中两个很少用但又很实用的隐藏的利器

公司的业务系统使用的数据以MySQL 居多，平时人岗架等基础数据同步也是极为常见，像以前，都是先按指定条件查询查询有无存在数据，存在则修改、不存在则新增。最近学到了两个比较有用的mysql用法

IS FALSE

基本语法和工作原理

IS FALSE用于判断一个表达式的结果是否为“假”。在MySQL语义中，“假”覆盖两种情形：

表达式求值为0
表达式求值为NULL
基本形式：

expression IS FALSE
真值规则概览：

结果为0 → 返回 TRUE
结果为NULL → 返回 TRUE
结果为非零值 → 返回 FALSE
示例：

SELECT 1 IS FALSE; – 0
SELECT 0 IS FALSE; – 1
SELECT NULL IS FALSE; – 1

– 返回所有status为0或NULL的记录
SELECT * FROM orders WHERE status IS FALSE;
– 等价于
– SELECT * FROM orders WHERE status = 0 OR status IS NULL;

注意：MySQL中的“布尔”通常用TINYINT(1)承载，除0/1以外的非零值（如2、-1）也被视为TRUE。建议在业务层面约束取值并在数据库层面通过CHECK或应用逻辑保证0/1规范

与IS TRUE、IS NULL的对比

IS TRUE：当表达式为非零且非NULL时为TRUE，NULL不匹配
IS FALSE：当表达式为0或NULL时为TRUE，包含NULL
IS NULL：仅当表达式为NULL时为TRUE

典型对比：

– 仅匹配值为0（不包含NULL）
SELECT * FROM t WHERE status = 0;

– 仅匹配值为NULL（不包含0）
SELECT * FROM t WHERE status IS NULL;

– 匹配0或NULL（同时涵盖“未设置”与“显式为假”）
SELECT * FROM t WHERE status IS FALSE;
与NOT的差异（NULL参与时尤为重要）：

– 当status为NULL时：
– NOT status 的结果为NULL（而非TRUE），因此会被WHERE过滤掉
– status IS FALSE 的结果为TRUE，会保留该行
SELECT * FROM t WHERE status IS FALSE; – 包含NULL与0
SELECT * FROM t WHERE NOT status; – 仅包含0，不包含NULL

三值逻辑在MySQL中的应用

MySQL在条件计算中采用三值逻辑（TRUE / FALSE / UNKNOWN）。凡是涉及NULL的比较，通常产生UNKNOWN：

SELECT NULL = NULL; – 结果为NULL（UNKNOWN）
在WHERE子句中，只有条件为TRUE的行才会被保留，FALSE与UNKNOWN都会被过滤。因此，判断NULL时应使用具名谓词：

SELECT * FROM users WHERE last_login IS NULL; – 明确匹配NULL
而对于“假”的统一收敛判断，IS FALSE提供了更直观的定义：

– 包含值为0或NULL两种“假”情形
SELECT * FROM flags WHERE enabled IS FALSE;
一个常见模式是对比较表达式直接应用IS FALSE：

– 查找不大于（<=）或存在NULL的一侧（使判定更“宽松”）
SELECT * FROM ranges WHERE (end_at > start_at) IS FALSE;
– 等价于：end_at <= start_at OR end_at IS NULL OR start_at IS NULL
这类表达式在需要“空值视作不满足条件”的业务中尤为有用

ON DUPLICATE KEY UPDATE详解

基本语法结构和工作原理

ON DUPLICATE KEY UPDATE 是 MySQL 在 INSERT 语句上的扩展，用于在插入数据发生“主键或唯一索引冲突”时，自动改为更新既有行，从而实现“存在则更新，不存在则插入”的幂等写入模式。

基本语法：

INSERT INTO table_name (col1, col2, ..., colN)
VALUES (val1, val2, ..., valN)
ON DUPLICATE KEY UPDATE
  colA = VALUES(colA),
  colB = colB + VALUES(colB),
  ...;

多行插入（批量）同样支持该语法：

INSERT INTO table_name (id, name, count)
VALUES
  (1, 'Alice', 1),
  (2, 'Bob',   3),
  (3, 'Cindy', 2)
ON DUPLICATE KEY UPDATE
  name  = VALUES(name),
  count = count + VALUES(count);

工作原理（执行流程概览）：

冲突检测：MySQL 先检测插入的值是否违反主键或任一唯一索引约束
行为选择：
无冲突 → 执行正常 INSERT
有冲突 → 执行 UPDATE，更新既有行的指定字段
原子性与锁机制：冲突检测阶段加共享锁（S），确定需要更新时升级为排他锁（X）；整个过程在同一语句内完成，具备原子性456
自增ID提示：即使最终执行的是 UPDATE，插入尝试阶段的自增计数器可能仍递增，导致ID不连续；但目标行的主键值并不会被更新替换4
示例：库存累加（“存在则累加，不存在则插入”）

INSERT INTO products (product_id, name, stock)
VALUES (1001, 'iPhone 15', 100)
ON DUPLICATE KEY UPDATE
  stock = stock + VALUES(stock);

若 product_id=1001 不存在 → 插入新行
若已存在 → 将 stock 在原值基础上累加 10046
该语法可用来写出简洁的“幂等写入”逻辑，减少应用层判断与往返，提高吞吐与可维护性。

与主键和唯一索引的关系

ON DUPLICATE KEY UPDATE 的触发条件是“主键或唯一索引冲突”，二者满足任意一个即可触发更新操作。

冲突检测规则：

主键优先：当同时存在主键与唯一索引时，主键冲突会优先触发更新
或逻辑：只要主键或任一唯一索引冲突，就会触发更新（不是需要全部冲突）78
示例：

1
2
3

INSERT INTO users (id, username, email)
VALUES (1, 'zhangsan', 'new@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

若 id=1 已存在 → 更新该行的 email
若 id 不存在但 username=’zhangsan’ 与唯一索引冲突 → 同样更新 email79
特殊注意事项：

多唯一索引风险：当表上存在多个唯一索引时，任一索引冲突都会触发更新，可能带来意料之外的行为。业务上应尽量收敛唯一约束的数量，或清晰划分更新策略
大小写敏感性：InnoDB 默认在某些字符集/排序规则下对字符串唯一索引不区分大小写，可通过字符集与校对规则（collation）设置调整，避免“看似不同、索引视为相同”的冲突

VALUES()函数的使用方法

在 ON DUPLICATE KEY UPDATE 子句中，VALUES(col_name) 用于引用本次 INSERT 语句中“为该列提供的原始插入值”（即便最终执行的是 UPDATE，也可以在 UPDATE 子句里访问这份插入时的值）。

基本用法：

INSERT INTO table_name (col1, col2)
VALUES (1, 2)
ON DUPLICATE KEY UPDATE
  col2 = VALUES(col1) + 1;  -- 在更新时使用插入时为 col1 提供的值

特性与场景：

值上下文：即使发生冲突并转为 UPDATE，VALUES(col) 依旧返回该行在 INSERT 子句内的原始值，便于实现“基于插入值的条件更新、累加计算”等1011
批量插入：对多行插入，每一行在 UPDATE 子句中的 VALUES() 引用都与该行的插入值一一对应，不会串行混淆1012
常见模式：

条件更新：只有当新值不为空（或满足条件）才覆盖旧值

INSERT INTO profiles (user_id, name, bio)
VALUES (1, 'Alice', NULL)
ON DUPLICATE KEY UPDATE
  name = IF(VALUES(name) != '', VALUES(name), name),
  bio  = COALESCE(VALUES(bio), bio);

累积计算：将插入值与现值组合，实现计数器/指标累加

INSERT INTO metrics (metric_key, day, cnt)
VALUES ('pv', CURDATE(), 1)
ON DUPLICATE KEY UPDATE
  cnt = cnt + VALUES(cnt);

与REPLACE INTO和INSERT IGNORE的对比

从冲突处理策略和数据侧影响看，三者的行为差异显著：

冲突处理
- ON DUPLICATE KEY UPDATE：发生冲突时更新既有行的指定字段
- REPLACE INTO：发生冲突时先删除旧行，再插入新行（等价于 DELETE + INSERT）
- INSERT IGNORE：冲突时直接“忽略”该行的插入，保持现状
数据保留与默认值
- ODKU：只更新声明的字段，未更新字段保持原值
- REPLACE：由于删除重插，未声明字段会重置为默认值（可能导致数据丢失）
- IGNORE：保持原有数据不变
自增ID与触发器
- ODKU：更新不会更换目标行的主键；插入阶段的自增计数器可能递增，导致不连续
- REPLACE：删除+插入会消耗新的自增ID，且会触发 DELETE 与 INSERT 触发器
- IGNORE：成功插入才消耗自增ID，跳过时不递增；仅触发成功的 INSERT 触发器
适用场景建议
- ODKU：需要在冲突时精确更新部分字段的场景（去重写入、计数器累加、快照回填）
- REPLACE：需要彻底替换整行的场景（慎用，避免默认值覆盖）
- IGNORE：仅需去重，冲突即跳过的场景（如幂等导入，对已有数据不做改动）

示例对比：

-- ON DUPLICATE KEY UPDATE：存在则更新
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = 'Alice';

-- REPLACE INTO：先删除旧行再插入新行
REPLACE INTO users (id, name) VALUES (1, 'Alice');

-- INSERT IGNORE：冲突时跳过插入
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');

IS FALSE：用于判断表达式是否为“假”，即值为0或为NULL；适合布尔/状态位、软删除标记、权限开关、未初始化字段等三态场景
INSERT INTO … ON DUPLICATE KEY UPDATE：实现“存在则更新，不存在则插入”的幂等写入，适用于去重写入、计数器/统计累加、缓存/快照回填、批量导入纠偏等

MySQL中两个很少用但又很实用的隐藏的利器

https://blogs.52fx.biz/posts/3810678666.html