nodejs进阶(6)—连接MySQL数据库示例

npm install mysql

mysql> CREATE DATABASE mydb1;
mysql> SHOW DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| mysql       |
| mydb1       |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec) 

create table user( 
id int not null primary key auto_increment, 
name VARCHAR(100) not null, 
pwd VARCHAR(100) not null,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
CREATE UNIQUE INDEX t_quiz_IDX_0 on user(name); 

var mysql = require('mysql'); //调用MySQL模块
//创建一个connection 
var connection = mysql.createConnection({   
  host: 'localhost',    //主机 
  user: 'root',        //MySQL认证用户名 
  password: 'root',    //MySQL认证用户密码 
  database: 'mydb1', 
  port: '3306'          //端口号 
});
//创建一个connection 
connection.connect(function(err){ 
  if(err){     
    console.log('[query] - :'+err); 
    return; 
  } 
  console.log('[connection connect] succeed!'); 
}); 
//----插入
var userAddSql = 'insert into user (name,pwd) values("htmlcode">

var mysql = require('mysql'); //调用MySQL模块 
function OptPool(){ 
  this.flag=true; //是否连接过 
  this.pool = mysql.createPool({   
    host: 'localhost',    //主机 
    user: 'root',        //MySQL认证用户名 
    password: 'root',    //MySQL认证用户密码 
    database: 'test', 
    port: '3306'          //端口号 
  }); 
 
  this.getPool=function(){ 
     return this.pool; 
  } 
}; 
module.exports = OptPool;




下面的代码展示如何使用这个连接池，插入和查询的使用。需要注意的是conn.release(); //释放一个连接放回连接池 需要再操作结束后再执行，否则后面的数据库操作会报错。




var OptPool = require('./models/OptPool'); 
 
var optPool = new OptPool(); 
var pool = optPool.getPool(); 

//执行SQL语句 
//从连接池中获取一个连接
pool.getConnection(function(err,conn){ 
  //----插入 
  var userAddSql = 'insert into user (uname,pwd) values("htmlcode">

var OptPool = require('./models/OptPool'); 
 
var optPool = new OptPool(); 
var pool = optPool.getPool(); 

var insertSQL = 'insert into table1(name,pwd) values("conan","123"),("fens.me","456")';
var selectSQL = 'select * from table1 limit 10';
var deleteSQL = 'delete from table1';
var updateSQL = 'update table1 set name="conan update" where name="conan"';

pool.getConnection(function(err,conn){ 
//delete
  conn.query(deleteSQL, function (err0, res0) {
    if (err0) console.log(err0);
    console.log("DELETE Return ==> ");
    console.log(res0);

    //insert
    conn.query(insertSQL, function (err1, res1) {
      if (err1) console.log(err1);
      console.log("INSERT Return ==> ");
      console.log(res1);

      //query
      conn.query(selectSQL, function (err2, rows) {
        if (err2) console.log(err2);

        console.log("SELECT ==> ");
        for (var i in rows) {
          console.log(rows[i]);
        }

        //update
        conn.query(updateSQL, function (err3, res3) {
          if (err3) console.log(err3);
          console.log("UPDATE Return ==> ");
          console.log(res3);

          //query
          conn.query(selectSQL, function (err4, rows2) {
            if (err4) console.log(err4);

            console.log("SELECT ==> ");
            for (var i in rows2) {
              console.log(rows2[i]);
            }
          });
        });
      });
    });
  });
})



以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。