MySQL DML 高频操作速查表

DML（Data Manipulation Language，数据操纵语言）用于改变表中的数据。日常最常用的是 INSERT、UPDATE 和 DELETE。

本文以 MySQL 8.0 及 users、products、orders、order_items 表为例。纯查询操作请查看 mysql-dql-cheatsheet.md。

执行批量更新或删除前，先用完全相同的 WHERE 条件执行 SELECT，确认范围后再操作。

1. 中文数据写入前的字符集检查

表使用 utf8mb4 并不代表连接一定正确。写入中文或 Emoji 前，可以检查当前连接：

SELECT
  @@character_set_client,
  @@character_set_connection,
  @@character_set_results,
  @@collation_connection;

当前会话统一为 utf8mb4：

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

真实 Node.js 项目应在连接池中统一配置字符集，不要依赖每次手动执行 SET NAMES。源码、终端、SQL 文件和数据库连接也应统一使用 UTF-8。

2. INSERT：新增数据

2.1 新增一条记录

INSERT INTO users (username, email, phone)
VALUES ('张三', 'zhangsan@example.com', '+86-13800138000');

推荐明确写出字段名，不要依赖表中字段的物理顺序。

2.2 使用默认值和 NULL

INSERT INTO users (username, email, phone, status)
VALUES ('李四', 'lisi@example.com', NULL, DEFAULT);

• NULL 表示未知或未填写。
• DEFAULT 使用字段定义中的默认值。
• NULL、空字符串 '' 和数字 0 含义不同。

2.3 批量新增

INSERT INTO products (sku, name, price, stock)
VALUES
  ('SKU-1001', '无线鼠标', 99.00, 100),
  ('SKU-1002', '机械键盘', 399.00, 50),
  ('SKU-1003', '显示器支架', 199.50, 30);

批量插入通常比循环执行多次单条 INSERT 更高效。单批数据不宜无限增大，应根据 SQL 包大小、事务时间和锁竞争分批处理。

2.4 复制查询结果

INSERT INTO disabled_users_backup (user_id, username, email)
SELECT id, username, email
FROM users
WHERE status = 0;

目标字段与查询结果的数量、顺序和类型必须兼容。

2.5 唯一键冲突时更新

MySQL 8.0.19 及之后可以给新增行设置别名：

INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-1001', '无线鼠标（新版）', 109.00, 120) AS new
ON DUPLICATE KEY UPDATE
  name = new.name,
  price = new.price,
  stock = new.stock;

当主键或唯一索引冲突时改为更新，适合“存在则更新，不存在则新增”。旧写法 VALUES(column) 已被废弃，不建议在新代码中继续使用。

2.6 忽略冲突

INSERT IGNORE INTO users (username, email)
VALUES ('重复用户', 'zhangsan@example.com');

INSERT IGNORE 会把部分错误降级为警告，可能掩盖数据质量问题。只在明确允许跳过异常行时使用，并检查：

SHOW WARNINGS;

2.7 获取自增 ID

SELECT LAST_INSERT_ID();

该值与当前数据库连接绑定。Node.js 数据库驱动通常会直接返回 insertId，无需额外查询。

2.8 中文唯一值的大小写规则

唯一索引是否区分英文大小写取决于字段排序规则。例如使用 utf8mb4_0900_ai_ci 时：

User@example.com
user@example.com

通常会被认为是相同值。邮箱业务一般还会在应用层去除首尾空格并统一转换为小写，再写入数据库。

3. UPDATE：更新数据

3.1 根据主键更新

UPDATE users
SET username = '张三（新昵称）',
    phone = '+86-13900139000'
WHERE id = 1;

3.2 根据条件批量更新

先查询：

SELECT id, order_no, status
FROM orders
WHERE status = 'pending'
  AND created_at < CURRENT_TIMESTAMP - INTERVAL 30 MINUTE;

确认后更新：

UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
  AND created_at < CURRENT_TIMESTAMP - INTERVAL 30 MINUTE;

3.3 基于原值增减

UPDATE products
SET stock = stock + 20
WHERE id = 1001;

并发扣库存应把检查和扣减放在同一条 SQL 中：

UPDATE products
SET stock = stock - 2
WHERE id = 1001
  AND stock >= 2;

应用程序必须检查受影响行数。如果为 0，通常表示商品不存在或库存不足。

3.4 批量更新为不同值

UPDATE products
SET price = CASE id
  WHEN 1001 THEN 89.00
  WHEN 1002 THEN 369.00
  WHEN 1003 THEN 179.00
END
WHERE id IN (1001, 1002, 1003);

末尾的 WHERE id IN (...) 不可省略，否则其他记录可能被更新为 NULL 或意外值。

3.5 关联更新

将有已支付订单的用户标记为活跃：

UPDATE users AS u
INNER JOIN orders AS o ON o.user_id = u.id
SET u.status = 1
WHERE o.status = 'paid';

执行前先改写成查询，确认用户范围：

SELECT DISTINCT u.id, u.username
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid';

3.6 更新 JSON 属性

UPDATE products
SET attributes = JSON_SET(
  COALESCE(attributes, JSON_OBJECT()),
  '$.color',
  '深空灰'
)
WHERE id = 1001;

如果原字段可能为 NULL，先用 COALESCE 提供一个空 JSON 对象。

3.7 软删除和恢复

软删除：

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 3
  AND deleted_at IS NULL;

恢复：

UPDATE users
SET deleted_at = NULL
WHERE id = 3
  AND deleted_at IS NOT NULL;

软删除保留历史数据，但所有正常业务查询必须统一添加 deleted_at IS NULL。它也不会自动释放邮箱等唯一索引。

3.8 查看受影响行数

