MySQL usage in Nodejs

1. Establish database connection: CreateConnection (Object) method
The method takes an object as a parameter, which has four commonly used property host,user,password,database. Same as the parameters of the linked database in PHP. The list of attributes is as follows:

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

You can also use strings to connect to a database such as:

var connection = mysql.createconnection (' Mysql://user:[email protected]/db?debug=true&charset=big5_chinese_ci &timezone=-0700 ');  

2. Ending database connection end () and Destroy ()
End () accepts a callback function, and it is triggered after the query ends, and if query is faulted, the link is still terminated and the error is passed to the callback function for processing.

Destroy () terminates the database connection immediately, even if a query is not completed, and then the callback function is not triggered.

3. Create Connection Pool CreatePool (object) object and CreateConnection parameter 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 () Releases the link 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 also accepts several extended parameters

1. CreateConnection: The function used to create the link. (Default:mysql.createConnection)
2. Waitforconnections: Determines the behavior of the pool when there are no connection pools or when the number of links hits the maximum value. when True, the link is put in the queue when it is called, false when it is available, and returns error immediately. (Default: true)
3. Connectionlimit: Maximum number of connections. (DEFAULT:10)
4. Queuelimit: The maximum length of the connection request in the connection pool, exceeding this length will be an error, with a value of 0 without a limit. (default:0)

4. 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: True when a connection failure is allowed to retry (default:true)    Removenodeerrorcount: The Errorcount value increases when the connection fails. When the Errorcount value is greater than Removenodeerrorcount will remove a node from the Poolcluster. (Default:5)    Defaultselector: Default selector. (DEFAULT:RR)        RR: Loop. (Round-robin)        Random: Selects a node through a stochastic function.        ORDER: Unconditionally Select the first available node.

5. Switch User/change connection status

MySQL allows you to switch user    Connection.changeuser ({User: ' john '}, function (err) {          if (err) throw err;    }) in the case of a disconnected connection.

Parameter user      : The new user (the default is the earlier one).      Password: The new password for the new user (the default is the earlier one).      CharSet: The new character set (the default is the earlier one).      Database: The name of the new databases (the default is the earlier one).  

6. Processing server Connection disconnected

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 (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 loo                                     P, and to allow we 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 ();

7. Escaping query values

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 of different type values Numbers invariant Booleans converted to string ' true '/' false ' Date object converted to string ' Yyyy-mm-dd HH:ii:ss ' buffers converted to is 6 binary string STRINGS constant Arrays = [' A ', ' B '] is converted to ' a ', ' B ' nested arrays [[' A ', ' B '], [' C ', ' d ']] converted to (' A ', ' B '), (' C ', ' d ') obje The CTS converts to key = ' val ' pairs.    The nested object is converted to a string. Undefined/null ===> null nan/infinity unchanged.    MySQL does not support these values, and inserting these values will cause an error unless there is a tool support.    Conversion 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 convert var query = "SELECT * from posts WHERE title="+ mysql.escape (" Hello MySQL "); Console.log (query); SELECT * from posts WHERE title= ' Hello MySQL '

8. Convert 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 ORDER by ' + Mysql.escapeid (sorter);    Console.log (query); SELECT * from posts ORDER by ' date '    supports escaping multiple    var sorter = ' Date ';    var query = ' SELECT * ' from posts ORDER by ' + Mysql.escapeid (' posts. ' + sorter);    Console.log (query); SELECT * from posts ORDER by ' posts '. ' Date '    can be used?? The placeholder as the 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

9. Prepare for Enquiry
You can use Mysql.format to prepare the query statement, which automatically selects the appropriate method to escape the parameter.

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

11. Get the ID of the inserted row
Gets the insert row ID when using the self-increment primary key, such as:

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

12. Stream Processing
Sometimes you want to select a large number of rows and want to process them when the data arrives, you can use this method

var query = connection.query (' SELECT * from posts ');    Query      . On (' Error ', function (err) {        ///* Handle error, an ' end ' event would be ' emitted "after"      )      . 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 has been received      });

13. Mixed query Statement (multi-statement query)
Because hybrid queries are susceptible to SQL injection attacks, they are not allowed by default and can be used with var connection = Mysql.createconnection ({multiplestatements:true});
Mixed Query instances:

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

You can also use the stream 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, this follow        })        . On (' Res Ult ', function (row, index) {          //index refers to the statement this result belongs to (starts at 0)        });  

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

14. Transaction Processing
Connection-level simple transaction processing

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

5. Error handling

1. Err.code = string
2. Err.fatal = Boolean

Original: http://blog.csdn.net/zxsrendong/article/details/17006185
For more detailed instructions please see: https://github.com/felixge/node-mysql

MySQL usage in Nodejs