Node.js执行MySQL初始化文件时出现SQL语法错误求助

我有用于搭建数据库的schema.sql和填充初始数据的seed.sql文件，这两个文件在MySQL Workbench中运行完全正常，但用自定义的SQL封装类JoeSQL的reset方法执行时，却触发了SQL语法错误。

相关代码 #

// I'd name this SQLHelper but it'd be too long. 
// I'd name it MySQL but... well, you know. 
import mysql2 from "mysql2";
import fs from "fs";

import SECRETS from "../secrets.js";

/* == MISC == */
const SQL_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: SECRETS.SQL,
    database: 'employee_tracker'
}

/* == JOESQL HELPER CLASS == */
class JoeSQL {
    /* == CONSTRUCTOR == */
    constructor() {
        this.connection = null;
        return this;
    }

    /* == SUGAR FUNCTIONS == */
    // Connect to the DB
    connect() {
        return this.connection = mysql2.createConnection(SQL_CONFIG);
    }

    // Close the DB connection
    end() {
        this.connection.end();
    }

    /* == MAIN FUNCTIONS == */
    // Many removed for simplicity

    // FIXME
    reset() {
        const schema = fs.readFileSync("db/schema.sql", {encoding: "utf-8"});
        const seed = fs.readFileSync("db/seed.sql", {encoding: "utf-8"});
        
        this.connect();
        
        this.connection.query(schema);
        this.connection.query(seed);

        this.end();
    }
}

export default JoeSQL;

预期与实际行为 #

• 预期：加载两个SQL文件，连接数据库，执行查询并完成数据库重置
• 实际：文件能被读取为字符串发送给SQL服务器，但触发语法错误，且该错误在Workbench中不存在

错误信息 #

$ npm run seed

> employee-tracker@1.0.0 seed
> node db/reset.js

node:events:491
      throw er; // Unhandled 'error' event
      ^

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE employee_tracker;

USE employee_tracker;

CREATE TABLE departmen' at line 3
    at Packet.asError (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:456:32)
    at PacketParser.onPacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:85:12)
    at PacketParser.executeStart (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
Emitted 'error' event on Query instance at:
    at Query.execute (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\commands\command.js:39:14)
    at Connection.handlePacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:456:32)
    [... lines matching original stack trace ...]
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE employee_tracker;\n" +
    '\n' +
    'USE employee_tracker;\n' +
    '\n' +
    "CREATE TABLE departmen' at line 3",
  sql: 'DROP DATABASE IF EXISTS employee_tracker;\n' +
    '\n' +
    'CREATE DATABASE employee_tracker;\n' +
    '\n' +
    'USE employee_tracker;\n' +
    '\n' +
    'CREATE TABLE department (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    name VARCHAR(30) UNIQUE NOT NULL\n' +
    ');\n' +
    '\n' +
    'CREATE TABLE role (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    title VARCHAR(30) NOT NULL,\n' +
    '    salary DECIMAL NOT NULL,\n' +
    '    department_id INT NOT NULL,\n' +
    '    INDEX dp_ind (department_id),\n' +
    '    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE\n' +
    ');\n' +
    '\n' +
    'CREATE TABLE employee (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    first_name VARCHAR(30) NOT NULL,\n' +
    '    last_name VARCHAR(30) NOT NULL,\n' +
    '    role_id INT NOT NULL,\n' +
    '    INDEX role_ind (role_id),\n' +
    '    CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,\n' +
    '    manager_id INT,\n' +
    '    INDEX manager_ind (manager_id),\n' +
    '    CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employee(id) ON DELETE SET NULL\n' +
    ');'
}

补充说明 #

package.json里有个seed脚本，执行node db/index.js，这个文件只负责导入JoeSQL并调用reset方法。需求是通过npm脚本完成数据库重置，同时希望得到更优的实现方案。

问题原因与解决方案 #

核心原因

