# 新建/libs/model.js **model类采用了连接池进行操作,集成了增删改查方法,数据字段自动过滤功能,sql错误自动记录功能,数据查询文件缓存功能,查询方法可以select、find、column、value、map进行查询** ``` var fs = require('fs'); var config = require('../config'); var tools = require(__dirname + '/tools'); var mysql = require('mysql'); //使用config的数据库配置连接数据库 var connection = mysql.createConnection(config.mysql); const crypto = require('crypto'); var pool = mysql.createPool(config.mysql); //设置sql模式 pool.on('connection', function (connection) { connection.query("SET sql_mode=''"); }); var model = { debug: false, init: function () { }, //记录sql错误 log_error: function (err, data = '') { let str = err.errno + ':' + err.sqlMessage + "\n" + err.sql + "\n" + JSON.stringify(data) + "\n"; fs.appendFile(tools.runtimePath + 'mysql.txt', str, (err) => { console.error('sql error:', str); }); }, //数据表字段缓存 table_fields: function (table) { //check exist let jsonfile = tools.tableCachePath + table + '.json'; if (fs.existsSync(jsonfile)) { try { return JSON.parse(fs.readFileSync(jsonfile)); } catch (e) { return []; } } return new Promise((resolve, reject) => { pool.query("select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA=? and TABLE_NAME=?", [config.mysql.database, table], (err, results) => { if (err) { try { this.log_error(err, [table]); } catch (e) { } resolve([]); return; } let r = []; for (let i = 0; i < results.length; i++) { r.push(results[i]['COLUMN_NAME']); } try { fs.writeFileSync(jsonfile, JSON.stringify(r)); } catch (e) { console.error('save table jsonfile fail!'); } resolve(r); }); }); }, //where封装 where: function (obj, before = '') { if (Object.keys(obj).length == 0) return before; let sql = []; if (before) sql.push(before); for (let k in obj) { let v = obj[k]; if (v instanceof Array) { if (v.length != 2) continue; if (v[0].toLowerCase() == 'like') { sql.push(` ${k} like '${v[1]}' `); continue; } if (v[0].toLowerCase() == 'in') { if (!(v[1] instanceof Array)) { v[1] = [v[1]]; } v[1].push(0); v[1] = v[1].join(','); sql.push(` ${k} in(${v[1]}) `); continue; } if (v[0].toLowerCase() == 'not in') { if (!(v[1] instanceof Array)) { v[1] = [v[1]]; } v[1].push(0); v[1] = v[1].join(','); sql.push(` ${k} not in(${v[1]}) `); continue; } if (v[0].toLowerCase() == 'exp') { sql.push(` ${v[1]} `); continue; } sql.push(` ${k} ${v[0]} '${v[1]}' `); continue; } sql.push(` ${k}='${v}' `); } return sql.join(' and '); }, //insert insert: function (sql, p = []) { return new Promise((resolve, reject) => { pool.query(sql, p, (err, results, fields) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(false); return; } resolve(results.insertId); }); }); }, //insert into table set ? i: async function (sql, p = {}) { let table = sql.match(/insert into(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1]; if (!table) return false; let fields = await this.table_fields(table); let p2 = {}; for (var i in p) { if (fields.indexOf(i) > -1) p2[i] = p[i]; } return new Promise((resolve, reject) => { pool.query(sql, p2, (err, results, fields) => { if (err) { try { this.log_error(err, p2); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(false); return; } resolve(results.insertId); }); }); }, //update update: function (sql, p = []) { return new Promise((resolve, reject) => { pool.query(sql, p, (err, results, fields) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(false); return; } resolve(true); }); }); }, //update table set ? where id=? u: async function (sql, p = {}, where = []) { let table = sql.match(/update(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1]; if (!table) return false; let fields = await this.table_fields(table); let p2 = {}; for (var i in p) { if (fields.indexOf(i) > -1) p2[i] = p[i]; } where.unshift(p2); return new Promise((resolve, reject) => { pool.query(sql, where, (err, results, fields) => { if (err) { try { this.log_error(err, where); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(false); return; } resolve(true); }); }); }, //delete delete: function (sql, p = []) { return new Promise((resolve, reject) => { pool.query(sql, p, (err, results, fields) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(false); return; } resolve(true); }); }); }, //获取md5 getSign: function (sql, p, cacheTime) { return cacheTime > 0 ? crypto.createHash('md5').update(`${sql}${JSON.stringify(p)}`).digest('hex') : ''; }, //读取缓存 getCache: async function (sign, cacheTime) { let cachePath = tools.runtimePath + 'cache/'; let subFolderA = sign.substr(0, 2); let subFolderB = sign.substr(2, 2); let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json'; try { let e = await fs.promises.stat(dest); let mTime = parseInt(e.mtimeMs / 1000); let now = parseInt(new Date().getTime() / 1000); if (now > mTime + cacheTime) return [false, null]; let d = JSON.parse(await fs.promises.readFile(dest, 'utf8')); return [true, d]; } catch (err) { if (err) return [false, null]; } }, //设置缓存 setCache: async function (sign, data) { let cachePath = tools.runtimePath + 'cache/'; let subFolderA = sign.substr(0, 2); let subFolderB = sign.substr(2, 2); let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json'; try { await fs.promises.mkdir(cachePath + subFolderA + '/' + subFolderB + '/', { recursive: true }); await fs.promises.writeFile(dest, JSON.stringify(data)); return true; } catch (err) { if (err) return false; } }, //select返回数组 select: async function (sql, p = [], cacheTime = 0) { let sign = this.getSign(sql, p, cacheTime); if (cacheTime > 0) { let d = await this.getCache(sign, cacheTime); if (d[0] === true) return d[1]; } return new Promise(async (resolve, reject) => { pool.query(sql, p, async (err, results) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve([]); return; } if (cacheTime > 0) { await this.setCache(sign, results.length > 0 ? results : []); } resolve(results.length > 0 ? results : []); }); }); }, //find返回object find: async function (sql, p = [], cacheTime = 0) { let sign = this.getSign(sql, p, cacheTime); if (cacheTime > 0) { let d = await this.getCache(sign, cacheTime); if (d[0] === true) return d[1]; } return new Promise(async (resolve, reject) => { pool.query(sql, p, async (err, results) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve({}); return; } if (cacheTime > 0) { await this.setCache(sign, results.length > 0 ? results[0] : {}); } resolve(results.length > 0 ? results[0] : {}); }); }); }, //column返回一列数组 column: async function (sql, p = [], cacheTime = 0) { let sign = this.getSign(sql, p, cacheTime); if (cacheTime > 0) { let d = await this.getCache(sign, cacheTime); if (d[0] === true) return d[1]; } return new Promise(async (resolve, reject) => { pool.query(sql, p, async (err, results) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve([]); return; } let r = []; for (let i = 0; i < results.length; i++) { for (let n in results[i]) { r.push(results[i][n]); break; } } if (cacheTime > 0) { await this.setCache(sign, r); } resolve(r); }); }); }, //value返回一个值 value: async function (sql, p = [], cacheTime = 0) { let sign = this.getSign(sql, p, cacheTime); if (cacheTime > 0) { let d = await this.getCache(sign, cacheTime); if (d[0] === true) return d[1]; } return new Promise(async (resolve, reject) => { pool.query(sql, p, async (err, results) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve(''); return; } let r = ''; if (results.length > 0) { for (let n in results[0]) { r = results[0][n]; break; } if (cacheTime > 0) { await this.setCache(sign, r); } resolve(r); return; } if (cacheTime > 0) { await this.setCache(sign, r); } resolve(r); }); }); }, //map返回一列的键值对 map: async function (sql, p = [], cacheTime = 0) { let sign = this.getSign(sql, p, cacheTime); if (cacheTime > 0) { let d = await this.getCache(sign, cacheTime); if (d[0] === true) return d[1]; } return new Promise(async (resolve, reject) => { pool.query(sql, p, async (err, results) => { if (err) { try { this.log_error(err, p); } catch (e) { } if (this.debug) console.error('sql_error:', sql); resolve({}); return; } let r = {}; for (let i = 0; i < results.length; i++) { let fds = []; for (let n in results[i]) { fds.push(n); } r[results[i][fds[0]]] = results[i][fds[1]]; } if (cacheTime > 0) { await this.setCache(sign, r); } resolve(r); }); }); }, //自定义方法 get_users: function () { return ['admin', 'tom', 'jack']; } }; model.init(); module.exports = { connection: connection, pool: pool, model: model }; ```