Node.js无法连接数据库：使用JavaScript/jQuery连接查询数据库求助

Hey there, let's work through the problems you're hitting with your MySQL connection and the async function setup step by step.

First: Diagnose the Connection Failure #

If you can't connect to the database, start with these checks—they'll rule out most common configuration issues:

• Is MySQL running locally? Make sure the MySQL service is active on your machine, and that it's listening on port 3306 (the default). You can verify this with commands like netstat -ano | findstr :3306 (Windows) or lsof -i :3306 (macOS/Linux).
• Does your user have the right permissions? MySQL users are tied to specific hosts. If your user was created as myUser@localhost, it can only connect from the local machine—double-check with a MySQL client (like Workbench) using the same credentials to confirm you can access the mydb database.
• Are credentials and database name correct? Typos happen! Verify that myUser, myPassword, and mydb exactly match what's set up in your MySQL instance.
• Is the firewall blocking port 3306? On Windows, check Windows Firewall settings; on macOS/Linux, ensure your firewall isn't blocking incoming/outgoing traffic on port 3306.

Second: Fix the Async Logic Flaw #

Your GetSqlResult function has a critical issue with asynchronous code: the return result inside the con.query callback won't actually return anything to the caller. Functions like connect and query run asynchronously, meaning your function will exit before the callback finishes executing.

Here's how to fix this using Promises and async/await (the most readable approach for async code in Node.js):

Option 1: Using a Single Connection (with Proper Async Handling) #

const mysql = require('mysql');

// Create your connection
const con = mysql.createConnection({
  host: "localhost",
  user: "myUser",
  password: "myPassword",
  database: "mydb"
});

// Wrap the query in a Promise to handle async logic
function GetSqlResult(sql_query) {
  return new Promise((resolve, reject) => {
    con.connect(err => {
      if (err) {
        console.error('Failed to connect:', err);
        return reject(err); // Reject the Promise on connection error
      }

      con.query(sql_query, (err, result, fields) => {
        // Close the connection after the query completes
        con.end();
        
        if (err) {
          console.error('Query failed:', err);
          return reject(err); // Reject the Promise on query error
        }
        
        resolve(result); // Resolve with the query result
      });
    });
  });
}

// Use the function with async/await
async function runQuery() {
  try {
    const data = await GetSqlResult('SELECT * FROM customers');
    console.log('Query result:', data);
  } catch (error) {
    console.error('Error:', error);
  }
}

runQuery();

Option 2: Use a Connection Pool (Better for Production) #

Creating a new connection every time you run a query is inefficient. Instead, use MySQL's connection pool to reuse connections:

const mysql = require('mysql');

// Create a connection pool
const pool = mysql.createPool({
  connectionLimit: 10, // Max number of connections in the pool
  host: "localhost",
  user: "myUser",
  password: "myPassword",
  database: "mydb"
});

// Pool handles connection management automatically
function GetSqlResult(sql_query) {
  return new Promise((resolve, reject) => {
    pool.query(sql_query, (err, result, fields) => {
      if (err) return reject(err);
      resolve(result);
    });
  });
}

// Usage remains the same
async function runQuery() {
  try {
    const data = await GetSqlResult('SELECT * FROM customers');
    console.log('Query result:', data);
  } catch (error) {
    console.error('Error:', error);
  }
}

runQuery();

Pro Tip: Always Check Error Details #

Instead of just throw err, log the full error object or err.message—this will give you specific clues about what's wrong (e.g., "Access denied for user" vs. "Can't connect to MySQL server").

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