Fork me on GitHub

node.js 和 mysql 结合使用

可视化工具:navicat for MySQL

node和mysql的配合

mysql和node.js的连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost', // 数据库的ip
port : 3306, // 数据库起服务的端口
user : 'root',
password : '123456',
database : 'test' // 数据库名称
});
// 链接数据库
connection.connect();
// 推荐使用下面的链接方式
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
console.log('connected as id ' + connection.threadId); // 线程id
});
// 测试数据库是否连接成功
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});

创建表

1
2
3
4
connection.query("CREATE TABLE person(id int,user varchar(255),password varchar(255))", function(err,result){
if(err){throw err}
console.log("创建表成功")
})

连接池

连接池连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
var pool = mysql.createPool({
connectionLimit : 10,
host : 'example.org',
user : 'bob',
password : 'secret',
database : 'my_db'
});
// 测试数据库是否连接成功
pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
// 使用连接池连接可以更容易地共享某个连接,也可以管理多个连接。
pool.getConnection(function(err, connection) { // 使用连接
connection.query( 'SELECT something FROM sometable', function(err, rows) {
// 使用连接执行查询
connection.release(); //连接不再使用,返回到连接池
connection.destroy(); // 如果你想关闭连接并从连接池中删除它,在下次需要时连接池会再创建一个新的连接。
});
});

连接池的事件

连接
1
2
3
pool.on('connection', function (connection) {
connection.query('SET SESSION auto_increment_increment=1')
});
入列
1
2
3
pool.on('enqueue', function () {
console.log('Waiting for available connection slot');
});
在连接池中关闭所有连接
如不再需要连接池时,你必须关闭所有连接。
1
2
3
pool.end(function (err) {
// all connections in the pool have ended
});
集群连接池
集群连接池提供多主机连接.(分组&重试&选择器)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// create
var poolCluster = mysql.createPoolCluster();
// add configurations (the config is a pool config object)
poolCluster.add(config);
// add configuration with automatic name
poolCluster.add('MASTER', masterConfig);
// add a named configuration
poolCluster.add('SLAVE1', slave1Config);
poolCluster.add('SLAVE2', slave2Config);
// remove configurations
poolCluster.remove('SLAVE2');
// By nodeId poolCluster.remove('SLAVE*');
// By target group : SLAVE1-2
// 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) {});
// close all connections
poolCluster.end(function (err) { // all connections in the pool cluster have ended });
更换用户并且改变连接状态
该命令允许你在不关闭下列socket的情况下,改变当前用户和连接的其余部分.
1
2
3
connection.changeUser({user : 'john'}, function(err) {
if (err) throw err;
});

数据库操作(CURD)

代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作。

查询数据(Retrieve)

query(sqlString, callback)
第一个参数是一条SQL字符串,第二个参数是回调
1
2
3
4
5
6
connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
if (error) {
console.log(error.message);
}
// 查询之后的操作
});
query(sqlString, values, callback)
带有值的占位符 (查看转义查询值)
1
2
3
4
5
6
7
connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
if (error) {
console.log(error.message);
}
// 查询之后的操作
});
connection.end(); // 断开数据库连接
query(options, callback)
在查询时带有大量的高级可选项
1
2
3
4
5
6
7
8
9
10
connection.query({
sql: 'SELECT * FROM `books` WHERE `author` = ?',
timeout: 40000,
values: ['David']
}, function (error, results, fields) {
if (error) {
console.log(error.message);
}
// 查询之后的操作
});
查询值转义
为了防止SQL注入,每当需要在SQL查询中使用用户数据时,你都应当提前对这些值进行转义。
1
2
3
4
5
6
7
8
9
10
// 第一种,转义可以通过 mysql.escape(), connection.escape() 或 pool.escape() 方法实现。
var userId = 'some user provided value';
var sql = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
// ...
});
// 第二种,使用 ? 作为查询字符串中的占位符,替代你想要转义的值。
connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
// ...
});
查询标识符转义
如果用户提供了不可信的查询标识符(数据库名、表名、列名),
你应该用 mysql.escapeId(identifier), connection.escapeId(identifier) 或 pool.escapeId(identifier) 方法对它进行转义。
1
2
3
4
5
6
7
8
9
10
var sorter = 'date';
var sql = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function(err, results) {
// ...
});
var userId = 1;
var columns = ['username', 'email'];
var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err, results) {
// SELECT `username`, `email` FROM `users` WHERE id = 1
});
预查询
你可以使用 mysql.format 来创建一个多插入点的查询语句,对id和值可以使用适当的转义处理 。
这样你就获得了一个有效并且安全的查询语句,然后可以把它发送给 数据库。
1
2
3
var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

