道者编程


node基于mysql2的封装

驱动基于mysql2包,据说mysql2拥有更好的性能和兼容性。地址:https://github.com/sidorares/node-mysql2

npm install mysql2 --save

 安装后搞进来

const mysql = require('mysql2');
const config = require('../../config/config'); //配置文件
const mysqlConfig = {
    host: config.db.HOST,
    port: config.db.PORT,
    user: config.db.USERNAME,
    password: config.db.PASSWORD,
    database: config.db.DATABASE,
    charset: config.db.CHARSET, //字符集
    multipleStatements: true, // 执行多条sql
    waitForConnections: true, // 超过最大连接时排队
    connectionLimit: 100, // 最大连接数
    queueLimit: 0, // 排队最大数量(0 代表不做限制)
    timezone: '+08:00', //时区
};

let data = { dbname:'' , fileds:'*' , where:'' , order:'' , limit:'' , group:'' , see:'' , leftJoin:[] , rightJoin:[] , join:[] , bind:''}

/**
 * 执行sql
 * @param {string} sql
 * @param {array} values 绑定值
 * @return {string}
 */
exports.query = ( sql, values = [] ) => {
    return new Promise(( resolve, reject ) => {
        const pool = mysql.createPool(mysqlConfig); // 创建连接池
        
        // 返回 sql语句,便于调试
        if(data.see === true){
            resolve(mysql.format(sql, values));
            return;
        }
        pool.getConnection(function(err, conn) { //取连接池
            if(err){
                console.error('error connecting: ' + err)
                reject('mysql error') //抛出异常
            }else{
                let query = conn.query(sql, values, ( error, rows) => { //执行
                    //console.log(query.sql) //sql语句
                    //console.log(query.sql.slice(1))
                    if ( error ) {
                        console.error('error query: ' + error)
                        reject('mysql error') //抛出异常
                    } else {
                        resolve( rows ); //返回结果
                    }
                })
                pool.releaseConnection(conn); //释放连接
            }
        })
    })
}

/**
* 防注入过滤
* @param {string} str
* @return {string}
*/
exports.escape = function escape(str){
    return pool.escape(str)
}

