Node.js mysql模块中MySQL自定义变量能否在事务中使用？

Great question! Let's break this down step by step:

First off, the core answer: Yes, MySQL user-defined variables (like @user_id) work perfectly in transactions with the Node.js mysql module. Variables persist for the entire session of a single connection, and since transactions run on one dedicated connection by default, the variable will stay available across all queries in your transaction.

Fixes for Your Original Code #

Your sample code has a couple of small syntax/typo bugs that would block it from running even with working variables:

1. The first INSERT has invalid syntax: INSERT INTO user SET = ? should be INSERT INTO user SET ?
2. In rollback handlers, you’re throwing error instead of the actual error variable err

Here’s the corrected callback-based version:

connection.beginTransaction(err => {
  if (err) { throw err; }
  
  connection.query('INSERT INTO user SET ?', {id: 12, username: 'name'}, (err, results) => {
    if (err) {
      return connection.rollback(() => { throw err; });
    }
    
    connection.query('SELECT @user_id:=userID FROM user WHERE username = ?', ['name'], (err, results) => {
      if (err) {
        return connection.rollback(() => { throw err; });
      }
      
      connection.query('INSERT INTO authentication SET userID = @user_id, ?', {password: 'userpassword'}, (err, results) => {
        if (err) {
          return connection.rollback(() => { throw err; });
        }
        
        connection.commit(err => {
          if (err) {
            return connection.rollback(() => { throw err; });
          }
          console.log('success!');
        });
      });
    });
  });
});

Cleaner Promise-Based Implementation #

Callback nesting gets messy fast, especially with transactions. If you’re building a utility function, using Promises (either via util.promisify or the promise-native mysql2/promise module) will make your code far more maintainable.

Here’s how it looks with the original mysql module promisified:

const util = require('util');
// Promisify connection methods
const query = util.promisify(connection.query).bind(connection);
const beginTransaction = util.promisify(connection.beginTransaction).bind(connection);
const commit = util.promisify(connection.commit).bind(connection);
const rollback = util.promisify(connection.rollback).bind(connection);

async function runUserAuthTransaction() {
  try {
    await beginTransaction();
    
    // Insert new user
    await query('INSERT INTO user SET ?', {id: 12, username: 'name'});
    
    // Set the user_id variable
    await query('SELECT @user_id:=userID FROM user WHERE username = ?', ['name']);
    
    // Link authentication to the user via the variable
    await query('INSERT INTO authentication SET userID = @user_id, ?', {password: 'userpassword'});
    
    await commit();
    console.log('success!');
  } catch (err) {
    await rollback();
    throw err;
  }
}

runUserAuthTransaction().catch(err => console.error('Transaction failed:', err));

For Your Utility Function Scenario #

Since your utility accepts queries as parameters, using MySQL variables is still a great fit—you can pass query strings that reference the variables, and as long as they’re executed in order on the same connection, the variables will be available.

If you want to avoid variables entirely, you could use the insertId property from the first INSERT result (auto-increment columns return this value) and pass it to the third query. But using variables keeps your query strings self-contained, which is ideal if your utility abstracts away intermediate result handling.

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