Node.js中JavaScript操作MySQL的常用方法整理_node.js

一、建立資料庫連接：createConnection(Object)方法
      該方法接受一個對象作為參數，該對象有四個常用的屬性host，user，password，database。與php中連結資料庫的參數相同。屬性列表如下：

• host: 串連資料庫所在的主機名稱. (預設: localhost) 
• port: 串連連接埠. (預設: 3306) 
• localAddress: 用於TCP串連的IP地址. (可選) 
• socketPath: 連結到unix域的路徑。在使用host和port時該參數會被忽略. 
• user: MySQL使用者的使用者名稱. 
• password: MySQL使用者的密碼. 
• database: 連結到的資料庫名稱 (可選). 
• charset: 串連的字元集. (預設: 'UTF8_GENERAL_CI'.設定該值要使用大寫!) 
• timezone: 儲存本地時間的時區. (預設: 'local') 
• stringifyObjects: 是否序列化對象. See issue #501. (預設: 'false') 
• insecureAuth: 是否允許舊的驗證方法串連到資料庫執行個體. (預設: false) 
• typeCast: 確定是否講column值轉換為本地JavaScript類型列值. (預設: true) 
• queryFormat: 自訂的查詢語句格式化函數. 
• supportBigNumbers: 資料庫處理大數字(長整型和含小數),時應該啟用 (預設: false). 
• bigNumberStrings: 啟用 supportBigNumbers和bigNumberStrings 並強制這些數字以字串的方式返回(預設: false).  
• dateStrings: 強制日期類型(TIMESTAMP, DATETIME, DATE)以字串返回，而不是一javascript Date對象返回. (預設: false) 
• debug: 是否開啟調試. (預設: false) 
• multipleStatements: 是否允許在一個query中傳遞多個查詢語句. (Default: false) 
• flags: 連結標誌. 

還可以使用字串串連資料庫例如：

複製代碼 代碼如下:

var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700'); 

二、結束資料庫連接end()和destroy()
end()接受一個回呼函數，並且會在query結束之後才觸發，如果query出錯，仍然會終止連結，錯誤會傳遞到回呼函數中處理。
destroy()立即終止資料庫連接，即使還有query沒有完成，之後的回呼函數也不會在觸發。

三、建立串連池 createPool(Object) 

Object和createConnection參數相同。
可以監聽connection事件，並設定session值

pool.on('connection', function(connection) {     connection.query('SET SESSION auto_increment_increment=1')   }); 

connection.release()釋放連結到串連池。如果需要關閉串連並且刪除，需要使用connection.destroy()
pool除了接受和connection相同的參數外，還接受幾個擴充的參數

createConnection: 用於建立連結的函數. (Default: mysql.createConnection) 
    waitForConnections: 決定當沒有串連池或者連結數打到最大值時pool的行為. 為true時連結會被放入隊列中在可用是調用，為false時會立即返回error. (Default: true) 
    connectionLimit: 最大串連數. (Default: 10) 
    queueLimit: 串連池中串連請求的烈的最大長度，超過這個長度就會報錯，值為0時沒有限制. (Default: 0) 

四、串連池叢集
允許不同的host連結

// create   var poolCluster = mysql.createPoolCluster();    poolCluster.add(config); // anonymous group   poolCluster.add('MASTER', masterConfig);   poolCluster.add('SLAVE1', slave1Config);   poolCluster.add('SLAVE2', slave2Config);    // Target Group : ALL(anonymous, MASTER, SLAVE1-2), Selector : round-robin(default)   poolCluster.getConnection(function (err, connection) {});    // Target Group : MASTER, Selector : round-robin   poolCluster.getConnection('MASTER', function (err, connection) {});    // Target Group : SLAVE1-2, Selector : order   // If can't connect to SLAVE1, return SLAVE2. (remove SLAVE1 in the cluster)   poolCluster.on('remove', function (nodeId) {    console.log('REMOVED NODE : ' + nodeId); // nodeId = SLAVE1    });    poolCluster.getConnection('SLAVE*', 'ORDER', function (err, connection) {});    // of namespace : of(pattern, selector)   poolCluster.of('*').getConnection(function (err, connection) {});    var pool = poolCluster.of('SLAVE*', 'RANDOM');   pool.getConnection(function (err, connection) {});   pool.getConnection(function (err, connection) {});    // destroy   poolCluster.end(); 

連結叢集的選擇性參數

