Node.js中如何将SQL查询结果存入数组并写入JSON文件？

Hey there! No worries at all—Node.js can feel a bit overwhelming when you're just starting out, so let's break this down step by step to get your SQL results saved into a JSON file.

核心思路 #

Since your viewMontadora function already successfully fetches all manufacturer data from SQL, we just need to:

1. Capture that query result (which is already an array, thanks to most Node.js database drivers)
2. Use Node.js built-in fs (file system) module to write this array to a JSON file

具体实现步骤 #

First, let's start with the basics—make sure you have the necessary modules imported. We'll need your database driver (I'll use mysql as an example) and the fs module:

const mysql = require('mysql');
const fs = require('fs'); // 引入文件系统模块

1. 声明查询语句的位置

Your SQL query should live inside or right before the database call in your viewMontadora function. If you plan to reuse the query, you can declare it as a constant outside the function, but for simplicity, let's put it directly inside:

2. 修改viewMontadora函数

Here's how to adjust your function to capture results, store them in an array, and write to JSON:

// 先配置数据库连接（如果还没的话）
const db = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database_name'
});

// 连接数据库
db.connect(err => {
  if (err) throw err;
  console.log('Connected to database!');
});

function viewMontadora() {
  // 在这里声明你的SQL查询语句
  const sqlQuery = 'SELECT * FROM montadoras'; // 替换成你的实际表名

  // 执行查询
  db.query(sqlQuery, (err, results) => {
    if (err) {
      console.error('查询出错:', err);
      return;
    }

    // ✨ results就是查询返回的数组！不需要额外创建空数组再push数据
    const manufacturersArray = results;

    // 将数组转为格式化的JSON字符串并写入文件
    fs.writeFile('manufacturers.json', JSON.stringify(manufacturersArray, null, 2), writeErr => {
      if (writeErr) {
        console.error('写入文件出错:', writeErr);
        return;
      }
      console.log('数据已成功写入manufacturers.json！');
    });

    // 如果你原来需要把结果返回给前端/其他逻辑，继续保留即可
    console.log('所有汽车制造商信息:', manufacturersArray);
  });
}

// 调用函数执行逻辑
viewMontadora();

如果你用Async/Await（更现代的写法） #

If you're using a promise-based database driver like mysql2/promise, here's a cleaner async/await version:

const mysql = require('mysql2/promise');
const fs = require('fs').promises; // 使用promise版本的fs模块

async function viewMontadora() {
  try {
    // 建立数据库连接
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'your_username',
      password: 'your_password',
      database: 'your_database_name'
    });

    // 声明查询语句
    const sqlQuery = 'SELECT * FROM montadoras';
    // 执行查询并解构获取结果数组
    const [results] = await connection.query(sqlQuery);

    // 写入JSON文件
    await fs.writeFile('manufacturers.json', JSON.stringify(results, null, 2));
    console.log('文件写入完成！');

    // 关闭连接
    await connection.end();
  } catch (err) {
    console.error('出错:', err);
  }
}

// 调用异步函数
viewMontadora();

关键注意事项 #

• 安装依赖: Make sure you've installed your database driver with npm install mysql or npm install mysql2.
• 文件路径: The JSON file will be saved in the same directory where you run your Node.js script. If you want to save it to a specific folder (like ./data/), create the folder first or add logic to check for it using fs.mkdir.
• 错误处理: Always handle errors (like failed database connections or file writes) to avoid crashes.

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