Node.js数据库应用代码同步执行问题：用户城市API调试求助

Hey there! I see exactly what's going on here. Your code is behaving unexpectedly because MySQL queries in Node.js are asynchronous—the connection.query() calls don't block execution, so your count check and response get sent before all the insert queries finish running. That's why you see the executed log before the count increments to 2.

Let's break down the core issues in your current code first:

• The for loop fires off all insert queries immediately, but they run in the background without pausing the main code flow.
• The if(count == cities.length) check runs right after the loop, before any inserts have completed (so count is still 0 at that point).
• You're releasing the database connection before the inserts finish, which can cause unexpected behavior or dropped queries.

Solution 1: Use Async/Await for Clean, Synchronous-Like Flow #

The most intuitive fix is to use async/await, which lets you write asynchronous code that reads like synchronous code. I'd recommend switching to mysql2/promise (a drop-in replacement for the standard mysql library that supports promises out of the box).

Here's the refactored code:

// First install mysql2: npm install mysql2
const mysql = require('mysql2/promise');
const pool = mysql.createPool(/* your existing pool configuration */);

router.post("/users_city_new", async function(req, res) {
  const cities = req.body.city_id;
  const userId = req.body.user_id;

  // Validate API key first
  if(req.headers.authorization !== secret) {
    return res.json({"Error" : true, "Message" : "API Key is Missing or is Invalid"});
  }

  let connection;
  try {
    // Grab a connection from the pool
    connection = await pool.getConnection();

    // Delete existing city entries for the user first
    await connection.query("DELETE FROM users_city WHERE user_id = ?", [userId]);
    console.log("Successfully cleared existing user cities");

    // Insert all new cities in one bulk query (way more efficient!)
    if(cities.length > 0) {
      const insertValues = cities.map(city => [city.city, userId]);
      await connection.query("INSERT INTO users_city (city_id, user_id) VALUES ?", [insertValues]);
      console.log(`Successfully inserted ${cities.length} cities`);
    }

    // Send success response once all operations are done
    res.json({"Error" : false, "Message": "Inserted Successfully"});
  } catch(err) {
    // Catch and handle any errors that occur
    console.error(err);
    res.json({"Error" : true, "Message": "Error Inserting cities", "Details": err.message});
  } finally {
    // Always release the connection, even if an error happens
    if(connection) connection.release();
  }
});

Solution 2: Callback-Based Approach (If You Prefer to Keep Existing Library) #

If you don't want to switch to promises, you can use a counter to track when all insert queries finish executing:

router.post("/users_city_new", function(req, res) {
  const cities = req.body.city_id;
  const userId = req.body.user_id;
  let completedInserts = 0;
  let hasError = false;

  // Validate API key first
  if(req.headers.authorization !== secret) {
    return res.json({"Error" : true, "Message" : "API Key is Missing or is Invalid"});
  }

  pool.getConnection(function(err, connection) {
    if(err) {
      console.error(err);
      return res.json({"Error" : true, "Message": "Failed to connect to database"});
    }

    // Delete existing entries first
    connection.query("DELETE FROM users_city WHERE user_id = ?", [userId], function(err) {
      if(err) {
        console.error(err);
        connection.release();
        return res.json({"Error" : true, "Message": "Error clearing existing cities"});
      }
      console.log("Successfully cleared existing user cities");

      // Handle empty city list case
      if(cities.length === 0) {
        connection.release();
        return res.json({"Error" : false, "Message": "No cities to insert"});
      }

      // Insert each city and track completion
      cities.forEach(function(cityObj) {
        const city = cityObj.city;
        connection.query("INSERT INTO users_city (city_id, user_id) VALUES (?, ?)", [city, userId], function(err) {
          completedInserts++;
          if(err) {
            console.error(err);
            hasError = true;
          }

          // Check if all inserts are done
          if(completedInserts === cities.length) {
            connection.release();
            if(hasError) {
              res.json({"Error" : true, "Message": "Error Inserting some cities"});
            } else {
              res.json({"Error" : false, "Message": "Inserted Successfully"});
            }
          }
        });
      });
    });
  });
});

Key Improvements in Both Solutions: #

• Bulk Insert Efficiency: The first solution uses a single bulk insert query instead of multiple individual inserts, which reduces database load and speeds up execution.
• Proper Error Handling: Both solutions catch errors at every step and ensure the database connection is always released, even if something goes wrong.
• Guaranteed Synchronous Flow: Async/await or callback counting ensures we only send the response after all database operations are fully completed.

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