mysql2的connection.query()默认不支持一次性执行多条SQL语句，而你的schema.sql包含了多条独立命令（DROP、CREATE DATABASE、USE、CREATE TABLE等）。MySQL Workbench会自动拆分语句执行，但mysql2需要显式开启多语句支持，或手动拆分语句执行。此外，原代码未处理异步操作，调用end()会提前关闭连接，导致执行中断。

方案1：开启多语句查询（快速解决，注意安全）

修改连接配置开启多语句支持，同时用Promise处理异步逻辑：

// 修改SQL_CONFIG
const SQL_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: SECRETS.SQL,
    database: 'employee_tracker',
    multipleStatements: true // 新增开启多语句
}

// 修改reset方法为异步
async reset() {
    const schema = fs.readFileSync("db/schema.sql", {encoding: "utf-8"});
    const seed = fs.readFileSync("db/seed.sql", {encoding: "utf-8"});
    
    this.connect();
    
    // 用Promise包装query，等待执行完成
    await new Promise((resolve, reject) => {
        this.connection.query(schema, (err) => err ? reject(err) : resolve());
    });
    
    await new Promise((resolve, reject) => {
        this.connection.query(seed, (err) => err ? reject(err) : resolve());
    });

    this.end();
}

注意：开启multipleStatements存在SQL注入风险，仅适用于执行可信的本地SQL文件。

方案2：手动拆分SQL语句（更安全）

将SQL文件按分号拆分，过滤空行后逐条执行，无需开启多语句支持：

async reset() {
    const schemaContent = fs.readFileSync("db/schema.sql", {encoding: "utf-8"});
    const seedContent = fs.readFileSync("db/seed.sql", {encoding: "utf-8"});
    
    this.connect();

    // 封装语句执行逻辑
    const executeBatch = (content) => {
        return content.split(';')
            .map(q => q.trim())
            .filter(q => q.length > 0)
            .reduce((promise, query) => {
                return promise.then(() => {
                    return new Promise((resolve, reject) => {
                        this.connection.query(query, (err) => err ? reject(err) : resolve());
                    });
                });
            }, Promise.resolve());
    };

    await executeBatch(schemaContent);
    await executeBatch(seedContent);

    this.end();
}

方案3：用mysql2/promise简化异步代码

使用mysql2的Promise版本API，让代码更简洁：

// 导入promise版本
import mysql2 from "mysql2/promise";

const SQL_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: SECRETS.SQL,
    database: 'employee_tracker'
}

class JoeSQL {
    constructor() {
        this.connection = null;
    }

    async connect() {
        this.connection = await mysql2.createConnection(SQL_CONFIG);
    }

    async end() {
        await this.connection.end();
    }

    async reset() {
        const schema = fs.readFileSync("db/schema.sql", {encoding: "utf-8"});
        const seed = fs.readFileSync("db/seed.sql", {encoding: "utf-8"});
        
        await this.connect();
        
        // 拆分并执行语句
        const executeQueries = async (content) => {
            const queries = content.split(';')
                .map(q => q.trim())
                .filter(q => q);
            for (const q of queries) {
                await this.connection.execute(q);
            }
        };

        await executeQueries(schema);
        await executeQueries(seed);

        await this.end();
    }
}

额外优化建议 #

1. 错误处理：添加try/catch捕获异常，避免程序崩溃：

async reset() {
    try {
        // 原有执行逻辑
    } catch (err) {
        console.error('数据库重置失败:', err);
        if (this.connection) await this.connection.end();
        process.exit(1); // 标记脚本执行失败
    }
}

2. 绝对路径：用path.resolve处理文件路径，避免相对路径问题：

import path from "path";
const schemaPath = path.resolve(process.cwd(), 'db/schema.sql');
const schema = fs.readFileSync(schemaPath, {encoding: "utf-8"});

3. 直接用MySQL命令行：无需Node.js脚本，直接在package.json中配置命令：

{
  "scripts": {
    "seed": "mysql -u root -p${DB_PASSWORD} < db/schema.sql && mysql -u root -p${DB_PASSWORD} employee_tracker < db/seed.sql"
  }
}

可以通过环境变量DB_PASSWORD传递密码，避免明文暴露。

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