插入数据(Insert)

1
2
3
4
5
6
7
8
9
10
11
var addSql = 'INSERT INTO websites(Id, name, url, alexa, country) VALUES(0, ?, ?, ?, ?)';
var addSqlParams = ['余真帆的博客', 'fanerge.githut.io', '333325', 'CN'];
connection.query(addSql, addSqlParams, function (err, result) {
if (err) {
console.log('[SELECT ERROR] - ', err.message);
}
console.log('-------INSERT--------');
console.log('INSERT ID:', result);
console.log('-------END--------');
});
connection.end();

更新数据(Update)

1
2
3
4
5
6
7
8
9
10
11
var modSql = 'UPDATE websites SET name = ?, url = ?, alexa = ? WHERE Id = ?';
var modSqlParams = ['余真帆的网站', 'https://fanerge.github.io', 23, 6]
connection.query(modSql, modSqlParams, function (err, result) {
if (err) {
console.log('[UPDATE ERROR] - ', err.message);
}
console.log('-------UPDATE--------');
console.log('UPDATE affectedRows:', result.affectedRows);
console.log('-------END--------');
});
connection.end();

删除数据(Delete)

1
2
3
4
5
6
7
8
9
10
var delSql = 'DELETE FROM websites WHERE name = "余真帆的博客"';
connection.query(delSql, function (err, result) {
if (err) {
console.log('[DELETE ERROR] - ', err.message);
}
console.log('-------DELETE--------');
console.log('DELETE affectedRows:', result.affectedRows);
console.log('-------END--------');
});
connection.end();

常用技巧

获取插入行的id

如果你把一行插入到一个有自增主键的表中,可以这样获得插入的ID
1
2
3
connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {
if (err) throw err; console.log(result.insertId);
});

取得受影响的行数

你可以从 insert,update 或者 delete 子句中取得受影响的行数。
1
2
3
4
connection.query('DELETE FROM posts WHERE title = "wrong"', function (err, result) {
if (err) throw err;
console.log('deleted ' + result.affectedRows + ' rows');
})

取得被改变的行数

你可以从 update 子句取得被改变的行数。
1
2
3
4
connection.query('UPDATE posts SET ...', function (err, result) {
if (err) throw err;
console.log('changed ' + result.changedRows + ' rows');
})

获取连接ID

你可以取得MySQL的连接ID(“线程ID”),这是一个给定的连接,使用的是线程ID属性。
1
2
3
4
connection.connect(function(err) {
if (err) throw err;
console.log('connected as id ' + connection.threadId);
});

并行执行查询

MySQL协议是顺序的,这意味着你需要多次连接执行并行查询。
你可以使用池来管理连接,一个简单的办法是每传入一个http请求,就创建一个连接。

流式查询行

有的时候可能需要查询大量的数据行,然后在接收到这些数据行的时候一行行的处理它们。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
var query = connection.query('SELECT * FROM posts');
query.on('error', function(err) {
// 处理错误,这之后会触发 'end' 事件
})
.on('fields', function(fields) {
// 字段信息
})
.on('result', function(row) {
// 暂停连接。如果你的处理过程涉及到 I/O 操作,这会很有用。
connection.pause();
processRow(row, function() {
connection.resume();
});
})
.on('end', function() {
// 所有数据行都已经接收完毕
});

通过 Streams2 管道输出

通过管道将查询结果输出到另一个流(最大缓冲 5 个对象)
1
2
3
connection.query('SELECT * FROM posts')
.stream({highWaterMark: 5})
.pipe(...);

多语句查询

1
2
3
4
5
6
// 开启多语句查询
var connection = mysql.createConnection({multipleStatements: true});
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:
});

总结:

  1. 先使用var connection = mysql.createConnection方法一个数据库连接的配置项
  2. 使用connection.connect方法连接数据库
  3. 使用sql语句对数据库进行CURD操作
  4. 操作完成后需使用connection.end方法断开数据库连接
    connection.end(function (err) { … }); // 支持回调
    connection.destroy(); // 该方法会立即终止底层套接字(underlying socket)。另外,destroy()不会触发更多的事件和回调函数。

    参考文档:
    Node.js 连接 MySQL
    如何在node.js里连接和使用mysql
    mysqljs
    mysql

-------------本文结束感谢您的阅读,如果本文对你有帮助就记得给个star-------------
Donate comment here