穩(wěn)定版 ： npm install mysql --save
安裝最新版本： npm install felixge/node-mysql

連接數(shù)據(jù)庫

var mysql = require("mysql");
 
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'pwd',
  port: 3306,//端口
  database: '數(shù)據(jù)庫名'
});

關(guān)閉數(shù)據(jù)庫連接,請求結(jié)束應(yīng)當(dāng)關(guān)閉

connection.end(function(err) {});//已添加sql語句不會結(jié)束

connection.destroy();//銷毀，方法會即刻執(zhí)行，沒有callback

//增刪改查的一些回調(diào)數(shù)據(jù)
result.insertId;//插入有自增主鍵的表，可以這樣獲得插入的ID：
result.affectedRows;//插入盒犹，更新或刪除語句受影響的行數(shù)
result.changedRows ;//更新語句被改變的行數(shù)。
connection.threadId;//獲取MySQL連接ID（“thread ID”）票髓。

增

var usr={name:'x',password:'pwd',mail:'x@tk.cn'};
connection.query('insert into users set ?', usr, function(err, result) {
    if (err) throw err;
    console.log(result,result.insertId);//result.insertId插入行id

});

刪

connection.query('delete from  users where name="x"', function(err, result) {
    if (err) throw err;
    console.log(result);
});

改

connection.query('UPDATE users SET foo = ?, bar = ? WHERE id = ?', ['a', 'b', '1'], function (error, results, fields) {
  if (error) throw error;
  // ... 
});

查

#ORDER BY `time`(或其他字段) DESC(倒序毅人，ASC正序(默認(rèn)))

//1.普通方法
connection.query('select * from users where name="x"  and `pwd`= "123"', function(err, rows, fields) {
    if (err) throw err;
    for(var i= 0,usr;usr=rows[i++];){
        console.log('user nae='+usr.name + ', password='+usr.password);
    }
});

//2.占位符 .query(sqlString, values, callback)
connection.query({sql: 'SELECT * FROM `books` WHERE `x` = ?' },['David'],function (error, results, fields) {

});

//3. .query(options,values,callback):values會覆蓋options.values
connection.query({
    sql: 'SELECT * FROM `books` WHERE `author` = ?',
    timeout: 40000, // 40s
    values:['David'],
  },  ['David'],  function (error, results, fields) {

  }
);

//4. ??，應(yīng)該是進(jìn)行編碼
var userId = 1;
var columns = ['username', 'email'];
var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err, results) {
  // ...
});
console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

防止SQL注入, 對用戶提供的數(shù)據(jù)插入查詢語句前應(yīng)該總是對其進(jìn)行編碼 宅倒。

mysql.escape(), 
connection.escape(),
pool.escape()

var sql = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);

占位符默認(rèn)使用connection.escape()

err:

err.code: 
err.fatal: 布爾值, 指出這個錯誤是否是終端連接對象攘宙。

//監(jiān)聽錯誤
connection.on('error', function(err) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});

var express = require("express");
var mysql = require("mysql");
var server = express();

function mysqlFn(){
    var sql = mysql.createConnection({
        host: 'tk.cn',
        user: 'root',
        password: '',
        database: 'data',
        port: 3306
    })
    sql.connect();
    return sql
}
server.get("/", function(req, res) {
    var sql=mysqlFn();
        sql.query("SELECT * FROM `h` ORDER BY id DESC", function(err, rows, fields) {//"SELECT * FROM `h` where id=1 ORDER BY id DESC"
        if (err) {
            console.log(err);
             res.end();
        } else {
            res.header("charset", "utf-8");
            res.send(rows);
            sql.end();
            console.log(rows);
        }
    });
})
server.get("/set", function(req, res) {
    var sql=mysqlFn();
        sql.query("update `h` set name = 'x' where id=1", function(err, rows, fields) {
        if (err) {
            console.log(err);
            res.end();
        } else {
            res.header("charset", "utf-8");
            res.send(rows);
            sql.end();
//{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"(Rows matched: 1  Changed: 1  Warnings: 0","protocol41":true,"changedRows":1}
            console.log(rows);
        }
       
    });
})
server.get("/del", function(req, res) {
   var sql=mysqlFn();
        sql.query("delete from h where name='h' ", {},function(err, rows, fields){
        if (err) {
            console.log(err);
            res.end();
        } else {//"affectedRows":2,行數(shù)
// {"fieldCount":0,"affectedRows":2,"insertId":0,"serverStatus":34,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
            res.header("charset", "utf-8");
            res.send(rows);
            sql.end();
            console.log(rows);
        }
    })
})
server.get("/insert", function(req, res) {
    var sql=mysqlFn();
        sql.query("insert into h set ?", {
        "name": "h"
    }, function(error, results, fields) {
        if (error) {
            res.send(error);
        } else {
            res.send(results);
            sql.end();
// {"fieldCount": 0,"affectedRows": 1,"insertId": 4,"serverStatus": 2,"warningCount": 0,"message": "","protocol41": true,"changedRows": 0}
        }
    })
})
server.listen(8080);

原文：http://blog.csdn.net/huangxiongbiao/article/details/46116901