Node系列 · 数据库DML 增删改DMLData Manipulation Language是日常开发最高频的 SQL增、删、改、查。其中查是 DQL 单列的本章只讲增、删、改三件——它们看似简单错误用法却是线上事故的最大来源。一、INSERT 新增1.1 单条插入INSERTINTOstudent(stuno,name,sex)VALUES(2024001,张三,b1);1.2 批量插入INSERTINTOstudent(stuno,name,sex)VALUES(2024002,李四,b1),(2024003,王五,b0),(2024004,赵六,b1);批量插入比循环单条插入快10-100 倍——只发一次网络往返、数据库一次解析。1.3 插入或更新ON DUPLICATE KEY UPDATE如果主键或唯一键已存在就更新否则插入。常用于幂等写入INSERTINTOuser_score(user_id,score)VALUES(1,100)ONDUPLICATEKEYUPDATEscorescore100;1.4 替换REPLACE INTO主键或唯一键冲突时先删再插——比ON DUPLICATE KEY UPDATE更暴力自增 ID 会变REPLACEINTOconfig(key,value)VALUES(site_name,My Site);::: warningREPLACE INTO会删除旧行触发外键级联删除、自增 ID 变化。一般不推荐业务逻辑里多用ON DUPLICATE KEY UPDATE。:::1.5 返回自增 IDINSERTINTOstudent(stuno,name,sex)VALUES(2024001,测试,b1);-- MySQL 会话变量 LAST_INSERT_ID() 返回刚插入的 idSELECTLAST_INSERT_ID();Node 端mysql2驱动的INSERT结果默认带insertId字段。二、UPDATE 更新2.1 基础更新UPDATEstudentSETphone13800138000WHEREid1;2.2 多字段更新UPDATEstudentSETphone13800138000,name张三丰WHEREid1;2.3 表达式更新UPDATEaccountSETbalancebalance-100WHEREid1ANDbalance100;上面的例子同时保证扣款balance减 100和乐观锁balance 100校验余额足够——受影响的行数为 0 表示扣款失败。2.4 ⚠️ 没有 WHERE 的 UPDATE 灾难-- ❌ 极危险更新整张表UPDATEstudentSETphoneNULL;::: danger生产事故 90% 来自忘了写 WHERE。MySQL 默认开启了safe-updates模式带 LIMIT 才允许执行但生产服务器常关闭。安全做法写 SQL 前先SELECT ... WHERE ...看命中行数在事务里先查询再更新便于回滚应用层封装更新方法强制传 WHERE生产环境用 DBA 审核:::三、DELETE 删除3.1 按条件删除DELETEFROMstudentWHEREid1;3.2 ⚠️ 没有 WHERE 的 DELETE 全表清空-- ❌ 比 UPDATE 更危险删除所有行DELETEFROMstudent;::: danger同 UPDATEDELETE永远带WHERE。MySQL 8.0 默认开启sql_safe_updates开启时没带WHERE的UPDATE/DELETE会直接报错。生产建议开启。:::3.3 DELETE vs TRUNCATE维度DELETETRUNCATE类型DMLDDL能否回滚✅ 事务回滚❌ 不能回滚自增 ID保留重置触发器✅ 触发❌ 不触发性能逐行删除慢一次性释放数据页快WHERE支持不支持直接清空-- ✅ 软删除保留数据只标记UPDATEstudentSETdeleted_atNOW()WHEREid1;-- ✅ 真删除单条DELETEFROMstudentWHEREid1;-- ⚠️ TRUNCATE极少用仅用于测试环境快速清表TRUNCATETABLEstudent;::: tip生产项目几乎都用软删除加deleted_at字段保留数据用于审计和恢复。:::四、事务控制DML 操作涉及多个步骤时必须用事务保证原子性STARTTRANSACTION;UPDATEaccountSETbalancebalance-100WHEREid1;UPDATEaccountSETbalancebalance100WHEREid2;-- 没问题就提交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/rollbackNode 端mysql2驱动 execute(sql, [params])参数化SQL 注入永远用占位符不拼接用户输入七、小结INSERT单条 / 批量 /ON DUPLICATE KEY UPDATE幂等UPDATE永远带WHERE不带 WHERE 是 90% 线上事故的根因DELETE同样带WHERE生产几乎都用软删除deleted_atTRUNCATE不能回滚、不触发触发器仅测试环境使用多步操作必须用事务START TRANSACTION/COMMIT/ROLLBACKNode 端用mysql2的execute(sql, [params])参数化查询 Node系列 · 数据库：DML 增删改
Node系列 · 数据库DML 增删改DMLData Manipulation Language是日常开发最高频的 SQL增、删、改、查。其中查是 DQL 单列的本章只讲增、删、改三件——它们看似简单错误用法却是线上事故的最大来源。一、INSERT 新增1.1 单条插入INSERTINTOstudent(stuno,name,sex)VALUES(2024001,张三,b1);1.2 批量插入INSERTINTOstudent(stuno,name,sex)VALUES(2024002,李四,b1),(2024003,王五,b0),(2024004,赵六,b1);批量插入比循环单条插入快10-100 倍——只发一次网络往返、数据库一次解析。1.3 插入或更新ON DUPLICATE KEY UPDATE如果主键或唯一键已存在就更新否则插入。常用于幂等写入INSERTINTOuser_score(user_id,score)VALUES(1,100)ONDUPLICATEKEYUPDATEscorescore100;1.4 替换REPLACE INTO主键或唯一键冲突时先删再插——比ON DUPLICATE KEY UPDATE更暴力自增 ID 会变REPLACEINTOconfig(key,value)VALUES(site_name,My Site);::: warningREPLACE INTO会删除旧行触发外键级联删除、自增 ID 变化。一般不推荐业务逻辑里多用ON DUPLICATE KEY UPDATE。:::1.5 返回自增 IDINSERTINTOstudent(stuno,name,sex)VALUES(2024001,测试,b1);-- MySQL 会话变量 LAST_INSERT_ID() 返回刚插入的 idSELECTLAST_INSERT_ID();Node 端mysql2驱动的INSERT结果默认带insertId字段。二、UPDATE 更新2.1 基础更新UPDATEstudentSETphone13800138000WHEREid1;2.2 多字段更新UPDATEstudentSETphone13800138000,name张三丰WHEREid1;2.3 表达式更新UPDATEaccountSETbalancebalance-100WHEREid1ANDbalance100;上面的例子同时保证扣款balance减 100和乐观锁balance 100校验余额足够——受影响的行数为 0 表示扣款失败。2.4 ⚠️ 没有 WHERE 的 UPDATE 灾难-- ❌ 极危险更新整张表UPDATEstudentSETphoneNULL;::: danger生产事故 90% 来自忘了写 WHERE。MySQL 默认开启了safe-updates模式带 LIMIT 才允许执行但生产服务器常关闭。安全做法写 SQL 前先SELECT ... WHERE ...看命中行数在事务里先查询再更新便于回滚应用层封装更新方法强制传 WHERE生产环境用 DBA 审核:::三、DELETE 删除3.1 按条件删除DELETEFROMstudentWHEREid1;3.2 ⚠️ 没有 WHERE 的 DELETE 全表清空-- ❌ 比 UPDATE 更危险删除所有行DELETEFROMstudent;::: danger同 UPDATEDELETE永远带WHERE。MySQL 8.0 默认开启sql_safe_updates开启时没带WHERE的UPDATE/DELETE会直接报错。生产建议开启。:::3.3 DELETE vs TRUNCATE维度DELETETRUNCATE类型DMLDDL能否回滚✅ 事务回滚❌ 不能回滚自增 ID保留重置触发器✅ 触发❌ 不触发性能逐行删除慢一次性释放数据页快WHERE支持不支持直接清空-- ✅ 软删除保留数据只标记UPDATEstudentSETdeleted_atNOW()WHEREid1;-- ✅ 真删除单条DELETEFROMstudentWHEREid1;-- ⚠️ TRUNCATE极少用仅用于测试环境快速清表TRUNCATETABLEstudent;::: tip生产项目几乎都用软删除加deleted_at字段保留数据用于审计和恢复。:::四、事务控制DML 操作涉及多个步骤时必须用事务保证原子性STARTTRANSACTION;UPDATEaccountSETbalancebalance-100WHEREid1;UPDATEaccountSETbalancebalance100WHEREid2;-- 没问题就提交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/rollbackNode 端mysql2驱动 execute(sql, [params])参数化SQL 注入永远用占位符不拼接用户输入七、小结INSERT单条 / 批量 /ON DUPLICATE KEY UPDATE幂等UPDATE永远带WHERE不带 WHERE 是 90% 线上事故的根因DELETE同样带WHERE生产几乎都用软删除deleted_atTRUNCATE不能回滚、不触发触发器仅测试环境使用多步操作必须用事务START TRANSACTION/COMMIT/ROLLBACKNode 端用mysql2的execute(sql, [params])参数化查询