Node.js_node.js에서 JavaScript를 사용하여 MySQL을 작동하는 일반적인 방법

1. 데이터베이스 연결 설정: createConnection(Object) 메소드
이 방법은 객체를 매개변수로 받아들입니다. 객체에는 일반적으로 사용되는 4가지 속성인 호스트, 사용자, 비밀번호 및 데이터베이스가 있습니다. PHP의 데이터베이스 링크와 동일한 매개변수입니다. 속성 목록은 다음과 같습니다.

• host: 데이터베이스가 연결된 호스트 이름 (기본값: localhost)
• 포트 : 연결 포트 (기본값 : 3306)
• localAddress: TCP 연결에 사용되는 IP 주소(선택 사항)
• socketPath: Unix 도메인의 경로입니다. 호스트와 포트를 사용할 때 이 매개변수는 무시됩니다.
• user: MySQL 사용자의 사용자 이름
• password: MySQL 사용자의 비밀번호
• 데이터베이스: 연결할 데이터베이스의 이름(선택 사항)
• charset: 연결의 문자 집합입니다. (기본값: 'UTF8_GENERAL_CI'. 이 값을 설정할 때는 대문자를 사용하세요!)
• timezone: 현지 시간이 저장되는 시간대입니다. (기본값: 'local')
• stringifyObjects: 객체 직렬화 여부. 문제 #501을 참조하세요. (기본값: 'false')
• insecureAuth: 기존 인증 방법을 데이터베이스 인스턴스에 연결하도록 허용할지 여부(기본값: false)
• typeCast: 열 값을 기본 JavaScript 유형 열 값으로 변환할지 결정합니다. (기본값: true)
• queryFormat: 사용자 정의 쿼리문 형식 지정 기능
• supportBigNumbers: 데이터베이스가 큰 숫자(긴 정수 및 소수)를 처리하는 경우 활성화되어야 합니다(기본값: false).
bigNumberStrings: supportBigNumbers 및 bigNumberStrings를 활성화하고 이러한 숫자를 문자열로 강제로 반환합니다(기본값: false).
• dateStrings: 날짜 유형(TIMESTAMP, DATETIME, DATE)이 자바스크립트 Date 객체 대신 문자열로 반환되도록 합니다(기본값: false)
• debug: 디버깅 활성화 여부(기본값: false)
• multipleStatements: 하나의 쿼리에 여러 쿼리 문을 전달할 수 있는지 여부(기본값: false)
• 플래그: 링크 플래그
문자열을 사용하여 데이터베이스에 연결할 수도 있습니다. 예:

코드 복사 코드는 다음과 같습니다.

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

2. 데이터베이스 연결 end() 및 destroy()를 종료합니다
end()는 콜백 함수를 받아들이고 쿼리가 끝난 후에 실행됩니다. 쿼리에 오류가 있으면 링크는 계속 종료되고 오류는 처리를 위해 콜백 함수로 전달됩니다.
destroy()는 쿼리가 완료되지 않더라도 후속 콜백 함수가 트리거되지 않고 즉시 데이터베이스 연결을 종료합니다.
3. 연결 풀 생성 createPool(Object)

Object 및 createConnection 매개변수는 동일합니다.

연결 이벤트를 듣고 세션 값을 설정할 수 있습니다

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

connection.release()는 연결 풀에 대한 연결을 해제합니다. 연결을 종료하고 삭제해야 하는 경우에는 Connection.destroy()를 사용해야 합니다

풀은 연결과 동일한 매개변수를 허용하는 것 외에도 여러 확장 매개변수도 허용합니다

createConnection: 링크를 생성하는 데 사용되는 함수(기본값: mysql.createConnection)

WaitForConnections: 연결 풀이 없거나 연결 수가 최대값에 도달할 때 풀의 동작을 결정합니다. true인 경우 연결이 대기열에 들어가고 사용 가능한 경우 호출됩니다. (기본값: true)

ConnectionLimit: 최대 연결 수(기본값: 10)
QueueLimit: 연결 풀의 최대 연결 요청 길이입니다. 이 길이를 초과하면 오류가 보고됩니다. 값이 0이면 제한이 없습니다.

4. 연결 풀 클러스터

다른 호스트 링크 허용

// 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인 경우 연결 실패 시 재시도를 허용합니다. (기본값: true)

RemoveNodeErrorCount: 연결이 실패하면 errorCount 값이 증가합니다. errorCount 값이 RemoveNodeErrorCount보다 크면 PoolCluster에서 노드가 제거됩니다. (기본값: 5)

defaultSelector: 기본 선택기(기본값: RR)

• RR: 루프(라운드 로빈)
• RANDOM: 랜덤 기능을 통해 노드를 선택합니다.
• ORDER: 사용 가능한 첫 번째 노드를 무조건 선택합니다.

5. 사용자 전환/연결 상태 변경

Mysql에서는 연결 끊김 없이 사용자 전환이 가능합니다.

参数  

•     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.
 });
});