The common method of JavaScript operation MySQL in Node.js _node.js

I. Establishing a database connection: CreateConnection (Object) method
This method takes an object as a parameter, and the object has four commonly used property host,user,password,database. Same as the parameters of the linked database in PHP. The properties list is as follows:

• Host: the hostname where the database is connected. (Default: localhost)
• Port: Connection ports. (Default: 3306)
• LocalAddress: The IP address used for the TCP connection. (optional)
• Socketpath: The path linking to the UNIX domain. This parameter is ignored when the host and port are used.
• User:mysql the user's user name.
• Password:mysql the user's password.
• Database: The name of the databases to which you are linking (optional).
• CharSet: The character set of the connection. (Default: ' Utf8_general_ci '. Set this value to use uppercase!)
• TimeZone: The time zone for storing local times. (Default: ' Local ')
• Stringifyobjects: Serialization of objects. Issue #501. (default: ' false ')
• Insecureauth: Whether the old authentication method is allowed to connect to the database instance. (Default: false)
• Typecast: Determines whether the column value is converted to a local JavaScript type column value. (Default: TRUE)
• Queryformat: A custom query statement format function.
• Supportbignumbers: The database handles large numbers (long and decimal) and should be enabled (default: false).
• Bignumberstrings: Enable Supportbignumbers and bignumberstrings and force these numbers to return as strings (default: false).
• Datestrings: Force Date type (TIMESTAMP, DATETIME, date) is returned as a string instead of a JavaScript date object. (Default: false)
• Debug: Whether to turn on debugging. (Default: false)
• Multiplestatements: Allow multiple query statements to be passed in query. (Default:false)
• Flags: Link flags.

You can also use strings to connect to a database, for example:

Copy Code code as follows:

var connection = mysql.createconnection (' mysql://user:pass@host/db?debug=true&charset=big5_chinese_ci& Timezone=-0700 ');

Ending database connection end () and Destroy ()
End () accepts a callback function and is triggered after query finishes, and if query fails, the link is still terminated and the error is passed to the callback function for processing.
Destroy () terminates the database connection immediately, and even if query does not complete, subsequent callback functions are not triggered.

Third, create connection pool CreatePool (Object)

The object and CreateConnection parameters are the same.
Can listen for connection events and set session values

Pool.on (' Connection ', function (connection) { 
    connection.query (' SET session auto_increment_increment=1 ') 
  }) ; 

Connection.release () release is linked to the connection pool. If you need to close the connection and delete it, you need to use Connection.destroy ()
In addition to accepting and connection the same parameters, the pool accepts several extended parameters

CreateConnection: The function used to create the link. (Default:mysql.createConnection)
Waitforconnections: Determines the behavior of the pool when there is no connection pool or the number of links is hit to the maximum value. True when the link is placed in the queue the error is returned immediately when the call is available and false. (default:true)
Connectionlimit: Maximum number of connections. (DEFAULT:10)
Queuelimit: The maximum length of the connection request in the connection pool, exceeding this length will be the error, the value of 0 without restrictions. (default:0)

Iv. Connection Pool Cluster
Allow different host links

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

Optional parameters for linked clusters

Canretry: Retry when a connection failure is allowed when the value is True (default:true)
Removenodeerrorcount: The Errorcount value increases when the connection fails. When the Errorcount value is greater than Removenodeerrorcount, a node is removed from the poolcluster. (Default:5)

• Defaultselector: Default selector. (DEFAULT:RR)
• RR: Loop. (Round-robin)
• RANDOM: Select nodes through random functions.
• Order: Unconditionally Select the first available node.

V. Switch user/change connection status

MySQL allows users to switch over disconnected

  Connection.changeuser ({User: ' john '}, function (err) { 
    if (err) throw err; 
  }); 

Parameters

• User: New users (default is one of the earlier).
• Password: New password for new user (default is the one earlier).
• CharSet: New character Set (default is the one earlier).
• DB: New database name (default is the one earlier).

