nodejs mysql 直连&连接池及基本操作
一、内容
使用 nodejs 链接 mysql 并执行 CRUD。
使用直连
和连接池
两种方式实现
使用 mysql 需要安装:
yarn add global mysql
二、直连
const mysql = require('mysql');
const options = {
host:'127.0.0.1',
user:'root',
password:'123456',
database:'test',
port:'3306'
};
const connection = mysql.createConnection(options);
connection.connect((err)=>{
if(err) return console.log(err);
console.log('[Mysql connect]');
});
// 添加用户
const userAddSql = `insert into user (uname,age,password) values(?,?,?)`;
let param = ["ptbird",20,"123456"];
connection.query(userAddSql,param,(err,res)=>{
if(err) return console.log(err);
console.log('[Mysql INSERT 插入结果 : success');
});
// 查询所有用户
const userSelectSql = `select * from user`;
connection.query(userSelectSql,(err,rows,fields)=>{
rows.forEach((item)=>{
console.log("[Mysql SELECT 查询结果] : " + item.uname);
});
console.log('[Mysql query result] : success');
});
// 更新用户
const userUpdateSql = 'UPDATE USER SET uname = (?) where (uid = ?) ';
connection.query(userUpdateSql,['新姓名',14],(err,fields)=>{
if(err) return console.log(err);
console.log("[Mysql UPDATE 受影响的行数] : " + fields.affectedRows);
});
// 删除用户
const userDeleteSql = 'DELETE FROM user WHERE (uid = ?)';
connection.query(userDeleteSql,[1],(err,fields)=>{
if(err) return console.log(err);
console.log("[Mysql DELETE 受影响的行数] : " + fields.affectedRows);
});
// 连接关闭
connection.end((err)=>{
if(err) return console.log(err);
console.log('[Mysql connection end]');
});
效果:
数据表:
三、连接池方式
1、封装的连接池类
this.flag
判断是否需要 connection,如果是第一次连接,则返回 pool,如果不是第一次,则不需要返回。
const mysql = require('mysql');
class MysqlPool {
constructor(){
this.flag = true;
this.pool = mysql.createPool({
host:'127.0.0.1',
user:'root',
password:'123456',
database:'test',
port:3306
});
}
getPool(){
if(this.flag){
this.pool.on('connection', (connection)=>{
connection.query('SET SESSION auto_increment_increment=1');
this.flag = false;
});
}
return this.pool;
}
}
module.exports = MysqlPool;
2、使用连接池类
pool.query
实际上是 pool.getConnection -> connection.query -> connection.release
的封装形式。
const MysqlPool = require('./mysqlPool');
const mysqlPool = new MysqlPool();
const pool = mysqlPool.getPool();
pool.query('SELECT * FROM user',(err,rows)=>{
console.log(rows[0].uid);
});
pool.getConnection((err,connection)=>{
connection.query('SELECT * FROM user where (uid = ?)',[14],(err,res)=>{
console.log(res);
});
connection.release();
});
效果:
文章已经结束啦
文章版权:Postbird-There I am , in the world more exciting!
本文链接:http://www.ptbird.cn/mysql-mysqljs-connection-pool.html
转载请注明文章原始出处 !
扫描二维码,在手机阅读!