Nodejs notes--mysql (iv)

Test connection

varMySQL = require (' mysql ');//call the MySQL module//Create a connectionvarConnection =mysql.createconnection ({host:' localhost ',//HostUser: ' Root ',//MySQL authenticated user namePassword: ",//MySQL authenticated user passwordPort: ' 3306 ',//Port number}); //Create a connectionConnection.connect (function(err) {if(Err) {Console.log (' [query]-: ' +err); return; } console.log (' [Connection connect] succeed! ');}); //Execute SQL statementConnection.query (' SELECT 1 + 1 as solution ',function(Err, rows, fields) {if(Err) {Console.log (' [query]-: ' +err); return; } console.log (' The solution is: ', rows[0].solution);  }); //Close ConnectionConnection.end (function(err) {if(err) {return; } console.log (' [Connection end] succeed! ');});

Parameters

To Create a database connection, you need to understand the options host: Host address (default: localhost) User: username password: Password port: Port number (default:320WDB: Database name CharSet: Connection Character Set (default:' Utf8_general_ci ', note that the characters in the character set are capitalized) localaddress: This IP is used for TCP connections (optional) Socketpath: Connect to the UNIX domain path, which is ignored when using host and port timezone: Time zone (default:' Local ') ConnectTimeout: Connection timeout (default: No Limit; units: milliseconds) Stringifyobjects: whether to serialize the object (default:' false '; security-related https://github.com/felixge/node-mysql/issues/501)typecast: Whether to convert column values to local JavaScript type values (default:trueQueryformat: Custom Query Statement Format method https://Github.com/felixge/node-mysql#custom-formatsupportbignumbers: You need to set this option to True when the database supports bigint or decimal type columns (default:falseBignumberstrings:supportbignumbers and bignumberstrings enable force bigint or decimal columns to be returned as JavaScript string types (default:falsedatestrings: Forces the Timestamp,datetime,data type to be returned as a string type instead of a JavaScript date type (default:falseDebug : Turn on debug (default:falsemultiplestatements: Do you want to have multiple MySQL statements in a query (default:falseFlags: Used to modify connection flags, more details: https://Github.com/felixge/node-mysql#connection-flagsSSL: Using the SSL parameter (one to the crypto.createcredenitals parameter format) or a string containing the SSL profile name, only the configuration file for Amazon RDS is currently bundled

Create a database

CREATE DATABASE IF  not EXISTSNodesampleCHARACTER SETUTF8; Usenodesample;SETForeign_key_checks=0;DROP TABLE IF EXISTS' userinfo ';CREATE TABLE' userinfo ' (' Id ')int( One) not NULLAuto_increment COMMENT'PRIMARY Key', ' UserName 'varchar( -) not NULLCOMMENT'User name', ' Userpass 'varchar( -) not NULLCOMMENT'User Password',  PRIMARY KEY(' Id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 COMMENT='User Information Table';

Sql

{    insert:' INSERT into user (ID, name, age) VALUES (0,?,?) ' ,    Update:' update user set name=? ', age=? where id=? ' ,     Delete: ' Delete from user where id=? ' ,    ' SELECT * from user where id=? ' ,    ' SELECT * from user '}

Nodejs call a stored procedure with an out parameter and get the return value of the out parameter

create a stored procedure with an out parameter P_userinfo  for ' P_userinfo '------------------------------DROP PROCEDURE IF EXISTS ' p_userinfo ';D elimiter;; CREATE Definer= ' root ' @ ' localhost ' PROCEDURE ' p_userinfo ' (in Extid int,in extusername VARCHAR (+), in Extuserpass VARCHAR (+), out Extreturnval INT) top:begindeclare EXIT HANDLER for SQLEXCEPTION    BEGIN        ROLLBACK;         = 0;  -- Failed    END; START TRANSACTION;        INSERT into UserInfo (id,username,userpass) VALUES (extid,extusername,extuserpass);                 = 1;        SELECT Extreturnval;        COMMIT; END;;D Elimiter;

calledvarMySQL = require (' MySQL '); varConnection =mysql.createconnection ({host:' localhost ', User:' Root ', Password:‘‘, Port:' 3306 ', Database:' Nodesample '  }); Connection.connect ();varUserproc = ' Call P_userinfo (?,?,?, @ExtReturnVal); ';varUserproc_params = [0, ' finder ', ' ABCD '];//call a stored procedureConnection.query (Userproc,userproc_params,function(Err, retsult) {if(Err) {Console.log (' [EXEC PROC ERROR]-', Err.message); return; } console.log ('--------------------------PROC----------------------------');              Console.log (Retsult); Console.log (retsult[0][0].       Extreturnval); Console.log ('-----------------------------------------------------------------\ n '); }); Connection.end ();

End database connection two ways and differences

End ()

The end () method executes after queries, the end () method receives a callback function, queries performs an error, still ends the connection, and the error returns to the callback function err parameter, which can be handled in the callback function!

　　Destory ()

Compare violence, no callback function, immediate execution, no matter if queries is complete!

Connection Pool Pooling connections

1. Connection pool creation, using the CreatePool method, options and createconntion consistent, you can listen to connection events.

var mysql = require (' mysql '); // Create a connection pool var pool  = Mysql.createpool ({  host     ' 192.168.0.200 ',  user     ' root ' ,  ' ABCD '}); // Monitoring Connection Events function (connection) {      connection.query (' SET SESSION auto_increment_increment=1 ');});

Connection pooling can be used directly, or you can share a connection or manage multiple connections (referencing the official sample)

// Direct Use function (Err, rows, fields) {  ifthrow  err;  Console.log (' The solution is: ', rows[0].solution);}); // shared pool.getconnection (function(err, connection) {  //  connected! (Unless ' err ' is set)});

Additional Connection pool configuration options

Waitforconnections

When the connection pool is not connected or exceeds the maximum limit, set to true and the connection is placed in the queue, set to False to return error

Connectionlimit

Number of connections limit, default: 10

Queuelimit

Maximum connection request queue limit, set to 0 to indicate unrestricted, default: 0

Release

Calling the Connection.release () method will put the connection back into the connection pool and wait for other users to use it!

Using the example

varMySQL = require (' MySQL ');varPool =Mysql.createpool ({host:' 192.168.0.200 ', User:' Root ', Password:' ABCD ', Port:' 3306 ', Database:' Nodesample '});p Ool.getconnection (function(err, connection) {Connection.query (' SELECT * from UserInfo; ',function(err, result) {Console.log (result);    Connection.release ();    }); Connection.query (' SELECT * from UserInfo; ',function(err, result) {Console.log (result);    Connection.release (); });});

In the actual development process, should still use the connection pool way better!

Wire Break re-connect

Main Judgment Errorcode:protocol_connection_lost

varMySQL = require (' MySQL ');varDb_config ={host:' localhost ', User:' Root ', Password:‘‘, Port:' 3306 ', Database:' Nodesample '  };varconnection;functionHandledisconnect () {connection=mysql.createconnection (db_config); Connection.connect (function(err) {if(Err) {Console.log ("To make a wire break, re-connect:" +NewDate ()); SetTimeout (Handledisconnect,2000);//2 seconds to re-connect once      return; } console.log ("Connection succeeded");                                                                             }); Connection.on (' Error ',function(Err) {Console.log (' DB error ', err); if(Err.code = = = ' Protocol_connection_lost ')) {handledisconnect (); } Else {                                            Throwerr; }  });} Handledisconnect ();

Other

1.escape Escape , preventing SQL injection

varMySQL = require (' MySQL ');varPool =Mysql.createpool ({host:' localhost ', User:' Root ', Password:‘‘, Port:' 3306 ', Database:' Nodesample '});p Ool.getconnection (function(err,connection) {connection.query (' SELECT * from userinfo WHERE id = ' + ' 5 OR id = 6 ',function(err,result) {//Console.log (err);Console.log (Result);    Connection.release ();    }); Connection.query (' SELECT * from userinfo where id = ' + pool.escape (' 5 OR id = 6 '),function(err,result) {//Console.log (err);Console.log (Result);    Connection.release (); });})

2.mysql.escapeid (identifier)

If you cannot trust the SQL identifier (database name, column name, table name) that is prompted by the user, you can use this method, which is officially provided with an example (the most common is the sort by column name ...). ）

　　3.MySQL. Format

　　To prepare the query, the function chooses the appropriate escape method to escape the parameter

....

There are some security-related ways to see the official notes for yourself.

Nodejs notes--mysql (iv)