Node.js中从MySQL查询并输出指定二维数组格式数据的方法

Got it, let's wrap up this route functionality for you! Here's the complete, working code with proper error handling and the exact formatting you need, plus a breakdown of how it works:

app.get('/charts/sample1', function(req, res) {
  // Your SQL query (double-check "table1" is the correct table name!)
  const query = "SELECT col1, col2, col3, col4, col5 FROM table1";

  con.query(query, function(error, results, fields) {
    // Handle database query errors first—don't leave the frontend hanging!
    if (error) {
      console.error('Database query failed:', error);
      return res.status(500).json({ error: 'Could not retrieve data from database' });
    }

    // Transform MySQL's object-based results into your desired 2D array
    const formattedData = results.map(row => {
      // Convert col1 to a numeric timestamp if it's stored as a datetime string
      // Skip this line if col1 is already a numeric timestamp in your DB
      const timestamp = typeof row.col1 === 'string' ? new Date(row.col1).getTime() : row.col1;
      
      return [
        timestamp,
        row.col2,
        row.col3,
        row.col4,
        row.col5
      ];
    });

    // Send the formatted 2D array straight to the frontend as JSON
    res.json(formattedData);
  });
});

Key Details to Note: #

• Error Handling: We prioritize catching database errors (like typos in table/column names, connection drops) and send a clear 500 error response to the frontend—this makes debugging way easier than silent failures.
• Data Transformation: MySQL returns results as an array of objects (each row looks like { col1: value1, col2: value2, ... }). We use Array.map() to convert each object into a flat array that matches your required structure.
  • If your col1 is stored as a DATETIME/TIMESTAMP string in MySQL, the code converts it to a numeric timestamp (matching your example 1262889000000). If col1 is already a numeric value in the database, you can remove the timestamp conversion line and just use row.col1 directly.
• Response: Using res.json() sends the formatted array as a ready-to-use JSON object, so your frontend won't need extra parsing to work with the data.

Quick Pre-Flight Checks: #

• Make sure your con variable is a valid, active MySQL connection (either a single connection or from a connection pool via the mysql npm package).
• Verify that col1 through col5 are the exact column names in your table1 table—small typos here will break the query!

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