node.js操作mysql数据库

const mysql = require('mysql');
// var config = require('../config');
const connStr = require('../config').connStr;

var execute = function (sql, params, cb) {
    var conn = mysql.createConnection(connStr);
    conn.connect();
    conn.query(sql, params, function (error, rows, result) {
        if (error) {
            console.error('================= executePromise error ================')
            console.error('sql:', sql);
            console.error('-------------------------------------------------------')
            console.error(params);
            console.error('-------------------------------------------------------')
            console.error(error);
            console.error('=======================================================')
        }
        cb && cb(error, rows, result);
    });
    conn.end();
}

var executePromise = function (sql, params) {
    return new Promise((resolve, reject) => {
        execute(sql, params, (err, result, status) => {
            if (err) return reject(err);
            resolve(result);
        });
    })
}

var insertPromise = function (sql, params) {
    if (!(/^insert into/g.test(sql)) && typeof (params) === 'object' && !(params instanceof Array)) {
        var fields = new Array();
        var sqlParams = new Array();
        var sqlValue = new Array();
        for (var i in params) {
            var v = params[i];
            if (typeof (v) !== 'object') {
                fields.push(i);
                if (v === 'getDate()') {
                    sqlValue.push('UNIX_TIMESTAMP()')
                } else {
                    sqlValue.push('?');
                    sqlParams.push(v);
                }
            }
        }
        sql = ['insert into `', sql, '`(`', fields.join('`,`'), '`) values(', sqlValue.join(','), ')'].join('');
        params = sqlParams;
    }
    return new Promise((resolve, reject) => {
        executePromise(sql, params)
            .then(r => {
                resolve(r.insertId > 0 ? r.insertId : r.affectedRows);
            })
            .catch(reject);
    })
}

var deletePromise = function (sql, params) {
    if (!(/^insert into/g.test(sql)) && params && !(params instanceof Array) && typeof (params) === 'object') {
        var fields = new Array();
        var sqlParams = new Array();
        for (var i in params) {
            var v = params[i];
            if (typeof (v) === 'string') {
                if (v === 'getDate()') {
                    fields.push(['`', i, '`=UNIX_TIMESTAMP()'].join(''))
                } else {
                    fields.push(['`', i, '`=?'].join(''))
                    sqlParams.push(v);
                }
            }
        }
        sql = ['delete from `', sql, '` where ', fields.join(',')].join('');
        params = sqlParams;
    }
    return new Promise((resolve, reject) => {
        executePromise(sql, params)
            .then(r => {
                resolve(r.affectedRows);
            })
            .catch(reject);
    })
}

var updatePromise = function (sql, params) {
    // var reg = /^`?([^`\s]+)`?(\s+where\s+\S+)?$/g
    // if (reg.test(sql) && typeof (params) === 'object' && !(params instanceof Array)) {
    //     console(RegExp.$1);
    //     return Promise.reject('yes');
    // }
    return new Promise((resolve, reject) => {
        executePromise(sql, params)
            .then(r => {
                resolve(r.changedRows);
            })
            .catch(reject);
    })
}

/**
 * 查询数据
 * @param {*} sql 
 * @param {*} params 
 * @param {*} top 取多少条数据
 * @param {*} skip 跳过多少条数据
 */
var queryPromise = function (sql, params, top, skip) {
    params = params || [];
    var limit = '';
    if (top && !isNaN(top)) {
        sql += ' limit ?';
        if (skip && !isNaN(skip)) {
            sql += ',?';
            params.push(skip);
        }
        params.push(top);
    }
    return executePromise(sql, params);
}

/**
 * 查询并返回第一条数据
 * @param {*} sql 
 * @param {*} params 
 */
var queryFirstRowPromise = function (sql, params) {
    return new Promise((resolve, reject) => {
        queryPromise(sql, params, 1)
            .then(rows => {
                resolve(rows.length > 0 ? rows[0] : null);
            }).catch(reject);
    })
}

/**
 * 查询并返回首行首列
 * @param {*} sql 
 * @param {*} params 
 */
var queryScalarPromise = function (sql, params) {
    return new Promise((resolve, reject) => {
        execute(sql, params || [], (err, rows, result) => {
            if (err) return reject(err);
            if (rows.length > 0)
                resolve(rows[0][result[0].name]);
        })
    })
}

var anyPromise = function (sql, params) {
    return new Promise((resolve, reject) => {
        execute(sql += ' limit 1', params, (err, rows) => {
            if (err) return reject(err);
            resolve(rows.length > 0);
        })
    })
}
exports.anyPromise = anyPromise;
exports.insertPromise = insertPromise;
exports.deletePromise = deletePromise;
exports.updatePromise = updatePromise;
exports.queryPromise = queryPromise;
exports.queryFirstRowPromise = queryFirstRowPromise;
exports.queryScalarPromise = queryScalarPromise;