node使用mysql

安装

npm install mysql2

const net = require('net');
const fs = require('fs');
const mysql = require('mysql2/promise');

let configsList;
// 检查端口是否被占用
function isPortTaken(port) {
    return new Promise((resolve) => {
        const server = net.createServer();
        server.once('error', (err) => {
            if (err.code === 'EADDRINUSE') {
                resolve(true);
            } else {
                resolve(false);
            }
        });
        server.once('listening', () => {
            server.close();
            resolve(false);
        });
        server.listen(port, '0.0.0.0');
    });
}

function mysqlconn(configs){
    configsList = configs;
    // console.log(configsList)
    // 创建数据库连接池
    const pool = mysql.createPool({
        host: configs.myip,
        user: configs.myuser,
        password: configs.mypwd,
        port:configs.myport,
        database: 'wbrj_accset',
        waitForConnections: true,
        connectionLimit: 2,
        queueLimit: 0
    });
    return pool;
}

// 查询sql
async function query(pool,sql) {
    try {
        // const sql = `SELECT * FROM databasesource WHERE mysql_type != ${userId}`;
        const [rows] = await pool.execute(sql);
        // console.log(rows)
        return rows
    } catch (error) {
        // pool对象被销毁时，重试
        pool = mysqlconn(configsList)
        try {
            const [rows] = await pool.execute(sql);
            // console.log(rows)
            return rows
        } catch (error) {
            console.error('查询出错:', error);
        }
    }
}

//修改sql
async function update(pool,sql) {
    try {
        // const sql = `SELECT * FROM databasesource WHERE mysql_type != ${userId}`;
        const [rows] = await pool.execute(sql);
        // console.log(rows)
        return rows
    } catch (error) {
        // pool对象被销毁时，重试
        pool = mysqlconn(configsList)
        try {
            const [rows] = await pool.execute(sql);
            // console.log(rows)
            return rows
        } catch (error) {
            console.error('修改出错:', error);
            return "修改出错"+error
        }
    }
}

// 新增sql
async function inserts(pool,sql) {
    try {
        // const sql = `SELECT * FROM databasesource WHERE mysql_type != ${userId}`;
        const [rows] = await pool.execute(sql);
        // console.log(rows)
        return rows
    } catch (error) {
        // pool对象被销毁时，重试
        pool = mysqlconn(configsList)
        try {
            const [rows] = await pool.execute(sql);
            // console.log(rows)
            return rows
        } catch (error) {
            console.error('新增出错:', error);
            return "新增出错"+error
        }
    }
}

// 创建数据库方法
async function createDatabase(pool, databaseName) {
    try {
        // 检查数据库是否已经存在
        const checkSql = `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '${databaseName}'`;
        const [existingDatabases] = await pool.execute(checkSql);
        if (existingDatabases.length > 0) {
            console.log(`数据库 ${databaseName} 已经存在`);
            return;
        }
        // 创建数据库
        const createSql = `CREATE DATABASE \`${databaseName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci`;
        await pool.execute(createSql);
        console.log(`数据库 ${databaseName} 创建成功`);
    } catch (error) {
        // pool对象被销毁时，重试
        pool = mysqlconn(configsList);
        try {
            const checkSql = `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '${databaseName}'`;
            const [existingDatabases] = await pool.execute(checkSql);
            if (existingDatabases.length > 0) {
                console.log(`数据库 ${databaseName} 已经存在`);
                return;
            }
            const createSql = `CREATE DATABASE \`${databaseName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci`;
            await pool.execute(createSql);
            console.log(`数据库 ${databaseName} 创建成功`);
        } catch (error) {
            console.error(`创建数据库 ${databaseName} 出错:`, error);
            return `创建数据库 ${databaseName} 出错: ${error}`;
        }
    }
}

// 导入sql文件
async function installSql(sqlFilePath,configs,datasource) {
    // console.log(configsList)
    // 创建数据库连接池
    const pool = mysql.createPool({
        host: configs.myip,
        user: configs.myuser,
        password: configs.mypwd,
        port:configs.myport,
        database: datasource,
        waitForConnections: true,
        connectionLimit: 2,
        queueLimit: 0,
        multipleStatements: true,
        charset: 'utf8mb4'
    });
    try {
        const sql = fs.readFileSync(sqlFilePath, 'utf8');
        // 移除 SQL 注释
        const cleanedSql = sql.replace(/(--.*|\/\*[\s\S]*?\*\/)/g, '');
        // 使用正则表达式分割 SQL 语句
        const statements = cleanedSql.split(/;(?=(?:[^']*'[^']*')*[^']*$)/).filter(statement => statement.trim()!== '');
        for (const statement of statements) {
            await pool.execute(statement);
            console.log(statement)
        }
        console.log('SQL 文件导入成功');
    } catch (error) {
        console.error('导入 SQL 文件出错:', error);
    }
}

module.exports = {
    isPortTaken,
    mysqlconn,
    query,
    update,
    inserts,
    createDatabase,
    installSql
};