Nodemysql Chinese Document

Nodemysql Chinese Document

This is based on part of the Chinese translation of the node-mysql official document. As mysql is to be used in nodejs recently, this document is translated along with the document, so there is no responsibility for translation, incomplete or wrong.

Node-mysql Project address
My blog

Install

Stable version installation:

$ npm install mysql

Install the latest version:

$ npm install felixge/node-mysql

Introduction

This is a Node. js driver that uses Javascript to connect to the Mysql database.

Establish a connection

A recommended method for establishing a connection:

var mysql      = require('mysql');var connection = mysql.createConnection({  host     : 'example.org',  user     : 'bob',  password : 'secret'});connection.connect(function(err) {  if (err) {    console.error('error connecting: ' + err.stack);    return;  }  console.log('connected as id ' + connection.threadId);});

However, the connection can be implicitly established in the query:

var mysql = require('mysql');var connection = mysql.createConnection(...);connection.query('SELECT 1', function(err, rows) { // connected! (unless `err` is set)});

Connection options)

When creating a connection, you can use the following options:

Host: host address (default: localhost)

User: user Name

Password: password

Port: port Number (default: 3306)

Database: database Name

Charset: connected character set (default: 'utf8 _ GENERAL_CI ', note that all letters in the character set must be capitalized)

LocalAddress: this IP address is used for TCP connections (optional)

SocketPath: The Path to the unix domain. It is ignored when host and port are used.

Timezone: Time Zone (default: 'local ')

ConnectTimeout: Connection timeout (default: unlimited; Unit: milliseconds)

StringifyObjects: whether to serialize the object (default: 'false'; security-related https://github.com/felixge/node-mysql/issues/501)

TypeCast: whether to convert the column value to a local JavaScript type value (default: true)

QueryFormat: https://github.com/felixge/node-mysql#custom-format for formatting methods for custom query statements

SupportBigNumbers: if the database supports columns of the bigint or decimal type, set this option to true (default: false)

BigNumberStrings: supportBigNumbers and bigNumberStrings enable forced bigint or decimal columns to return JavaScript string type (default: false)

DateStrings: forced return of the timestamp, datetime, and data types as strings, rather than JavaScript Date (default: false)

Debug: Enable debugging (default: false)

MultipleStatements: Indicates whether a query contains multiple MySQL statements (default: false)

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

Ssl: uses ssl parameters (in the format of crypto. createCredenitals) or a string containing the ssl configuration file name. Currently, only the configuration file of Amazon RDS is bundled.

Others:

You can add strings in the URL format. I don't want to introduce them much. I don't like the format very much. I think it is easy to read and error. If you want to know about it, go to the homepage.

Close connection

You can close the database connection in two ways.

Use the end () method to close the completed database connection as normal:

connection.end(function(err) {  // The connection is terminated now});

Another optional method is to use the destroy () method.
This method will be executed immediately, no matter whether queries is complete or not!

connection.destroy();

Unlike end (), the destroy () method does not accept callback.

Query

The most basic way to create a query is to call the. query () method on a Connection instance (such as Connection, Pool, PoolNamespace, or other similar objects ).

The simplest form of query () is. query (sqlString, callback). The first parameter is an SQL statement, and the second parameter is a callback function:

connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {  // error will be an Error if one occurred during the query  // results will contain the results of the query  // fields will contain information about the returned results fields (if any)});

The second form. The placeholder is used for query (sqlString, values, callback:

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) { // error will be an Error if one occurred during the query // results will contain the results of the query // fields will contain information about the returned results fields (if any)});

The third form. query (options, callback) is to use multiple option parameters in the query, such as escaping query values, joins with overlapping column names, timeouts, and type casting:

connection.query({ sql: 'SELECT * FROM `books` WHERE `author` = ?', timeout: 40000, // 40s values: ['David']}, function (error, results, fields) { // error will be an Error if one occurred during the query // results will contain the results of the query // fields will contain information about the returned results fields (if any)});

Note that when Placeholders are passed as parameters rather than objects, the second and third forms can be used in combination.

The values parameter overwrites the values in the option object.

Encoding query value

To prevent SQL injection, you should always encode the data provided by the user before inserting the query statement.
You can do this:
Mysql. escape (), connection. escape () or pool. escape () method:

var userId = 'some user provided value';var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);connection.query(sql, function(err, results) {  // ...});

Can you use it? As a placeholder, You can encode the value as follows:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) { // ...});

In essence, the same connection. escape () method is used.

If you pay attention to it, you may notice that this can make your statement concise:

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'

If you want to encode it yourself, you can also use the escaping function directly:

var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");console.log(query); // SELECT * FROM posts WHERE title="Hello MySQL"

Encoding query identifier

If you do not believe the user-provided query identifier (database/table/column name), you can use mysql. escapeId (identifier ),
Connection. escapeId (identifier) or pool. escapeId (identifier) to encode it:

var sorter = 'date';var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);connection.query(sql, function(err, results) {  // ...});

You can also add the appropriate identifier, which will be encoded.

var sorter = 'date';var sql = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);connection.query(sql, function(err, results) { // ...});

In addition, you can use ?? The character as a placeholder gives you the identifier to encode as follows:

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

You can pass an object to. escape () or. query (),. escapeId () to avoid SQL injection.

Create query statement

You can use mysql. format to create a query statement with multiple insertion points and encode the id and value properly. A simple example:

var sql = "SELECT * FROM ?? WHERE ?? = ?";var inserts = ['users', 'id', userId];sql = mysql.format(sql, inserts);

In this way, you can obtain a valid and secure query statement.

Custom format

If you like another query encoding format, there is a connection option that allows you to define a custom format.
An example of custom format implementation:

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" });

Obtains the id of the inserted row.

If you insert a row into a table with an auto-incrementing primary key, you can obtain the inserted ID as follows:

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {  if (err) throw err;  console.log(result.insertId);});

When processing large numbers (in JavaScript precision), you should consider enabling the supportbignumbers option to read and insert IDS as strings; otherwise, an error will be thrown.
Enable this option when retrieving large numbers from the database.

Obtain the number of affected rows

You can obtain the number of rows affected by the insert, update, or delete statement.

connection.query('DELETE FROM posts WHERE title = "wrong"', function (err, result) {  if (err) throw err;  console.log('deleted ' + result.affectedRows + ' rows');})

Obtain the number of changed rows

You can obtain the number of rows changed by the update statement.

"ChangedRows" is different from "affectedRows" because it is not a value that has not been changed.

connection.query('UPDATE posts SET ...', function (err, result) {  if (err) throw err;  console.log('changed ' + result.changedRows + ' rows');})

Obtain the connection ID

You can use the threadId attribute to obtain the MySQL connection ID ("thread ID ").

connection.connect(function(err) {  if (err) throw err;  console.log('connected as id ' + connection.threadId);});

Multi-statement Query

It is disabled by default for security reasons.
To create a connection as needed:

var connection = mysql.createConnection({multipleStatements: true});

After opening, you can execute the following multi-statement query:

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

In addition, you can also:

var query = connection.query('SELECT 1; SELECT 2');query .on('fields', function(fields, index) { // the fields for the result rows that follow }) .on('result', function(row, index) { // index refers to the statement this result belongs to (starts at 0) });

If an Error occurs due to a query statement, an Error occurs.
The err. index attribute is the number of statements you encounter.
When an error occurs, MySQL stops executing other statements.

Error Handling

This module comes with error handling. To write robust applications, you should take a closer look.

All Errors created through this module are JavaScript Error object instances.
. They have two attributes:

Err. code: MySQL error code (e.g. 'Er _ ACCESS_DENIED_ERROR '), node. js error (e.g. 'ignore') or internal error (e.g. 'Protocol _ CONNECTION_LOST '). * err. fatal: Boolean value, indicating whether the error is a terminal connection object.

Error: https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Error
MySQL server error: http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

A fatal error suspends all callbacks. In the following example, a fatal error is triggered when you try to connect to an invalid port. Therefore, the error is passed to the callback:

var connection = require('mysql').createConnection({  port: 84943, // WRONG PORT});connection.connect(function(err) {  console.log(err.code); // 'ECONNREFUSED'  console.log(err.fatal); // true});connection.query('SELECT 1', function(err) {  console.log(err.code); // 'ECONNREFUSED'  console.log(err.fatal); // true});

Normal errors are returned only to their callback functions. In the following example, only the first callback receives an error and the second query has an error:

connection.query('USE name_of_db_that_does_not_exist', function(err, rows) { console.log(err.code); // 'ER_BAD_DB_ERROR'});connection.query('SELECT 1', function(err, rows) { console.log(err); // null console.log(rows.length); // 1});

If a fatal error is generated, there is no callback to be determined, or when a normal error is generated but does not belong to another callback, the error will send an error event to the connection object. The following is an example:

connection.on('error', function(err) { console.log(err.code); // 'ER_BAD_DB_ERROR'});connection.query('USE name_of_db_that_does_not_exist');

Note: The error event is special in node. If they appear and are not bound to a listener, the stack trace is printed and your process is killed.

connection.query({ sql: 'SELECT * FROM `books` WHERE `author` = ?', timeout: 40000, // 40s }, ['David'], function (error, results, fields) { // error will be an Error if one occurred during the query // results will contain the results of the query // fields will contain information about the returned results fields (if any) });