canRetry: 值為true時，允許串連失敗時重試(Default: true) 
    removeNodeErrorCount: 當串連失敗時 errorCount 值會增加. 當errorCount 值大於 removeNodeErrorCount 將會從PoolCluster中刪除一個節點. (Default: 5)  

•     defaultSelector: 預設選取器. (Default: RR) 
•         RR: 迴圈. (Round-Robin) 
•         RANDOM: 通過隨機函數選擇節點. 
•         ORDER: 無條件地選擇第一個可用節點. 

五、切換使用者/改變串連狀態

Mysql允許在比中斷連線的的情況下切換使用者  

  connection.changeUser({user : 'john'}, function(err) {     if (err) throw err;   }); 

參數  

•     user: 新的使用者 (預設為早前的一個). 
•     password: 新使用者的新密碼 (預設為早前的一個). 
•     charset: 新字元集 (預設為早前的一個). 
•     database: 新資料庫名稱 (預設為早前的一個). 

六、處理伺服器串連斷開

var db_config = {     host: 'localhost',     user: 'root',     password: '',     database: 'example'   };    var connection;    function handleDisconnect() {    connection = mysql.createConnection(db_config); // Recreate the connection, since                            // the old one cannot be reused.     connection.connect(function(err) {       // The server is either down     if(err) {                   // or restarting (takes a while sometimes).      console.log('error when connecting to db:', err);      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,     }                   // to avoid a hot loop, and to allow our node script to    });                   // process asynchronous requests in the meantime.                        // If you're also serving http, display a 503 error.    connection.on('error', function(err) {     console.log('db error', err);     if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually      handleDisconnect();             // lost due to either server restart, or a     } else {                   // connnection idle timeout (the wait_timeout      throw err;                 // server variable configures this)     }    });   }    handleDisconnect(); 

七、轉義查詢值
為了避免SQL注入攻擊，需要轉義使用者提交的資料。可以使用connection.escape() 或者 pool.escape()
例如：

var userId = 'some user provided value';   var sql  = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);   connection.query(sql, function(err, results) {    // ...   });   或者使用？作為預留位置   connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {    // ...   });   不同類型值的轉換結果   Numbers 不變   Booleans 轉換為字串 'true' / 'false'    Date 對象轉換為字串 'YYYY-mm-dd HH:ii:ss'   Buffers 轉換為是6進位字串   Strings 不變   Arrays => ['a', 'b'] 轉換為 'a', 'b'   嵌套數組 [['a', 'b'], ['c', 'd']] 轉換為 ('a', 'b'), ('c', 'd')   Objects 轉換為 key = 'val' pairs. 嵌套對象轉換為字串.   undefined / null ===> NULL   NaN / Infinity 不變. MySQL 不支援這些值, 除非有工具支援，否則插入這些值會引起錯誤.   轉換執行個體：   var post = {id: 1, title: 'Hello MySQL'};   var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {    // Neat!   });   console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL' 

    或者手動轉換  

  var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");    console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL' 

八、轉換查詢標識符
如果不能信任SQL標識符(資料庫名、表名、列名)，可以使用轉換方法mysql.escapeId(identifier)；

var sorter = 'date';   var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter);    console.log(query); // SELECT * FROM posts ORDER BY `date`   支援轉義多個   var sorter = 'date';   var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter);    console.log(query); // SELECT * FROM posts ORDER BY `posts`.`date`   可以使用??作為標識符的預留位置   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 

九、準備查詢
可以使用mysql.format來準備查詢語句，該函數會自動的選擇合適的方法轉義參數。

var sql = "SELECT * FROM ?? WHERE ?? = ?";   var inserts = ['users', 'id', userId];   sql = mysql.format(sql, inserts);   10、自訂格式化函數   connection.config.queryFormat = function (query, values) {    if (!values) return query;    return query.replace(/\:(\w+)/g, function (txt, key) {     if (values.hasOwnProperty(key)) {      return this.escape(values[key]);     }     return txt;    }.bind(this));   };    connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" }); 

十、擷取插入行的id
當使用自增主鍵時擷取插入行id，如：

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {    if (err) throw err;     console.log(result.insertId);   }); 

十一、流處理
有時你希望選擇大量的行並且希望在資料到達時就處理他們，你就可以使用這個方法

