nodemysql部分中文文檔

連接選項(Connection options)

在建立一個連接時，可以使用以下選項:

host：主機地址 （默認：localhost）

user：用戶名

password：密碼

port：端口號 （默認：3306）

database：數據庫名

charset：連接字符集（默認：’UTF8_GENERAL_CI’，注意字符集的字母都要大寫）

localAddress：此IP用於TCP連接（可選）

socketPath：連接到unix域路徑，當使用 host 和 port 時會被忽略

timezone：時區（默認：’local’）

connectTimeout：連接超時（默認：不限制；單位：毫秒）

stringifyObjects：是否序列化對象（默認：’false’ ；與安全相關https://github.com/felixge/node-mysql/issues/501）

typeCast：是否將列值轉化為本地JavaScript類型值 （默認：true）

queryFormat：自定義query語句格式化方法 https://github.com/felixge/node-mysql#custom-format

supportBigNumbers：數據庫支持bigint或decimal類型列時，需要設此option為true （默認：false）

bigNumberStrings：supportBigNumbers和bigNumberStrings啟用 強制bigint或decimal列以JavaScript字符串類型返回（默認：false）

dateStrings：強制timestamp,datetime,data類型以字符串類型返回，而不是JavaScript Date類型（默認：false）

debug：開啟調試（默認：false）

multipleStatements：是否許一個query中有多個MySQL語句 （默認：false）

flags：用於修改連接標志，更多詳情：https://github.com/felixge/node-mysql#connection-flags

ssl：使用ssl參數（與crypto.createCredenitals參數格式一至）或一個包含ssl配置文件名稱的字符串，目前只捆綁Amazon RDS的配置文件

　　其它：

　　可以使用URL形式的加接字符串，不多介紹了，不太喜歡那種格式，覺得可讀性差，也易出錯，想了解的可以去主頁上看。

關閉連接

可以通過兩種方式關閉數據庫連接。

通過 end() 方法按照正常狀態關閉已經完成的數據庫連接：

connection.end(function(err) {
  // The connection is terminated now
});

connection.destroy();

connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

connection.query({
  sql: 'SELECT * FROM `books` WHERE `author` = ?',
  timeout: 40000, // 40s
  values: ['David']
}, function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

connection.query({
    sql: 'SELECT * FROM `books` WHERE `author` = ?',
    timeout: 40000, // 40s
  },
  ['David'],
  function (error, results, fields) {
    // error will be an Error if one occurred during the query
    // results will contain the results of the query
    // fields will contain information about the returned results fields (if any)
  }
);

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

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"

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function(err, results) {
  // ...
});

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
connection.query(sql, function(err, results) {
  // ...
});

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

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

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

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

  console.log(result.insertId);
});

connection.query('DELETE FROM posts WHERE title = "wrong"', function (err, result) {
  if (err) throw err;

  console.log('deleted ' + result.affectedRows + ' rows');
})

connection.query('UPDATE posts SET ...', function (err, result) {
  if (err) throw err;

  console.log('changed ' + result.changedRows + ' rows');
})

connection.connect(function(err) {
  if (err) throw err;
  console.log('connected as id ' + connection.threadId);
});

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

var connection = require('mysql').createConnection({
  port: 84943, // WRONG PORT
});

connection.connect(function(err) {
  console.log(err.code); // 'ECONNREFUSED'
  console.log(err.fatal); // true
});

connection.query('SELECT 1', function(err) {
  console.log(err.code); // 'ECONNREFUSED'
  console.log(err.fatal); // true
});

connection.query('USE name_of_db_that_does_not_exist', function(err, rows) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});

connection.query('SELECT 1', function(err, rows) {
  console.log(err); // null
  console.log(rows.length); // 1
});

connection.on('error', function(err) {
  console.log(err.code); // 'ER_BAD_DB_ERROR'
});

connection.query('USE name_of_db_that_does_not_exist');