SuperAPI AI 工具集
合作ENAI 行业资讯收藏
Skill 广场mariadb-syntax-sql-dml

mariadb-syntax-sql-dml

数据与分析

查看项目

更新于 10-16 07:06

技能介绍

用途

该技能用于编写和审查 MariaDB 的数据操作语句（DML），具体包括 INSERT、UPDATE、DELETE、REPLACE 与 upsert 语句，并帮助回答以下问题：为什么某一行没有被更新；如何把 MySQL 的 DML 写法迁移到 MariaDB；如何在插入或删除后拿到受影响行的数据。

主要能力

  • 提供 MariaDB DML 的确定性写法，重点在于规避那些「看起来能跑、实际会静默破坏数据」的陷阱。
  • 明确若干关键结论：优先使用 INSERT ... ON DUPLICATE KEY UPDATE 而非 REPLACE INTO，因为 REPLACE 底层是 DELETE 后 INSERT，会触发 ON DELETE CASCADE、触发 DELETE 与 INSERT 触发器，并且每次调用都会消耗一个新的 AUTO_INCREMENT 值。
  • 指出不要用 INSERT IGNORE 来表达「重复则跳过」，因为它会把 NOT NULL 违规静默强制为空字符串或零、抑制越界转换错误，并把其他约束违规都隐藏为警告。
  • 说明 ON DUPLICATE KEY UPDATE 在非零 auto_increment_increment 的表上走 UPDATE 分支时也会消耗 AUTO_INCREMENT 序列值，因此可能出现空洞；LAST_INSERT_ID() 只在走 INSERT 分支时返回新自增值，走 UPDATE 分支时需在 SET 列表中写 id = LAST_INSERT_ID(id) 才会返回既有行 id。
  • 指出当表存在多个 UNIQUE 索引时，ON DUPLICATE KEY UPDATE 的行为是未定义的；知识库原文为「若匹配到多个唯一索引，只有第一个会被更新。不建议在含多个唯一索引的表上使用该语句」。
  • 明确版本支持差异：UPDATE RETURNING 在 10.6-LTS、10.11-LTS、11.x、12.x 均不存在，仅从 MariaDB 13.0 起可用；RETURNING 支持于 INSERT（10.5 起）、DELETE（10.0 起）、REPLACE（10.5.0 起）。
  • 提醒多表 UPDATE / DELETE 的行顺序未定义，不应编写依赖哪一行先被更新的逻辑。
  • 区分 DELETE HISTORY 与普通 DELETE：前者清除系统版本表上的行历史，后者会创建历史，二者不可互换。
  • 强调 RETURNING 可在单次往返中返回受影响行的数据，替代旧的 INSERT ; SELECT LAST_INSERT_ID() 模式。
  • 提醒不带 WHERE 的 UPDATE 会更新所有行，建议在交互会话中使用 --safe-updates 模式，或始终带上主键谓词。

使用方式

技能以决策树和代码模式组织内容，供在编写 DML 时按场景查阅。

决策树共四组：

  1. 「我要做 upsert」：单一 UNIQUE 键（含 PRIMARY）时用 INSERT ... ON DUPLICATE KEY UPDATE，对 AUTO_INCREMENT、外键、触发器均安全，多行插入时在 SET 内用 VALUES(col)；存在多个 UNIQUE 键时不要使用该语句（未定义行为），除非这些唯一键不可能在同一次调用中匹配到不同的既有行，否则应改为先在应用代码中 SELECT 再分支，或重构 schema 使用单一复合 UNIQUE；没有 UNIQUE / PRIMARY 时，REPLACE INTO 等价于普通 INSERT，直接用 INSERT。
  2. 「要不要用 REPLACE INTO」：若表或子表存在任何 ON DELETE CASCADE，绝不要用 REPLACE，级联会清空子表，应改用 ODKU；若使用 AUTO_INCREMENT 且需要稳定 id，绝不要用 REPLACE；若存在会做审计或副作用的 DELETE 触发器，绝不要用 REPLACE；以上都不涉及的话 REPLACE INTO 虽然「合法」但仍不常见。
  3. 「想用 INSERT IGNORE 跳过重复」：如果不清楚 IGNORE 抑制的是全部错误而非仅重复错误，应停止并阅读反模式文档；确认越界、NOT NULL、字符集、外键错误在本场景中都可以被静默强制转换并仅发警告时（少见情况），才可用 INSERT IGNORE，且批量后要检查 SHOW WARNINGS 并记录数量；常见情况应改用 INSERT ... ON DUPLICATE KEY UPDATE id = id，匹配到一个唯一键时做无操作 UPDATE，其他错误正常暴露，返回行数为 0 表示重复、1 表示插入、2 表示更新。
  4. 「需要插入的 id 或行数据」：单条 INSERT 且单个 AUTO_INCREMENT 列时，用 LAST_INSERT_ID() 或 INSERT ... RETURNING id, created_at（10.5+）；多行 INSERT 要全部生成的 id 时用 RETURNING，因为 LAST_INSERT_ID() 只返回第一个生成的 id；DELETE 且需捕获删除了哪些行时用 DELETE ... RETURNING（10.0+）；UPDATE 且需要新旧值在 13.0 之前不支持 RETURNING，可选在事务中先 SELECT、再 UPDATE、再 SELECT，或使用带显式 OUT 参数的存储过程，或等待 13.0+ 提供的 UPDATE ... RETURNING OLD_VALUE(col)。

代码模式覆盖：单行 INSERT、多行 INSERT、INSERT SET、INSERT ... SELECT、upsert（ODKU，并说明返回码语义与 LAST_INSERT_ID 行为）、INSERT RETURNING（限制：RETURNING 列表中不能有聚合函数，也不能有多行/多列子查询）、REPLACE INTO（仅在安全时使用，默认用 ODKU）、带 ORDER BY + LIMIT 的单表 UPDATE、带 JOIN 的多表 UPDATE、带 JOIN 的多表 DELETE、带自引用子查询的 DELETE（MariaDB 自 10.3.1 起允许从子查询引用的表删除，需用派生表别名避免旧版本报错）、DELETE RETURNING、系统版本表上的 DELETE HISTORY（自 10.3 起，需要单独的 DELETE HISTORY 权限；普通 DELETE 不会清除历史，而是把活行移入历史并更新 row_end）。

适用场景

适用于编写或审查 MariaDB 的 INSERT、UPDATE、DELETE、REPLACE、upsert 语句；排查行未按预期更新；将 MySQL DML 模式迁移到 MariaDB；以及需要获取插入 id 或受影响行数据的场景。

限制

该技能内容限于上述 DML 语法与行为，并明确引用若干前置或相关主题：表创建、约束、AUTO_INCREMENT、UNIQUE 索引（mariadb-syntax-sql-ddl）、决定 ODKU 正确性的 UNIQUE 索引设计（mariadb-syntax-indexing）、系统版本表与 FOR SYSTEM_TIME AS OF（mariadb-syntax-system-versioning）、strict_mode、sql_mode 与外键约束失败（mariadb-errors-data-integrity）。资料未提供其他功能、收费或价格信息。