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

});
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) {								 
 4/6   首页 上一页 2 3 4 5 6 下一页 尾页