如何从Node.js MySQL连接函数中返回数据？附代码示例

Hey there! Let's get your executeQuery function returning data properly. First, a quick heads-up on your current code: calling connection.connect() every time you run a query is inefficient (it creates a new connection each time), and since database operations are asynchronous, you can't just return results directly from a callback-based function.

Here are two reliable, production-ready solutions:

Solution 1: Wrap the Query in a Promise (Recommended) #

Promises let you handle asynchronous operations cleanly, making it easy to retrieve results with .then() or async/await. We'll also fix the connection issue by reusing an existing connection instead of creating a new one each time.

Full Updated Code #

var mysql = require('mysql'); 
var config = require('./../Config.js'); 

// Create a single connection (reuse this, don't recreate on every query)
var connection = mysql.createConnection({ 
  host: config.databaseConnection.host , 
  user: config.databaseConnection.user, 
  password: config.databaseConnection.password, 
  database: config.databaseConnection.database 
});

// Promise-wrapped executeQuery function
var executeQuery = function(querystring) {
  return new Promise((resolve, reject) => {
    // Check if we're already connected; only connect if disconnected
    if (connection.state === 'disconnected') {
      connection.connect(err => {
        if (err) {
          console.error('Failed to connect to database:', err);
          return reject(err);
        }
        console.log("Connected to database successfully");
        runQuery();
      });
    } else {
      runQuery();
    }

    // Internal function to run the actual query
    function runQuery() {
      connection.query(querystring, (err, rows) => {
        if (err) {
          console.error('Query failed to execute:', err);
          return reject(err);
        }
        // Resolve with the query results
        resolve(rows);
      });
    }
  });
};

// Example usage
executeQuery('SELECT * FROM your_table_name')
  .then(results => {
    console.log('Query results:', results);
    // Use your results here
  })
  .catch(error => {
    // Handle any connection/query errors here
    console.error('Error fetching data:', error);
  });

Solution 2: Use Async/Await (Promise Syntax Sugar) #

If you're running Node.js 8+, you can use async/await to write asynchronous code that looks synchronous. This builds directly on the Promise-based function above:

Example Usage #

// Async function to fetch data
async function fetchTableData() {
  try {
    const results = await executeQuery('SELECT * FROM your_table_name');
    console.log('Fetched data:', results);
    return results; // Return data to the caller
  } catch (error) {
    console.error('Failed to fetch data:', error);
    throw error; // Re-throw error if you want the caller to handle it
  }
}

// Call the async function
fetchTableData();

Bonus: Use a Connection Pool for Production #

For production applications, a connection pool is far more efficient than a single connection—it manages a pool of reusable connections, avoiding the overhead of creating/destroying connections on every query:

var mysql = require('mysql'); 
var config = require('./../Config.js'); 

// Create a connection pool
const pool = mysql.createPool({
  host: config.databaseConnection.host,
  user: config.databaseConnection.user,
  password: config.databaseConnection.password,
  database: config.databaseConnection.database,
  connectionLimit: 10 // Adjust based on your app's needs
});

// Pool-based executeQuery function
var executeQuery = function(querystring) {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        return reject(err);
      }
      connection.query(querystring, (err, rows) => {
        connection.release(); // Release the connection back to the pool
        if (err) {
          return reject(err);
        }
        resolve(rows);
      });
    });
  });
};

Key Takeaways #

• Asynchronous operations like database queries can't return results directly—use Promises (or async/await) to pass results around.
• Never call connection.connect() on every query; reuse connections or use a pool.
• Always handle errors to prevent uncaught exceptions from crashing your app.

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