/**
* 获取单条数据
* @param {string} sql
* @return {object}
*/
exports.first = function(sql='') {
    sql =  splic(sql)
    reset()
    try {
        return this.query(sql,data.bind).then(rows => {
            if(data.see == 1){
                return rows
            }else{
                return rows[0]
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 获取多条数据
* @param {string} sql
* @return {array}
*/
exports.get = function(sql='') {
    sql =  splic(sql)
    reset()
    try {
        return this.query(sql,data.bind).then(rows => {
            return rows
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 获取单个指定字段值,如果为空,返回空字符串
* @param {string} filedName 待获取的字段
* @param {string} sql
* @return {}
*/
exports.getField = function(filedName,sql=''){
    sql =  splic(sql)
    reset()
    try {
        return this.query(sql,data.bind).then(rows => {
            if(data.see == 1){
                return rows
            }else {
                if(rows.length){
                    return rows[0][filedName]
                }else{
                    return ''
                }
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 根据sql获取总条数
* @param {string} sql
* @return {int}
*/
exports.count = function (sql='') {
    sql =  splic(sql)
    reset()
    if ( sql.indexOf("FROM") !=-1 ){
        strSp = ' FROM '
    }else{
        strSp = ' from '
    }
    sqlList = sql.split(strSp)


    sql = 'SELECT count(*) AS count FROM '+sqlList[1]
    try {
        return this.query(sql,data.bind).then(rows => {
            if(data.see == 1){
                return rows
            }else {
                return rows[0].count
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 插入数据,返回自增ID
* @param {string} sql
* @param {object} 键值对插入数据
* @example fileds = {name: 'Hello MySQL'}
* @return {}
*/
exports.insert = function (table,fileds) {
    let sql = 'INSERT INTO '+table+ ' SET ?'
    try {
        return this.query(sql,fileds).then(rows => {
            if(data.see == 1){
                return rows
            }else {
                return rows.insertId
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 删除数据,返回影响行数,失败返回 -1
* @param {string} table
* @param {string} where
* @return {int}
*/
exports.delete = function (table,where) {
    let sql = 'DELETE FROM '+table+' WHERE '+where
    try {
        return this.query(sql).then(results => {
            if(data.see == 1){
                return results
            }else {
                return results.affectedRows
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
* 更新数据 返回影响行数,失败返回 -1
* @param {string} sql
* @param {object} 键值对更新数据
* @param {string} where
* @example fileds = {name: 'Hello MySQL'}
* @return {}
*/
exports.update = function(table,fileds,where) {
    let sql = 'UPDATE '+table+' SET '
    let field = ''
    let values = []
    for (let index in fileds){
        field += index+'= ?, '
        values.push(fileds[index])
    }
    field = field.replace(/^(\s|,)+|(\s|,)+$/g, '')
    sql += field + ' WHERE '+where
    try {
        return this.query(sql,values).then(results => {
            if(data.see == 1){
                return results
            }else {
                return results.changedRows
            }
        })
    } catch (err) {
        return new Promise((resolve, reject) => {
            reject(err)
        })
    }
}

/**
 * 事务处理
 */
exports.beginTransaction = function () {
    return new Promise((resolve, reject) => {
        pool.getConnection((error, connection) => {
            if (error) {
                //logCustom(error);
                console.error(`error connecting: ${error}`);
                reject(error); // 抛出异常
            } else {
                data.connection = connection;
                data.connection.beginTransaction();
                resolve(connection);
            }
        });
    });
};

/**
 * 提交事务
 */
exports.commit = function () {
    return new Promise((resolve, reject) => {
        data.connection.commit((err) => {
            if (err) {
                // console.log(err)
                reject(err);
                // resolve('mysql error')
            } else {
                resolve();
            }
        });
    });
};

/**
 * 回滚
 */
exports.rollback = function (connection) {
    return new Promise((resolve, reject) => {
        data.connection.rollback((err) => {
            if (err) {
                reject(err);
                // resolve('mysql error')
            } else {
                resolve();
            }
        });
    });
};

/**
 * 返回 SQL语句
 */
exports.see = function () {
    data.see = true;
}

/**
 * 设置表名
 */
exports.dbname = function (table) {
    data.dbname = table
    return this
}

/**
 * 设置字段
 */
exports.fileds = function(fileds){
    data.fileds = fileds
    return this
}

/**
 * 设置where条件
 */
exports.where = function(where,bind='') {
    if(bind == ''){
        data.where = ' WHERE '+where
    }else{
        data.where = ' WHERE '+where
        data.bind = bind
    }
    return this
}

/**
 * 设置group
 */
exports.group = function(filed) {
    data.group = ' GROUP BY '+filed
    return this
}

/**
 * 设置order
 */
exports.order = function(field,sort='ASC'){
    data.order = ' ORDER BY '+field+' '+sort
    return this
}

/**
 * 设置limit
 */
exports.limit = function (start,end=''){
    if(end !=''){
        data.limit = ' LIMIT '+start+','+end
    }else{
        data.limit = ' LIMIT '+start
    }
    return this
}

/**
 * 左连接
 */
exports.leftJoin = function(tableName,where) {
    whereList = where.split('=')
    leftWhere = whereList[0].trim()+' = '+whereList[1].trim()
    data.leftJoin.push(' LEFT JOIN '+tableName+' ON '+leftWhere)
    return this
}

/**
 * 右连接
 */
exports.rightJoin = function(tableName,where) {
    whereList = where.split('=')
    leftWhere = whereList[0].trim()+' = '+whereList[1].trim()
    data.leftJoin.push(' RIGHT JOIN '+tableName+' ON '+leftWhere)
    return this
}

/**
 * 内连接
 */
exports.join = function(tableName,where) {
    whereList = where.split('=')
    leftWhere = whereList[0].trim()+' = '+whereList[1].trim()
    data.leftJoin.push(' INNER JOIN '+tableName+' ON '+leftWhere)
    return this
}

reset = function() {
    data.fileds = '*'
    data.where = ''
    data.order = ''
    data.limit = ''
    data.group = ''
    data.sql = ''
    data.leftJoin = []
    data.rightJoin = []
    data.join = []
}

/**
 * 拼凑SQL
 */
splic = function (sql='') {
    if( sql != '' ) {
        return sql
    }
    let sqldata = 'SELECT '+data.fileds+' FROM '+data.dbname


    if( data.leftJoin.length >= 0 ) {
        let left = ''
        for (let index in data.leftJoin){
            left += data.leftJoin[index]
        }
        sqldata += left
    }


    if( data.rightJoin.length >= 0 ) {
        let right = ''
        for (let index in data.rightJoin){
            right += data.rightJoin[index]
        }
        sqldata += right
    }


    if( data.join.length >= 0 ) {
        let join = ''
        for (let index in data.join){
            join += data.join[index]
        }
        sqldata += join
    }


    if( data.where != '' ) {
        sqldata += data.where
    }


    if( data.group != '' ) {
        sqldata += data.group
    }


    if( data.order !='' ) {
        sqldata += data.order
    }


    if( data.limit !='' ) {
        sqldata += data.limit
    }
    return sqldata
}

 用法:

const db = require('db')
//查询单条
let result = await db.dbname('goods').where("id = ?",[4]).order('id').first() //绑定参数,绑定自动转义
let result = await db.dbname('goods').where('id = 7').group('id').first() //直接写参数
let result = await db.dbname('goods').leftJoin('goods','a =b').where('id = 7').group('id').first() //左连接,支持多个
let result = await db.dbname('goods').rightJoin('goods','a =b').where('id = 7').group('id').first() //右连接
let result = await db.dbname('goods').join('goods','a =b').where('id = 7').group('id').first() //内连接
let result = await db.first("SELECT * FROM goods WHERE id=4") //也可以直接写sql
return ctx.body = result

防止SQL注入有两种方法
1:使用escape方法,过滤where参数
let mobile = await db.escape('13512347890')
let name = await db.escape('liming')
data = await db.dbname('goods')
    .where(`mobile='${mobile}' AND name='${name}'`)
    .get();

2:使用占位符 ?  绑定参数
let mobile = '13512347890'
let name = 'liming'
data = await db.dbname('goods')
    .where('mobile=? AND name=?',[mobile,name])
    .get();


//查询所有
let result = await db.get("SELECT * FROM goods") //也可以直接写sql
let result = await db.dbname('goods').limit(8).get() //查询所有
let result = await db.dbname('goods').limit(1,8).get()
let return ctx.body = result


// 获取数量
let result = await db.count("SELECT id , title , name from goods") //可以直接写
let result = await db.dbname('goods').fileds('id,title').where("id != ?",[4]).count() //也可以构建
return ctx.body = result


//获取一个字段的值
let result = await db.dbname('goods').where("id = ?",[7]).getField('title') //也可以构建
let result = await db.getField('title','SELECT * FROM goods WHERE id=7') //可以直接写
return ctx.body = result


//插入,键值对入库 ,返回自增ID
let fileds = {name:"hellow nodejs",title:"李大龙",age:23}
let result = await db.insert('goods',fileds)
return ctx.body = result


//更新 ,键值对入库,加where条件 返回影响行数
let fileds = {name:"hellow nodejs",title:"成龙",age:23}
let result = await db.update('goods',fileds,'id = 7')
return ctx.body = result


//删除 返回影响行数
let result = await db.delete('goods','id=4')
return ctx.body = result


//原生SQL
let result = await db.query(sql) //这里写原生方式的增删查改


//获取sql语句,具体如下
db.see() //获取sql语句


//比如获取查询语句
db.see() //首先设置获取sql语句
let result = await db.get("SELECT * FROM goods") //这里返回的就是sql语句,其他所有的增删查改都是一样
return ctx.body = result
 

 事务:

await db.beginTransaction(); //开启事务
try {
    await db.insert('merchant_banks', banksFileds);
    await db.insert('merchant_manages', managesFileds);
    await db.update('shop_users', { mid: merchant_id }, `id = ${shop_user_id}`);
 
    await db.commit(); //提交
} catch (err) {
    await db.rollback(); //回滚
    throw err;
} finally {
    await db.release(); //释放
}
 


最新评论:
我要评论:

看不清楚