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(?,?)';var param = ['fff','123'];connection.query(userAddSql,param,function(err,rs){  if(err){    console.log('insert err:',err.message);    return;  }    console.log('insert success');});//执行查询 connection.query('SELECT * from user where id=?',[2], function(err, rs) {   if (err) {     console.log('[query] - :'+err);     return;   }   for(var i=0;i<rs.length;i++){    console.log('The solution is: ', rs[i].uname);   }});  //关闭connection connection.end(function(err){   if(err){     console.log(err.toString());    return;   }   console.log('[connection end] succeed!'); });

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;

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(?,?)';   var param = ['eee','eee'];  conn.query(userAddSql,param,function(err,rs){    if(err){       console.log('insert err:',err.message);       return;     }     console.log('insert success');     //conn.release(); //放回连接池  })  //查询   conn.query('SELECT * from user', function(err, rs) {     if (err) {       console.log('[query] - :'+err);       return;     }      for(var i=0;i<rs.length;i++){      console.log(rs[i].uname);     }    conn.release(); //放回连接池  });});

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]);            }          });        });      });    });  });})