Six, processing server connection disconnect

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 is reused. Connection.connect (function (ERR) {//the server is either down if (err) {//or restarting (t 
     Akes 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 ();

Seven, escape query value
to avoid SQL injection attacks, you need to escape user-submitted data. You can use Connection.escape () or Pool.escape ()
For example:

 var userId = ' Some user provided value '; 
  var sql = ' SELECT * from users WHERE id = ' + connection.escape (userId); 
  Connection.query (SQL, function (err, results) {//...}); or use? 
  As placeholder connection.query (' SELECT * from users WHERE id =? ', [UserId], function (err, results) {//...});  
  Conversion results for different types of values Numbers invariant Booleans converted to string ' true '/' false ' Date object to string ' Yyyy-mm-dd HH:ii:ss ' buffers converted to 6 string Strings unchanged Arrays => [' A ', ' B ' to ' A ', ' B ' nested arrays [[' A ', ' B '], [' C ', ' d ']] converted to (' A ', ' B '), (' C ', ' d ') obje The CTS is converted to key = ' val ' pairs. 
  The nested object is converted to a string. Undefined/null ===> null nan/infinity unchanged. 
  MySQL does not support these values, but inserting these values can cause errors unless the tool supports them. 
  Convert instance: 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 ' 

or manually converted

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

Viii. Converting query identifiers
If you cannot trust the SQL identifier (database name, table name, column name), you can use the conversion method Mysql.escapeid (identifier);

var sorter = ' Date '; 
  var query = ' SELECT * from posts ' + Mysql.escapeid (sorter); 
 
  Console.log (query); SELECT * from posts the ' date ' 
  supports escaping multiple 
  var sorter = ' Date '; 
  var query = ' SELECT * from posts ' + Mysql.escapeid (' posts. ' + sorter); 
 
  Console.log (query); SELECT * from posts the ' posts '. ' Date ' 
  can be used?? Placeholder for identifier 
  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 

IX. Preparation of inquiries
you can use Mysql.format to prepare a query statement that automatically selects the appropriate method escape parameter.

var sql = "Select * from??" WHERE?? = ?"; 
  var inserts = [' Users ', ' IDs ', userId]; 
  sql = Mysql.format (sql, inserts); 
  10, custom format functions 
  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"}); 

X. Get the ID of the inserted row
Gets the Insert row ID when using the self-added primary key, such as:

Connection.query (' INSERT into posts SET? ', {title: ' Test '}, function (err, result) { 
   if (err) throw err; 
 
   Console.log (Result.insertid); 
  }); 

Xi. Flow Processing
Sometimes you want to select a lot of rows and want to process them when the data arrives, and you can use this method

var query = connection.query (' SELECT * from posts '); 
  Query 
   . On (' Error ', function (err) { 
    //Handle error, a ' end ' event would be emitted after this as 
   OK}) 
   . On (' Fields ', function (fields) { 
    //"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 
   }); 

12. Mixed query Statement (multi-statement query)
because mixed queries are vulnerable to SQL injection and are not allowed by default, you can use the var connection = Mysql.createconnection ({multiplestatements:true}) to turn on the feature.
Mixed Query instance:

Connection.query (' SELECT 1; SELECT 2 ', function (err, results) { 
   if (err) throw err; 
 
   ' Results ' is a array with one element for every statement in query: 
   console.log (results[0]);//[{1:1}] 
   C Onsole.log (Results[1]); [{2:2}] 
  }); 

You can also use streaming to process mixed query results:

var query = Connection.query (' SELECT 1; SELECT 2 '); 
 
  Query 
   . On (' Fields ', function (fields, index) {//The fields for the "result 
    rows 
   " follow}) 
   . On (' re Sult ', function (row, index) { 
    //index refers to ' statement '-belongs to (starts at 0) 
   }); 

If one of the query statements fails, the Error object contains the ID of the err.index indicating the error statement, and the entire query terminates.
The streaming method of mixed query results is experimental and unstable.

13. Transaction Processing
simple transaction processing at the connection level

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

14. Error Handling

Err.code = String 
err.fatal => Boolean 

PS: The pit encountered when using the MySQL module
previously wrote a small program node News, used to the MySQL database, in the local test is not God horse problem. After putting the server on the run for a while, the page was accidentally found to be waiting until the Nginx returned a timeout error. So on the server checked, found that the program is still running, and can correctly record each request, and then modify the code tracking debugging, the original is in the query database, the callback has not been executed, the program hangs there.

Think for a long time also want to do not understand for God horse MySQL module did not perform a callback, finally suddenly remembered to see the error log, only to find that there is a "no reconnection after connection lost" error has not been caught, the original is the connection is lost, On the GitHub to see the following file and issues, said that the connection is missing and will not automatically reconnect, will trigger the error event. I hastened to add to the program automatically after the disconnection function, now has been normal operation for more than 10 days.

In MySQL, there is a variable called Wait_timeout, which indicates an operation timeout, and automatically closes the connection when there is no activity for more than a certain amount of time, which defaults to 28800 (that is, 8 hours).

To automatically connect to the database code:

function HandleError (err) {if
 (err) {
  //If the connection is disconnected, automatically reconnect if
  (Err.code = = ' Protocol_connection_lost ') {
   Connect ();
  } else {
   console.error (Err.stack | | err);

}}} Connection database
function connect () {
 db = mysql.createconnection (config);
 Db.connect (handleerror);
 Db.on (' Error ', handleerror);
}

var db;
Connect ();

The most popular use of the MySQL module code, often overlooked this problem, inadvertently let a dial again to the pit of people stepped.
Have children's shoes reply to ask how to use pool, so went to see the source of the MySQL module: currently available in NPM installed to the latest version of 2.0.0-alpha7, using Mysql.createpool () to create a pool can not automatically handle the connection is closed the problem, However, the version on GitHub has been repaired (it should not be posted to NPM), and when the connection error event is triggered, the connection object is removed from the connection pool. (Source code: https://github.com/felixge/node-mysql/blob/master/lib/Pool.js#L119)
Code to use 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, it has been returned to the pool.});