NodeJS连接MySQL数据库并进行操作

1、安装mysql npm install mysql --save

2、使用

// <1>mysql的配置  

const mysqlConfig = {  

host:'127.0.0.1',  

port:'3306',  

user:'root',  

password:'mysql',  

database:'doubandb'  

}  

// <2>引用mysql  

const mysql = require('mysql');  

// <3> 进行连接  

const connection = mysql.createConnection(mysqlConfig);  

// <4>操作数据库  

connection.query("select * from books", (err, result) => {

      console.log(err);      

if (!err) {          

data = result;          

res.json({ data });      

            }  

});

 3、进行mysql工具的封装

<1> 定义配置信息,在config-->dbconfig.js添加mysql的配置信息

const MYSQL_CONFIG = {  

host: '127.0.0.1',  

port: '3306',  

user: 'root',  

password: 'mysql',  

database: 'doubandb'

}

 <2>封装mysqlutils.js工具

 // 引用

mysqlconst mysql = require('mysql');

const { MYSQL_CONFIG } = require('../../config/dbconfig');

// 创建连接池

const pool = mysql.createPool(MYSQL_CONFIG);

// 执行sql的方法

const exec = sql => {    return new Promise((resolve, reject) => {        

// 获取连接    

pool.getConnection((err, connection) => {

if (err) {      

// 有错误                

reject(err);          

} else {                

connection.query(sql, (err, result) => {                    

if (!err) {                        

resolve(result);                  

} else {                        

reject(err);                  

}              

});          

}            

// 当连接不再使用时，用connection对象的release方法将其归还到连接池中            connection.release();      

});  

});

}

module.exports= {    

exec

}；

 <3>在使用的地方进行调用exec，传递sql

const { exec } = require('../utils/dbutils/mysqlutils');  

exec("select * from books").then(data=>{      

res.json({data});  

});

 查询

bookRouter.get('/searchList', async (req, res, next) => {  

let sql = ` SELECT b.*,a.authorname,a.authordesc  

FROM books b, author a, author_books ab  

WHERE ab.aid = a.aid AND ab.bid = b.bid `;  

// 获取查询的参数  

const { bookname, isbn, authorname } = req.query;  

if (bookname && bookname.trim() !== '') {      

sql += ` and b.bookname like '%${bookname}%' `;  

}  

if (isbn && isbn.trim() !== '') {      

sql += ` and b.isbn = '${isbn}' `;  

}  

if (authorname && authorname.trim() !== '') {      

sql += ` and a.authorname like '%${authorname}%' `;  

}  

console.log('sql:', sql);  

exec(sql).then(data => {      

res.send(JSON.stringify({ code: 0, data }));  

});

});

 删除

bookRouter.post('/del', (req, res, next) => {  

const { bids } = req.body;// bids是数组  

if (bids && bids.length > 0) {      

const paramsStr = bids.map(bid=>`'${bid}'`);      

const params = paramsStr.join(',');      

const sql = `delete from books b where b.bid in (${params}) `;      

exec(sql).then(data => {          

res.send(JSON.stringify({ code: 0, data }));      

});  

} else {      

res.json({          

code: 0,          

message: '必须要进行选择'      

});  

}});

 修改

bookRouter.post('/modify', (req, res, next) => {  

const { bookid, bookname,isbn,publishinghouse } = req.body;  

let sql =` UPDATE books b SET b.bid = b.bid `;  

if(bookname && bookname.trim()!==''){      

sql+=` , b.bookname= '${bookname}' `  

}  

if(isbn && isbn.trim()!==''){      

sql+=`, b.isbn='${isbn}' `;

}  

if(publishinghouse && publishinghouse.trim()!==''){      

sql+=` , b.publishinghouse='${publishinghouse}' `;  

}  

sql += ` WHERE b.bid = '${bookid}' `;  

exec(sql).then(data => {      

res.send(JSON.stringify({ code: 0, data }));  

});

});

 新增方法：

bookRouter.post('/add', async (req, res, next) => {  

// 向数据库中添加数据  

let { bookname, price = 10.0, isbn='',      

dateofpublication=null, publishinghouse = '',      

producer = '', originaltitle = '', translator = '',      

numberofpages = 0, contentintroduction = '', cid = null  

} = req.body;  

if(dateofpublication!==null){      

dateofpublication = `DATE_FORMAT('${dateofpublication}','%Y-%m-%d')`;  

}  

const bid = moment().get() + '_' + (Math.random() * 100000000 + 1) + '';  

const sql = `insert into books(bid,bookname, price, isbn,dateofpublication, publishinghouse ,producer, originaltitle, translator,       numberofpages, contentintroduction, cid) values('${bid}','${bookname}',${price},       '${isbn}',${dateofpublication},'${publishinghouse}','${producer}','${originaltitle}',       '${translator}',${numberofpages},'${contentintroduction}',${cid}) `;      

exec(sql).then(data => {          

res.send(JSON.stringify({ code: 0, data }));      

});

});