var query = connection.query('SELECT * FROM posts');   query    .on('error', function(err) {     // Handle error, an 'end' event will be emitted after this as well    })    .on('fields', function(fields) {     // the field packets for the rows to follow    })    .on('result', function(row) {     // Pausing the connnection is useful if your processing involves I/O     connection.pause();      processRow(row, function() {      connection.resume();     });    })    .on('end', function() {     // all rows have been received    }); 

十二、混合查詢語句（多語句查詢）
 因為混合查詢容易被SQL注入攻擊，預設是不允許的，可以使用var connection = mysql.createConnection({multipleStatements: true});開啟該功能。
 混合查詢執行個體：

connection.query('SELECT 1; SELECT 2', function(err, results) {    if (err) throw err;     // `results` is an array with one element for every statement in the query:    console.log(results[0]); // [{1: 1}]    console.log(results[1]); // [{2: 2}]   }); 

同樣可以使用流處理混合查詢結果：

var query = connection.query('SELECT 1; SELECT 2');    query    .on('fields', function(fields, index) {     // the fields for the result rows that follow    })    .on('result', function(row, index) {     // index refers to the statement this result belongs to (starts at 0)    }); 

如果其中一個查詢語句出錯，Error對象會包含err.index指示錯誤語句的id，整個查詢也會終止。
混合查詢結果的流處理方式是做實驗性的，不穩定。

十三、交易處理
connection層級的簡單交易處理

connection.beginTransaction(function(err) {    if (err) { throw err; }    connection.query('INSERT INTO posts SET title=?', title, function(err, result) {     if (err) {       connection.rollback(function() {       throw err;      });     }      var log = 'Post ' + result.insertId + ' added';      connection.query('INSERT INTO log SET data=?', log, function(err, result) {      if (err) {        connection.rollback(function() {        throw err;       });      }       connection.commit(function(err) {       if (err) {         connection.rollback(function() {         throw err;        });       }       console.log('success!');      });     });    });   }); 

十四、錯誤處理

err.code = string err.fatal => boolean 

PS：使用mysql模組時遇到的坑
之前寫了個小程式Node News，用到了MySQL資料庫，在本地測試均沒神馬問題。放上伺服器運行一段時間後，偶然發現開啟頁面的時候頁面一直處於等待狀態，直到Nginx返回逾時錯誤。於是上伺服器檢查了遍，發現程式仍然在運行，且能正確記錄每次的請求，再修改代碼跟蹤調試，原來是在查詢資料庫的時候，回調一直沒有被執行，程式就掛在那裡了。

想了很久也想不明白為神馬mysql模組沒有執行回調，最後突然想起來去看了下錯誤記錄檔，才發現有個“No reconnection after connection lost”錯誤沒有被捕捉到，原來是串連丟失了，上github上看了下文檔和issues，上面說到串連丟失後不會自動重新串連，會觸發error事件。我趕緊給程式添加了斷線後自動重連功能，現在已正常運行了10多天。

MySQL中有一個名叫wait_timeout的變數，表示操作逾時時間，當串連超過一定時間沒有活動後，會自動關閉該串連，這個值預設為28800（即8小時）。

自動重連資料庫的代碼：

function handleError (err) { if (err) {  // 如果是串連斷開，自動重新串連  if (err.code === 'PROTOCOL_CONNECTION_LOST') {   connect();  } else {   console.error(err.stack || err);  } }}// 串連資料庫function connect () { db = mysql.createConnection(config); db.connect(handleError); db.on('error', handleError);}var db;connect();

網上流傳的大多數使用mysql模組的代碼，往往忽略了這個問題，一不小心就讓一撥又一撥的人往坑裡踩了。
有童鞋回複問使用pool又會怎樣，於是去看了下mysql模組的源碼：目前可在npm中安裝到的最新版本為2.0.0-alpha7，使用mysql.createPool()來建立的pool沒辦法自動處理串連被關閉的問題，但是在github上的版本已經修複了（應該還沒發布到npm上），當觸發了connection的error事件時，會把該connection對象從串連池中移除。（源碼：https://github.com/felixge/node-mysql/blob/master/lib/Pool.js#L119 ）
使用pool的代碼：

var mysql = require('mysql');var pool = mysql.createPool(config);pool.getConnection(function(err, connection) { // Use the connection connection.query( 'SELECT something FROM sometable', function(err, rows) {  // And done with the connection.  connection.end();  // Don't use the connection here, it has been returned to the pool. });});