Nodejs MySQL Data query example

1, installation Nodejs

2. Install MySQL NPM package

Address: Https://github.com/felixge/node-mysql

NPM install MySQL

3, the corresponding API view and call:

var mysql      = require (' mysql '); var connection = mysql.createconnection ({  host     ' localhost ',  user     ' me ' ,  ' Secret '}); Connection.connect (); Connection.query (function(err, rows, fields) {   ifthrow  err;  Console.log (' The solution is: ', rows[0].solution);}); Connection.end ();

4, the relevant points of attention and methods

Save the corresponding results in file format using the fs file system and path path module

var $ = require (' underscore '); var fs = require (' FS '); var path = require (' path ');

Underscore is a collection of data processing, which can be easily and quickly processed by traversing and splicing.

NPM Install underscore

Connection.query (' select * from Xmmember ',function(Err, rows) {//account user Accounts table    if(ERR)Throwerr; varTempaccount = $.map (rows,function(c) {return{id:c.id, name:c.m_username, Password:c.m_userpass, Surepassword:c.    M_userpass, QQ:C.QQ}}); Fs.writefile (Path.join (__dirname,' Account.js '), Json.stringify (Tempaccount),function(err) {if(ERR)Throwerr; Console.log ("Export account success!"); });});

After the data query result is converted in JSON format, it is exported to a specific file, which facilitates the management of the corresponding system for import.

Related API methods:

1, configure the query format, the corresponding update operation, the code is as follows:

 Connection.config.queryFormat = function   (Query, values) { if  (!values) return   query;  return  query.replace (/\:(\w+)/g,   (TXT, key) { if   (Values.hasownproperty (key)) { return      This  .escape (Values[key]);   return   txt; }.bind ( this   "UPDATE posts SET title =: Title", {title: "Hello MySQL"}); 

2. Perform insert operation. As follows:

function (err, result) {  ifthrow  err;  Console.log (Result.insertid);});

3. Delete execution, sample code below:

function (err, result) {  ifthrow  err;  Console.log (' deleted ' + result.affectedrows + ' rows ');})

4, a large number of data query and operation, you can add the corresponding fault-tolerant processing: The sample code is as follows:

varquery = Connection.query (' SELECT * from posts ')), query. On (' Error ',function(err) {//Handle error, an ' end ' event would 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/OConnection.pause (); Processrow (Row,function() {connection.resume ();  }); }). On (' End ',function() {    //All rows are been received});

Where err is the error handling that is required in the event of a specific error.

fields for the specific execution of the result set

Row is the processing of each row of a specific result set, which can be paused and resumed.

5, more than one query to execute processing, the sample code is as follows:

Configure the following first:

var true});

The execution of multiple statements can be done as follows:

function (err, results) {  ifthrow  err;   // ' Results ' is a array with one element for every statement in the query:  // [{1:1}]  // [{2:2}]});

6, the result of assembly processing, the code is as follows:

var true  function(err, results) {  /* results'll be is an array like this now :  [{ C9/>table1: {      fielda: ' ... ',      fieldb: ' ... ',    },    table2: {      fielda: ' ... ',      fieldb: ' ... ',    },  }, ...]   */ });

Or use the following methods:

var options = {sql: ' ... ', Nesttables: ' _ 'function(err, results) {  /* Results'll be a array like this now :  [{    Table1_fielda: ' ... ',    table1_fieldb: ' ... ',    table2_fielda: ' ... ',    table2_fieldb: '  ... ', }, ...]   */ });

7, the implementation of things and treatment. The code is as follows:

Connection.begintransaction (function(err) {if(ERR) {Throwerr;} Connection.query (' INSERT into posts SET title=? ', title,function(err, result) {if(Err) {Connection.rollback (function() {        Throwerr;    }); }    varLog = ' Post ' + result.insertid + ' added '; Connection.query (' INSERT into log SET data=? ', log,function(err, result) {if(Err) {Connection.rollback (function() {          Throwerr;      }); } connection.commit (function(err) {if(Err) {Connection.rollback (function() {            Throwerr;        }); } console.log (' Success! ');    });  }); });});

BeginTransaction (), commit () and rollback () Three methods for the start of SQL things, Commit, rollback three operations.

8, the execution time-out capture processing, the sample code is as follows:

function (err, rows) {  if (err && err.code = = = ' Protocol_sequence_timeout ') {      ThrowNew Error (' too long to count table rows! ' );  }   if (Err) {    throw  err;  }  Console.log (rows[0].count + ' rows ');});

9, you can also handle the SQL error code, the sample code is as follows:

 var  connection = require (' mysql '  console.log (err.fatal); //   ' SELECT 1 ', function   //  ' econnrefused '  console.log (err.fatal); // }); 

Finally, node MySQL provides powerful data pool processing, as well as SSL encryption verification capabilities, where you can view specific APIs without a description.

This document refers to the API to write a brief, if there are errors, please forgive.

Nodejs MySQL Data query example