SELECT ROW_COUNT() AS affected_rows;

在应用程序中优先读取驱动返回的 affectedRows。它为 0 时，要结合 SQL 判断是记录不存在、条件不满足，还是值没有发生数据库认定的变化。

4. DELETE：删除数据

4.1 根据主键删除

DELETE FROM order_items
WHERE id = 9001;

4.2 按条件批量删除

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < CURRENT_DATE - INTERVAL 1 YEAR;

批量删除大量数据时应分批进行，避免长事务、日志膨胀和长时间锁定。

一种简单的分批方式：

DELETE FROM operation_logs
WHERE created_at < CURRENT_DATE - INTERVAL 180 DAY
ORDER BY id
LIMIT 1000;

重复执行直到受影响行数为 0。每批提交并观察数据库负载。

4.3 关联删除

删除已软删除用户的历史草稿：

DELETE d
FROM drafts AS d
INNER JOIN users AS u ON u.id = d.user_id
WHERE u.deleted_at IS NOT NULL;

执行前先使用相同的 JOIN 和 WHERE 查询目标记录。

4.4 外键对删除的影响

用户被订单引用时，直接删除可能失败：

DELETE FROM users WHERE id = 1;

常见策略：

• 保留用户并使用软删除。
• 先按正确业务顺序删除子表数据。
• 明确使用外键 ON DELETE CASCADE，让数据库级联删除。

不要为了“删除方便”就默认使用 CASCADE。订单、账单、审计记录通常需要保留。

5. 事务：保证多条写操作一致

事务严格来说属于 TCL，但它与日常 DML 紧密相关，因此在此速查表中一起说明。

5.1 基本事务

START TRANSACTION;

UPDATE products
SET stock = stock - 2
WHERE id = 1001
  AND stock >= 2;

INSERT INTO orders (order_no, user_id, total_amount, status)
VALUES ('ORD202608080001', 1, 198.00, 'pending');

COMMIT;

发生 SQL 错误或业务条件不满足时：

ROLLBACK;

应用程序必须检查扣库存的受影响行数，不能仅因为 SQL 没有抛错就继续创建订单。

5.2 保存点

START TRANSACTION;

INSERT INTO orders (order_no, user_id, total_amount, status)
VALUES ('ORD202608080002', 1, 399.00, 'pending');

SAVEPOINT order_created;

INSERT INTO order_items (
  order_id,
  product_id,
  product_name,
  unit_price,
  quantity,
  subtotal
)
VALUES (LAST_INSERT_ID(), 1002, '机械键盘', 399.00, 1, 399.00);

-- 只回滚到保存点
ROLLBACK TO SAVEPOINT order_created;

COMMIT;

保存点适合一个较长事务中的局部回退，但不能替代清晰的事务设计。

5.3 事务注意事项

• 事务中的全部 SQL 必须使用同一个数据库连接。
• 成功执行 COMMIT，异常时执行 ROLLBACK。
• 不要在事务中进行耗时网络请求或等待用户输入。
• 控制事务大小和持续时间，减少锁竞争。
• DDL 可能触发隐式提交，不要随意混入业务事务。
• Node.js 连接无论成功失败都应归还连接池。

6. Node.js 参数化写入

不要拼接用户输入：

// 不安全：输入可能改变 SQL 结构
const sql = `UPDATE users SET username = '${username}' WHERE id = ${id}`;

使用 mysql2/promise 参数化查询：

import type { ResultSetHeader } from "mysql2";
import { pool } from "./database";

interface UpdateUserInput {
  id: number;
  username: string;
}

async function updateUsername(input: UpdateUserInput): Promise<boolean> {
  const [result] = await pool.execute<ResultSetHeader>(
    `UPDATE users
     SET username = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [input.username, input.id],
  );

  return result.affectedRows === 1;
}

? 只能替代值，不能替代表名、字段名和 ASC、DESC 等 SQL 结构。动态结构必须使用应用代码白名单。

Node.js 连接池应统一使用支持中文的连接字符集，并确保库表也是 utf8mb4。如果中文写入后乱码，要同时检查表定义、连接设置和源文件编码。

7. DML 安全检查清单

• 是否明确写出了 INSERT 的字段名？
• 中文、Emoji 写入前，连接和表是否都是 utf8mb4？
• 唯一字段是否考虑了排序规则带来的大小写比较行为？
• UPDATE、DELETE 是否先用相同条件执行了 SELECT？
• AND、OR 混用时是否添加了括号？
• 是否知道预计和实际影响行数？
• 多步操作是否需要事务？
• 批量操作是否需要分批，避免长事务？
• 应用代码是否使用参数化查询？
• 是否保留了业务要求的订单、账单和审计数据？

8. 一页式 DML 模板

-- 单条新增
INSERT INTO users (username, email, phone)
VALUES (?, ?, ?);

-- 批量新增
INSERT INTO products (sku, name, price, stock)
VALUES (?, ?, ?, ?), (?, ?, ?, ?);

-- 存在则更新，不存在则新增（MySQL 8.0.19+）
INSERT INTO products (sku, name, price, stock)
VALUES (?, ?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
  name = new.name,
  price = new.price,
  stock = new.stock;

-- 条件更新
UPDATE users
SET username = ?, phone = ?
WHERE id = ? AND deleted_at IS NULL;

-- 安全扣库存
UPDATE products
SET stock = stock - ?
WHERE id = ? AND stock >= ?;

-- 软删除
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = ? AND deleted_at IS NULL;

-- 物理删除
DELETE FROM order_items
WHERE id = ?;

-- 事务
START TRANSACTION;
-- 多条 DML
COMMIT;
-- 出错时 ROLLBACK;