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(); //释放
} 