pbl-api/mysql.js

var us = {
    mysql: require('mysql'),
    sqle: {}
};

us.mysqlconnection = function (host, database) {
    if (!us.sqle[host] || !us.sqle[host][database]) {
        let port = 3306;
        if (host == '123.58.32.152') port = 20330;
        else if (host == '183.36.25.92' || host == '183.36.26.8') port = 20007;

        us.sqle[host] = us.sqle[host] || {};
        us.sqle[host][database] = us.mysql.createPool({
            connectionLimit: 10,        // ★ 必须明确设置，建议 10~20
            queueLimit: 0,              // 0 = 不限制排队（默认）
            acquireTimeout: 10000,      // 取连接超时
            waitForConnections: true,
            host: host,
            user: "root",
            password: host == '127.0.0.1' ? "root" : "cocorobo",
            charset: 'utf8mb4_general_ci',
            database: database,
            port: port,
            connectTimeout: 10000,
            // 长连接保活，防止被 MySQL/中间件掐断后残留死连接
            enableKeepAlive: true,
            keepAliveInitialDelay: 10000
        });
    }
    return us.sqle[host][database];
};

// ★ 用 pool.query() 替代 getConnection+query+release
exports.usselect = function (param, callback) {
    if (param.length <= 1) return callback(new Error('param invalid'));

    var pool = us.mysqlconnection(param[0], param[1]);

    // 参数解码
    try {
        for (var i = 0; i < param.length; i++) {
            param[i] = decodeURIComponent(param[i]);
        }
    } catch (e) {
        console.log("decodeURIComponent 失败：", e);
        return callback(e);
    }

    var _sql = "CALL ";
    var _param = [];
    if (param.length > 3) {
        _param = param.slice(3);
        _sql += (param[2] || "") + "(?";
        _sql += Array(_param.length).join(",?");
        _sql += ");";
    } else {
        _sql += param[2] + "();";
    }

    pool.query(_sql, _param, function (error, results) {
        if (error) {
            console.log("sql执行失败", error);
            return callback(error);
        }
        callback(results.affectedRows != null
            ? results.affectedRows
            : results.length > 1 ? results : results[0]);
    });
};

exports.usquery = function (param, callback) {
    if (param.length <= 2) return callback(new Error('param invalid'));

    var pool = us.mysqlconnection(param[0], param[1]);
    var _sql = param[2];
    var _param = param.length > 3 ? param.slice(3) : [];

    pool.query(_sql, _param, function (error, results) {
        if (error) {
            console.log("sql执行失败", error);
            return callback(error);
        }
        callback(results.affectedRows != null
            ? results.affectedRows
            : results.length > 1 ? results : results[0]);
    });
};


//重新连接处理
// us.mysqlconnet.reconnection = function (host, database, error) {
//     if (error) {
//         console.log("连接失败,尝试重连", error);
//         setTimeout(function () {
//             us.mysqlconnection(host, database)
//         }, 2000);
//     }
// }