index.js --- mysql操作封装
//引入mysql
const mysql = require("mysql")
// 数据库参数
var conn = mysql.createConnection({
host: '127.0.0.1',
user: 'root',
password: 'root',
database: '***',
port: 3306
})
//连接数据库
conn.connect(function(err) {
if (!err) {
console.log("数据库连接成功")
}
})
// mysql操作--查询
let Select = (table, callback) => {
var sql = `select * from ${table}`
conn.query(sql, (err, result) => {
if (err) {
console.log('[错误]' + err);
return;
}
var string = JSON.stringify(result);
var data = JSON.parse(string);
callback('', data);
})
}
// mysql操作--指定查询
let Find = (table, field, find, callback) => {
if (!isNaN(parseFloat(find))) {
var sql = `select * from ${table} where ${field} = ${find}`
} else {
var sql = `select * from ${table} where ${field} = '${find}'`
}
conn.query(sql, (err, result) => {
if (err) {
console.log('[错误]' + err);
return;
}
var string = JSON.stringify(result);
var data = JSON.parse(string);
callback('', data);
})
}
// mysql操作--删除数据
let Delete = (table, field, find, callback) => {
if (!isNaN(parseFloat(find))) {
var sql = `delete from ${table} where ${field} = ${find}`
} else {
var sql = `delete from ${table} where ${field} = '${find}'`
}
conn.query(sql, (err, result) => {
if (err) {
console.log('[错误]' + err);
return;
}
callback('', "删除成功");
})
}
// mysql操作--修改数据
let Update = (table, data, field, find, callback) => {
let skey = []
let sparse = []
let arrtext = []
for (key in data) {
if (key != 'Id') {
skey.push(key)
}
}
for (var i = 0; i < skey.length; i++) {
var keys = skey[i]
var text = data[skey[i]]
sparse.push(`${text}`)
arrtext.push(`${keys}=?`)
}
if (!isNaN(parseFloat(find))) {
var sql = `update ${table} set ${arrtext} where ${field} = ${find}`
} else {
var sql = `update ${table} set ${arrtext} where ${field} = '${find}'`
}
conn.query(sql, sparse, (err, result) => {
if (err) {
console.log('[错误]' + err);
return;
}
callback('', "修改成功");
})
}
// mysql操作--新增数据
let Insert = (table, data, callback) => {
let skey = []
let sparse = []
let arrtext = ''
for (key in data) {
if (key != 'Id') {
skey.push(key)
}
}
//这里使用为json格式,可以在下面代码更改其他类型格式
for (var i = 0; i < skey.length; i++) {
sparse.push(data[skey[i]])
if (i != skey.length - 1) {
arrtext = arrtext + '?,'
} else {
arrtext = arrtext + '?'
}
}
skey = skey.toString()
var sql = `insert into ${table}(${skey}) values (${arrtext})`
conn.query(sql, sparse, (err, result) => {
if (err) {
console.log('[错误]' + err);
return;
}
callback('', "上传成功");
})
}
exports.S = Select;
exports.F = Find;
exports.D = Delete;
exports.U = Update;
exports.I = Insert;
使用方式:
var setMysql = require("./db/index.js");//引入
//示例样式
// 查询表
setMysql.S('vi_user', (err, rest) => {
if(err){
console.log(err)
}
if(rest.length){
console.log(rest)
}
})
// 指定查询
setMysql.F('vi_user','username','sa', (err, rest) => {
if (err) {
console.log(err)
}
if (rest.length) {
console.log(rest)
}
})
// 删除
setMysql.D('vi_user','username','sa', (err, rest) => {
if (err) {
console.log(err)
}
if (rest.length) {
console.log(rest)
}
})
// 修改数据
// 文本需要加上双引号''
var db = ["password='open'","grouplist='12367'"]
console.log(db)
setMysql.U('vi_user',db,'Id','1', (err, rest) => {
if (err) {
console.log(err)
}
if (rest.length) {
console.log(rest)
}
})
// 新增数据
var db = ["username = 'open'","password = '234'","power = '普通用户'"]
setMysql.setInsert('vi_user',db, (err, rest) => {
if (err) {
console.log(err)
}
if (rest.length) {
console.log(rest)
}
})
新增实例:
// 新增信息
app.post('/api/UserAdd', (req, res) => {
var data = req.body
// 新增数据
setMysql.I('vi_user', data, (err, rest) => {
if (err) {
console.log(err)
}
res.send(rest)
})
});
注意:所有的data数据均为json格式,如若是其他格式可以自行更改。