Node.js操作MySQL执行多条查询如何保障无错误、数据一致

你当前的写法依赖MySQL默认的自动提交机制，单条SQL执行完成就会立即持久化变更，一旦中间某条语句报错，前面已经执行完成的操作不会自动撤销，必然产生数据不一致。要彻底解决这个问题，必须使用数据库事务，事务的原子性可以保证整组SQL要么全部执行成功持久化，要么全部执行失败回滚到初始状态，不会出现部分生效的情况。

前置准备 #

• 确认你操作的表使用InnoDB存储引擎，MyISAM引擎不支持事务，目前MySQL 5.5之后的版本默认引擎都是InnoDB，老版本表可以手动修改引擎。
• 给MySQL连接开启多语句执行权限，否则Node.js mysql驱动会拦截多语句请求，配置示例：

const mysqlConnection = mysql.createConnection({
  host: '你的数据库地址',
  user: '数据库用户名',
  password: '数据库密码',
  database: '库名',
  multipleStatements: true // 必须开启该配置才能一次执行多条SQL
})

可靠实现代码 #

按照事务的标准流程手动控制执行、提交、回滚逻辑，不要直接把整组SQL传入普通query方法：

// 1. 开启事务
mysqlConnection.beginTransaction(transactionErr => {
  if (transactionErr) {
    return res.status(500).send({ message: transactionErr.sqlMessage })
  }

  // 定义要执行的SQL组，用?做参数占位，不要直接拼接值到SQL字符串里，避免SQL注入
  const execSql = `
    INSERT IGNORE INTO ???(??,??) values(?,?);
    UPDATE ??? SET ??? = true WHERE id = ?;
    INSERT INTO ???? (title,body,individual_id) values(?,?,?);
    INSERT INTO ????(title,body,worker_id) values(?,?,?);
    SELECT ????, (SELECT ??? FROM ??? WHERE id LIKE ?) as ??? FROM ??? WHERE id = ?;
  `
  // 按照SQL中占位符的顺序，把实际的表名、字段名、参数值放到数组中传入
  const sqlParams = [/* 按顺序填入你的实际参数 */]

  mysqlConnection.query(execSql, sqlParams, (queryErr, results) => {
    if (queryErr) {
      // 任意一条SQL执行报错，直接回滚所有已执行的变更，返回错误
      return mysqlConnection.rollback(() => {
        res.status(500).send({ message: queryErr.sqlMessage })
      })
    }

    // 所有SQL执行无报错，提交事务持久化变更
    mysqlConnection.commit(commitErr => {
      if (commitErr) {
        // 提交过程出错同样执行回滚
        return mysqlConnection.rollback(() => {
          res.status(500).send({ message: commitErr.sqlMessage })
        })
      }
      // 事务提交成功后再返回成功响应
      // 多语句执行时results是数组，索引对应SQL顺序，最后一条SELECT的结果为results[4]
      res.send({
        message: "success",
        queryResult: results[4]
      })
    })
  })
})

注意事项 #

• 事务执行过程会占用数据库连接、持有对应数据的锁，不要在事务中加入耗时过长的逻辑或慢SQL，避免影响其他数据库请求的正常执行。
• 事务只能回滚INSERT、UPDATE、DELETE这类数据操作语句，无法回滚建表、修改表结构等DDL语句，事务块内不要放DDL操作。
• 如果你后续使用mysql2的promise版本、或是Sequelize、TypeORM这类ORM框架，也都封装了对应的事务方法，核心逻辑和上述实现一致，都是「开启事务→执行逻辑→失败回滚/成功提交」的流程。
• 禁止直接把用户传入的参数拼接进SQL字符串，必须通过参数化方式传入，避免SQL注入漏洞。

内容的提问来源于stack exchange，提问作者user13432717