"Turn" Nodejs Learning Notes (iv)---interaction with MySQL (felixge/node-mysql)

Directory

• Introduction and Installation
• Test MySQL
• Meet Connection Options
• MYSQL Curd
  • Insert
  • Update
  • Inquire
  • Delete
• Nodejs call a stored procedure with an out parameter and get the return value of the out parameter
• End database connection two ways and differences
• Connection Pool Pooling connections
  • Create
  • Additional Connection pool configuration options
  • Release
  • Using the example
• Wire Break re-connect
• Other...

Introduction and Installation

node. JS interacts with MySQL with many libraries, which can be viewed in https://www.npmjs.org/search?q=mysql.

I chose Felixge/node-mysql, with more people, first conformity look at its use, not too tangled in the performance of the library between the implementation of the problem, the other library has a study of the package can also share a high performance requirements of the choice ^_^!

　　

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

Https://www.npmjs.org/package/mysql

This is a node. js driver for MySQL. It's written in JavaScript, does isn't require compiling, and is 100% MIT licensed

Implemented in plain JavaScript. MIT 100%!

　　Installation

npm install mysql

Note: Before installing the directory CD to Node.exe directory, so that the installation command will be found in the directory under Node_modules, and installed in this directory, otherwise when using MySQL, you will appear error:cannot find module ' mysql '

Test MySQL

MySQL version: 5.5

Modify and test on the basis of the official example:

　　　

test Sample source code

　　Execute successfully!

Meet Connection Options

To create a database connection, you need to understand the options first

　　Host: Hosts address (default: localhost)

　　User: Username

Password: password

　　Port: Port number (default: 3306)

　　Database: DB name

　　CharSet: Connection Character Set (default: ' Utf8_general_ci ', note character set letters are capitalized)

　　LocalAddress: This IP is used for TCP connections (optional)

　　Socketpath: Connecting to a UNIX domain path that is ignored when host and Port are used

　　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: TRUE)

　　Queryformat: Custom Query statement formatting method Https://github.com/felixge/node-mysql#custom-format

　　Supportbignumbers: You need to set this option to True when the database supports bigint or decimal type columns (default: false)

　　Bignumberstrings:supportbignumbers and bignumberstrings enable force bigint or decimal columns to be returned as JavaScript string types (default: false)

　　Datestrings: Forces the Timestamp,datetime,data type to be returned as a string type instead of a JavaScript date type (default: false)

　　Debug: Turn on Debug (default: false)

　　Multiplestatements: Whether to make a query with multiple MySQL statements (default: false)

　　Flags: Used to modify connection flags, more details: https://github.com/felixge/node-mysql#connection-flags

　　SSL: 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

Other:

You can use the URL form of the addition string, not much introduced, do not like that format, feel poor readability, also prone to error, want to know can go to the homepage to see.

MYSQL Curd

First create a test database nodesample, and build a userinfo table in the database

CREATE DATABASE IF not EXISTS nodesample CHARACTER SET UTF8; Use Nodesample; SET foreign_key_checks=0;drop TABLE IF EXISTS ' userinfo '; CREATE TABLE ' userinfo ' (  ' Id ' int (one) not NULL auto_increment COMMENT ' primary key ',  ' UserName ' varchar (UP) ' NOT null COMME NT ' user name ',  ' userpass ' varchar (UP) not NULL COMMENT ' user password ',  PRIMARY KEY (' Id ')) Engine=innodb DEFAULT Charset=utf8 C omment= ' User Information Form ';

　　1. Insert a piece of data into the UserInfo table

　　

inserting sample source code

As can be seen from the execution result, result contains some useful information, affectedrows (number of rows affected) Insertid (inserted primary key ID), etc...

With the number of rows affected and the ID of the inserted data, it is convenient to perform some subsequent operations (such as deciding whether to succeed or continue to perform other operations based on the ID of the inserted data)

Below, I go to the database server using the navicate for MySQL tool to query the UserInfo table

Insert Success!

　　2. Update the data information inserted in the 1 operation

　　

Update sample source code

The update should be correct from the number of rows affected, and we'll use a Select query example to verify that the update was successful!

　　3. Enquiry

　　

Query Sample source code

As can be seen from the results of the query, result returns a JSON-formatted data, while the second step in the update is successful!

　　4. Delete

　　

Delete Sample source code

Go to the database server and check it out.

　　

Query UserInfo table has no data, delete success!

To this node. js combined with MySQL Add, delete, change, check operation should be completed!

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

Nodejs call stored procedure is nothing special, just call with out parameter stored procedure and get out parameter return value Maybe some people are confused, here is an example to explain

Create a stored procedure with an out parameter first P_userinfo

--------------------------------Procedure structure 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;        SET extreturnval = 0;  --Failed    END; START TRANSACTION;        INSERT into UserInfo (id,username,userpass) VALUES (extid,extusername,extuserpass);                SET extreturnval = 1;        SELECT Extreturnval;        COMMIT; END;;D Elimiter;

Below, to write an example to invoke the

call stored procedure to get out parameters example source code

Go to the data for a look

　　

Insert this data correctly in the table, and correctly get the value of the out parameter, careful may find me in the stored procedure in Set Extreturnval = 1, after the success of a sentence, more than a select Extreturnval;

This allows the value of the out parameter to be queried. (There are other ways to share ^_^!)

End database connection two ways and differences

In the previous example, I call a Connection.end () method at the end, which corresponds to a connection.connect (), a start, an end!

There are two ways to end a connection: End (), destory ();

　　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 Connection pool var pool  = Mysql.createpool ({  host     : ' 192.168.0.200 ',  user     : ' Root ',  password: ' ABCD '}),//Monitor Connection Event Pool.on (' Connection ', 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)

Directly using Pool.query (' SELECT 1 + 1 as solution ', function (err, rows, fields) {  if (err) throw err;  Console.log (' The solution is: ', rows[0].solution);}); /Shared Pool.getconnection (function (err, connection) {  //connected! (Unless ' err ' is set)});

　　2. 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

　　3. Release

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

　　4. Using the example

　　

connection pooling using sample source code

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

Wire Break re-connect

The database can be connected for a variety of reasons, this must have a reconnection mechanism!

Main Judgment Errorcode:protocol_connection_lost

1. First go to the database server to stop the MySQL service

　　

2. Run the wire break to re-connect the code

　　

In the code in the Error event, determine whether the return errorcode is: Protocol_connection_lost, if it is settimeout timed 2 seconds to re-connect!

From the execution results can be seen, about 2-3 seconds will output the reconnection output information, constantly trying to reconnect!

3. Go to the data server, open the MySQL server, and then look at the results of the execution

　　

When the database server MySQL service restarts, the execution result output connection is successful, no longer output disconnection heavy days log ^_^!

Wire Break re-connect sample source codeOther...

　　1. Escape ()

Prevent SQL injection, you can use Pool.escape () and Connect.escape (), not much to say, self can try, I provide an example

　　

Escape () sample source code

The results can be seen, the 1th query splicing conditions can be executed, and escaped through the escape method after ignoring the back of the stitching part!

Can you see me in front of you? Placeholder for the way, a simple try, the benefits are not such a danger, here does not provide an example, in the code I provided above to change it can be tried out ^_^!

　　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.

　　This time also did not talk about the connection pool cluster, later have time to put out some of the missing some of the increase in the article, now the basic and MySQL interaction should be less ^_^!

　　

"Turn" Nodejs Learning Notes (iv)---interaction with MySQL (felixge/node-mysql)