nodejs封装mysql执行语句函数

1.mysql.js

var mysql = require('mysql');

module.exports = function m_connection(req,callback,notoken){
	let m_req = req;
	let m_callback = callback;
	let m_notoken = notoken;
	
	var connection = mysql.createConnection({
		host: 'mysql数据库地址',
		user: '***',
		password: '***',
		database: '***',
		autoReconnect: true,
		multipleStatements: true,    //支持执行多条sql语句
	});
	
	connection.connect();
	
	callback(connection);
	
};

2.common.js

var mysql = require('../bd/mysql.js');

module.exports = {
	//sql语句单条执行
	sql(res, sql_str, callback) {
		mysql('',(sql)=> {
			sql.query(sql_str, (error, results, fields)=> {
				if (error) {
					res.send(error);
				}else{
					callback(results);
				}
			});
			sql.end();
		})
	},
	
	//sql语句批量执行
	sqlBatch(res, sql2, callback) {
		let sql1 = `BEGIN;`;
		let sql_all = sql1 + sql2;
		mysql('', (sql)=> {
			sql.query(sql_all, (error, results, fields)=> {
				if (error) {
					//事务回滚
					sql.query('ROLLBACK;', (error2, results2, fields2)=> {
						if (error2) {
							res.send(error2);
						}else{
							// console.log(results2);
							res.send({
								status: 202,
								message: 'success',
								data: results2
							});
						}
						sql.end();
					});
				}else{
					//事务确认
					sql.query('COMMIT;', (error3, results3, fields3)=> {
						if (error) {
							res.send(error);
						}else{
							results.shift();
							callback(results);
						}
						sql.end();
					});
				}
			});
		})
	}
}

3.执行单条SQL语句

const express = require('express');
var mysql = require('../../bd/mysql.js');
var common = require('../../common/common.js');
var router = express.Router()

router.get('/userList', (req,res)=> {
	common.sql(res, `SELECT * FROM t_user`, (results)=>{
		res.send({
			code: 200,
			message: 'success',
			data: results
		});
	})
})

4.执行多条SQL语句

const express = require('express');
var mysql = require('../../bd/mysql.js');
var common = require('../../common/common.js');
var router = express.Router()

router.get('/typeAndSub', (req,res)=> {
	let sql1 = `SELECT * FROM t_type WHERE deleted = 0;`;
	let sql2 = `SELECT * FROM t_sub_type WHERE deleted = 0;`;
	let sql_str = `${sql1}${sql2}`;
	
	common.sqlBatch(res, sql_str, (results)=>{
		res.send({
			code: 200,
			message: 'success',
			data: {
				type: results[0]
				subType: results[1]
			}
		});
	})
})