Dar1in9's Blog HomeTagsCategoriesArchivesAbout
nodejs使用mysql
|node.js
字数总计: 2k|阅读时长: 8 分钟

node-mysql是一个实现了MySQL协议的Node.js JavaScript客户端，通过这个模块可以与MySQL数据库建立连接、执行查询等操作

安装

1
$ npm install mysql

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
var mysql = require('mysql')

var connection = mysql.createConnection({   // 配置连接信息
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'password',
    database: 'my_db'
});
connection.connect((err) => {
  if(err){
    console.log('connect error: ' + err.message)
  }
})   // 连接数据库
connection.query('SELECT 1 + 1 AS solution', function (err, rows, fields) {
  if (err) throw err
  console.log('The solution is: ', rows[0].solution)
})

connection.end()    // 断开连接

连接

建立连接

建立连接可以使用createConnection()方法创建连接对象，再使用connect()建立连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
var connection = mysql.createConnection({   // 配置连接信息
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'root',
    database: 'db',
    charset: 'UTF8'
});
connection.connect((err) => {
  if(err){
    console.log('connect error: ' + err.message)
  }
})

连接参数也可以一个查询字符串的形式：

1
var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=UTF8&timezone=-0700');

关闭连接

关闭数据库连接可以使用两种方法。

  1. 通过end()方法，在执行结束后关闭连接
    1
    2
    3
    connection.end(function(err) {
      // The connection is terminated now
    });
  2. 使用destroy()方法，这个方法会立即关闭连接套接字，而不管执行是否完毕，且这个方法没有回调函数
    1
    connection.destroy();

切换用户和改变连接

MySQL可以在当前不关闭套接字的情况下切换用户和改变连接，通过changeUser()方法能够实现这一功能：

1
2
3
4
5
connection.changeUser({user : 'admin'}, function(err) {
  if (err){
    console.log(err.message)
  };
});

调用这一方法时可以传入一个包含以下可选值的对象：

  • user－新用户的用户名（默认为之前用户）
  • password－新用户的密码（默认为之前用户密码）
  • charset－新连接的编码（默认为之前连接的字符编码）
  • database－新连接的数据库（默认为之前数据库）

连接池

数据库连接是一种有限的，能够显著影响到整个应用程序的伸缩性和健壮性的资源，在多用户的网页应用程序中体现得尤为突出。

数据库连接池正是针对这个问题提出来的，它会负责分配、管理和释放数据库连接，允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个连接，释放空闲时间超过最大允许空闲时间的数据库连接以避免因为连接未释放而引起的数据库连接遗漏。数据库连接池能明显提高对数据库操作的性能。

连接池连接

通过createPool()方法可以使用连接池连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var mysql = require('mysql');

var pool  = mysql.createPool({
  connectionLimit : 10,
  host : '127.0.0.1',
  user : 'root',
  password : 'root'
});

pool.query('SELECT username from users', function(err, result) {
  if (err){
    console.log(err.message)
  }
  console.log(rows);
});

通过getConnection()方法连接可以共享一个连接，或管理多个连接：

1
2
3
4
5
6
7
8
9
10
11
var mysql = require('mysql');

var pool  = mysql.createPool({
  host : '127.0.0.1',
  user : 'root',
  password : 'root'
});

pool.getConnection(function(err, connection) {
  // connected! (unless `err` is set)
});

管理连接池连接

连接使用完后通过调用connection.release()方法可以将连接返回到连接池中，这个连接可以被其它人重复使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var mysql = require('mysql');
var pool  = mysql.createPool({
  host : '127.0.0.1',
  user : 'root',
  password : 'root'
});

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query( 'SELECT something FROM sometable', function(err, rows) {
    // And done with the connection.
    connection.release();  //放回连接池
    // Don't use the connection here, it has been returned to the pool.
  });
});

连接池关闭

当使用完连接池，要关闭所有连接，Node.js的事件循环在MySQL服务关闭前全然会有效。我们可以使用end()方法关闭连接池中的所有连接：

1
2
3
pool.end((err)=>{
  // all connections in the pool have ended
})

执行sql语句

执行查询

在node-mysql中，通过Connection或Pool实例的query()执行SQL语句，所执行的SQL语句可以是一个SELECT查询或是其它数据库操作。query()方法有以下三种形式（他们的返回值都是数组类型，数组中内容为查询结果对象）：
.query(sqlString, callback)

  • sqlString－要执行的SQL语句
  • callback－回调函数，其形式为function (error, results, fields) {}
1
2
3
4
5
connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
  // error 是一个错误对象，在查询发生错误时存在
  // results 为查询结果
  // fields 包含查询结果的字段信息
});

.query(sqlString, values, callback)

  • sqlString－要执行的SQL语句
  • values－{Array}，要应用到查询占位符的值
  • callback－回调函数，其形式为function (error, results, fields) {}
1
2
3
4
5
connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
  // error 是一个错误对象，在查询发生错误时存在
  // results 为查询结果
  // fields 包含查询结果的字段信息
});

.query(sqlString, values, callback)

  • options－{Object}，查询选项参数
  • callback－回调函数，其形式为function (error, results, fields) {}
1
2
3
4
5
6
7
8
9
connection.query({
  sql: 'SELECT * FROM `books` WHERE `author` = ?',
  timeout: 40000, // 40s
  values: ['David']
}, function (error, results, fields) {
  // error 是一个错误对象，在查询发生错误时存在
  // results 为查询结果
  // fields 包含查询结果的字段信息
});

当使用参数占位符时，第二种和第三种形式也可以结合使用。如，可以将上例中的values值独立为一个参数传入：

1
2
3
4
5
6
7
8
connection.query({
  sql: 'SELECT * FROM `books` WHERE `author` = ?',
  timeout: 40000 // 40s
}, ['David'], function (error, results, fields) {
  // error 是一个错误对象，在查询发生错误时存在
  // results 为查询结果
  // fields 包含查询结果的字段信息
});

查询编码与安全

为了防止SQL注入，可以传入参数进行编码。参数编码方法有：
mysql.escape()、connection.escape()、pool.escape()，这三个方法可以在你需要的时候调用：

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

同样的，也可以使用?做为查询参数占位符，这与查询值编码效果是一样的：

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

在使用查询参数占位符时，在其内部自动调用connection.escape()方法对传入参数进行编码。

escape()方法编码规则如下：

  • Numbers不进行转换
  • Booleans转换为true/false
  • Date对象转换为’YYYY-mm-dd HH:ii:ss‘字符串
  • Buffers转换为hex字符串，如X’0fa5’
  • Strings进行安全转义
    在' " \0
  • Arrays转换为列表，如['a', 'b']会转换为'a', 'b'
  • 多维数组转换为组列表，如[['a', 'b'], ['c', 'd']]会转换为('a', 'b'), ('c', 'd')
  • Objects会转换为key=value键值对的形式
  • undefined/null会转换为NULL

使用查询查询占位符时时，其自动转换如下：

1
2
3
4
5
var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Next
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

如果你要自己进行编码，可以像下面这样的使用编码函数：

1
2
3
var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");

console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'

多语句查询

出于安全考虑node-mysql默认禁止多语句查询（可以防止SQL注入），启用多语句查询可以将multipleStatements选项设置为true：

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

启用后可以在一个query查询中执行多条语句：

1
2
3
4
5
6
7
connection.query('SELECT 1; SELECT 2', function(err, results) {
  if (err) throw err;

  // `results`是一个包含多个语句查询结果的数组
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});
文章作者: Dar1in9
文章链接: http://dar1in9s.github.io/2020/05/19/node/nodejs使用mysql/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Dar1in9's Blog！