Node系列 · 数据库：DML 增删改

DML（Data Manipulation Language）是日常开发最高频的 SQL：增、删、改、查。其中"查"是 DQL 单列的，本章只讲"增、删、改"三件——它们看似简单，错误用法却是线上事故的最大来源。

一、INSERT 新增

1.1 单条插入

INSERT INTO `student` (`stuno`, `name`, `sex`)
VALUES ('2024001', '张三', b'1');

1.2 批量插入

INSERT INTO `student` (`stuno`, `name`, `sex`) VALUES
  ('2024002', '李四', b'1'),
  ('2024003', '王五', b'0'),
  ('2024004', '赵六', b'1');

批量插入比循环单条插入快 10-100 倍——只发一次网络往返、数据库一次解析。

1.3 插入或更新（ON DUPLICATE KEY UPDATE）

如果主键或唯一键已存在，就更新；否则插入。常用于"幂等写入"：

INSERT INTO `user_score` (`user_id`, `score`)
VALUES (1, 100)
ON DUPLICATE KEY UPDATE `score` = `score` + 100;

1.4 替换（REPLACE INTO）

主键或唯一键冲突时先删再插——比 ON DUPLICATE KEY UPDATE 更暴力，自增 ID 会变：

REPLACE INTO `config` (`key`, `value`) VALUES ('site_name', 'My Site');

WARNING

REPLACE INTO 会删除旧行，触发外键级联删除、自增 ID 变化。一般不推荐，业务逻辑里多用 ON DUPLICATE KEY UPDATE。

1.5 返回自增 ID

INSERT INTO `student` (`stuno`, `name`, `sex`) VALUES ('2024001', '测试', b'1');
-- MySQL 会话变量 LAST_INSERT_ID() 返回刚插入的 id
SELECT LAST_INSERT_ID();

Node 端 mysql2 驱动的 INSERT 结果默认带 insertId 字段。

二、UPDATE 更新

2.1 基础更新

UPDATE `student`
SET `phone` = '13800138000'
WHERE `id` = 1;

2.2 多字段更新

UPDATE `student`
SET `phone` = '13800138000', `name` = '张三丰'
WHERE `id` = 1;

2.3 表达式更新

UPDATE `account`
SET `balance` = `balance` - 100
WHERE `id` = 1 AND `balance` >= 100;

上面的例子同时保证扣款（balance 减 100）和乐观锁（balance >= 100 校验余额足够）——受影响的行数为 0 表示扣款失败。

2.4 ⚠️ 没有 WHERE 的 UPDATE = 灾难

-- ❌ 极危险：更新整张表
UPDATE `student` SET `phone` = NULL;

DANGER

生产事故 90% 来自"忘了写 WHERE"。MySQL 默认开启了 safe-updates 模式（带 LIMIT 才允许执行），但生产服务器常关闭。安全做法：

• 写 SQL 前先 SELECT ... WHERE ... 看命中行数
• 在事务里先查询再更新，便于回滚
• 应用层封装更新方法，强制传 WHERE
• 生产环境用 DBA 审核

三、DELETE 删除

3.1 按条件删除

DELETE FROM `student` WHERE `id` = 1;

3.2 ⚠️ 没有 WHERE 的 DELETE = 全表清空

-- ❌ 比 UPDATE 更危险：删除所有行
DELETE FROM `student`;

DANGER

同 UPDATE：DELETE 永远带 WHERE。MySQL 8.0 默认开启 sql_safe_updates，开启时没带 WHERE 的 UPDATE / DELETE 会直接报错。生产建议开启。

3.3 DELETE vs TRUNCATE

维度	DELETE	TRUNCATE
类型	DML	DDL
能否回滚	✅ 事务回滚	❌ 不能回滚
自增 ID	保留	重置
触发器	✅ 触发	❌ 不触发
性能	逐行删除，慢	一次性释放数据页，快
WHERE	支持	不支持（直接清空）

-- ✅ 软删除：保留数据，只标记
UPDATE `student` SET `deleted_at` = NOW() WHERE `id` = 1;

-- ✅ 真删除：单条
DELETE FROM `student` WHERE `id` = 1;

-- ⚠️ TRUNCATE：极少用，仅用于测试环境快速清表
TRUNCATE TABLE `student`;

TIP

生产项目几乎都用软删除（加 deleted_at 字段），保留数据用于审计和恢复。

四、事务控制

DML 操作涉及多个步骤时，必须用事务保证原子性：

START TRANSACTION;

UPDATE `account` SET `balance` = `balance` - 100 WHERE `id` = 1;
UPDATE `account` SET `balance` = `balance` + 100 WHERE `id` = 2;

-- 没问题就提交
COMMIT;

-- 出问题就回滚（搭配 IF 防止提交后误回滚）
ROLLBACK;

ACID 四大特性：

特性	含义
Atomicity 原子性	要么全成功，要么全失败
Consistency 一致性	事务前后数据满足所有约束
Isolation 隔离性	并发事务之间互不干扰
Durability 持久性	事务一旦提交，数据永久保存

五、Node 端 CRUD 实战

以 mysql2 驱动为例：

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'your-password',
  database: 'myapp',
});

// 新增
const [insertResult] = await pool.execute(
  'INSERT INTO student (stuno, name) VALUES (?, ?)',
  ['2024005', '钱七']
);
console.log('新插入 id:', insertResult.insertId);

// 更新（必带 WHERE）
const [updateResult] = await pool.execute(
  'UPDATE student SET phone = ? WHERE id = ?',
  ['13900139000', 1]
);
console.log('受影响行数:', updateResult.affectedRows);

// 删除（必带 WHERE）
const [deleteResult] = await pool.execute(
  'DELETE FROM student WHERE id = ?',
  [1]
);
console.log('删除行数:', deleteResult.affectedRows);

// 事务
const conn = await pool.getConnection();
try {
  await conn.beginTransaction();
  await conn.execute('UPDATE account SET balance = balance - ? WHERE id = ?', [100, 1]);
  await conn.execute('UPDATE account SET balance = balance + ? WHERE id = ?', [100, 2]);
  await conn.commit();
} catch (e) {
  await conn.rollback();
  throw e;
} finally {
  conn.release();
}

WARNING

必须用参数化查询（? 占位符），不要拼接字符串——后者会引发 SQL 注入。

六、最佳实践

场景	推荐
批量写入	INSERT INTO ... VALUES (...), (...), (...) 一次性多条
幂等写入	INSERT ... ON DUPLICATE KEY UPDATE
软删除	UPDATE ... SET deleted_at = NOW()
真删除	永远带 WHERE；生产开启 sql_safe_updates
更新前	先 SELECT 看命中行数
多步操作	显式 beginTransaction / commit / rollback
Node 端	mysql2 驱动 + execute(sql, [params]) 参数化
SQL 注入	永远用占位符，不拼接用户输入

七、小结

• INSERT 单条 / 批量 / ON DUPLICATE KEY UPDATE（幂等）
• UPDATE 永远带 WHERE；不带 WHERE 是 90% 线上事故的根因
• DELETE 同样带 WHERE；生产几乎都用软删除（deleted_at）
• TRUNCATE 不能回滚、不触发触发器，仅测试环境使用
• 多步操作必须用事务（START TRANSACTION / COMMIT / ROLLBACK）
• Node 端用 mysql2 的 execute(sql, [params]) 参数化查询