This article mainly introduces node to operate mysql database, and combines the example form with a more detailed analysis of node operation database connection, addition, deletion, modification, transaction processing and error handling related operation skills. Friends in need can refer to the following
The example in this article describes how node operates the mysql database. Share it with everyone for your reference, the details are as follows:
1. Establish a database connection: createConnection(Object)
Method
This method accepts an object As parameters, this object has four commonly used attributes: host, user, password, and database. The same parameters as the database link in php. The attribute list is as follows:
host | The host name where the database is connected. (Default: localhost) |
port | Connection port. (Default: 3306) |
localAddress | IP address used for TCP connections . (Optional) |
socketPath | The path to link to the unix domain. This parameter will be ignored when using host and port. |
user | The username of the MySQL user. |
password | MySQL user’s password. |
database | The name of the database to link to (optional). |
charset | Character set for the connection. (Default: 'UTF8_GENERAL_CI'. Use uppercase when setting this value!) |
timezone | Save local The time zone of the time. (Default: 'local') |
stringifyObjects | Whether to serialize objects. See issue #501. (Default: 'false') |
insecureAuth | Whether old authentication methods are allowed to connect to the database instance. (Default: false) |
typeCast | Determine whether to convert column values to local Javascript type column values. (Default: true) |
queryFormat | Customized query statement formatting function. |
supportBigNumbers | When the database handles large numbers (long integers and decimals), it should be enabled (default: false). |
bigNumberStrings | Enable supportBigNumbers and bigNumberStrings and force these numbers to be returned as strings (default: false). |
dateStrings | Force date type (TIMESTAMP, DATETIME, DATE) is returned as a string instead of a javascript Date object. (Default: false) |
debug | Whether to enable debugging. (Default: false) : false) |
multipleStatements | Whether multiple query statements are allowed to be passed in one query. (Default: false) |
flags | Link flag. |
You can also use strings to connect to the database. For example:
var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');
2. End the database connection end()
and destroy()
end() accepts a callback function and will It is triggered after the query ends. If there is an error in the query, the connection will still be terminated, and the error will be passed to the callback function for processing.
destroy() terminates the database connection immediately. Even if the query is not completed, subsequent callback functions will not be triggered.
3. Create a connection pool createPool(Object)
The Object and createConnection parameters are the same.
You can listen to the connection event and set the session value
pool.on('connection', function(connection) { connection.query('SET SESSION auto_increment_increment=1') });
connection.release() releases the connection to the connection pool. If you need to close the connection and delete it, you need to use connection.destroy()
In addition to accepting the same parameters as connection, pool also accepts several extended parameters
createConnection | Function used to create links. (Default: mysql.createConnection) |
waitForConnections | Determine the behavior of the pool when there is no connection pool or the number of connections reaches the maximum value. When it is true, the connection will be put into the queue and called when available. When it is false, an error will be returned immediately. (Default: true) |
connectionLimit | Maximum number of connections. (Default: 10) |
queueLimit | The number of connection requests in the connection pool The maximum length. If it exceeds this length, an error will be reported. When the value is 0, there is no limit. (Default: 0) |
4. Connection pool cluster
Allow different host links
// create var poolCluster = mysql.createPoolCluster(); poolCluster.add(config); // anonymous group poolCluster.add('MASTER', masterConfig); poolCluster.add('SLAVE1', slave1Config); poolCluster.add('SLAVE2', slave2Config); // 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) {}); // destroy poolCluster.end();
Optional parameters for link cluster
canRetry | When the value is true, retry is allowed when the connection fails (Default: true) |
removeNodeErrorCount | The errorCount value will increase when the connection fails. When the errorCount value is greater than removeNodeErrorCount, a node will be removed from the PoolCluster. (Default: 5) |
defaultSelector | Default selection (Default: RR) |
Loop. (Round-Robin) | |
Select nodes by random function. | |
Unconditionally select the first available node. |
user | 新的用户 (默认为早前的一个). |
password | 新用户的新密码 (默认为早前的一个). |
charset | 新字符集 (默认为早前的一个). |
database | 新数据库名称 (默认为早前的一个). |
6、处理服务器连接断开
var db_config = { host: 'localhost', user: 'root', password: '', database: 'example' }; var connection; function handleDisconnect() { connection = mysql.createConnection(db_config); // Recreate the connection, since // the old one cannot be reused. connection.connect(function(err) { // The server is either down if(err) { // or restarting (takes a while sometimes). console.log('error when connecting to db:', err); setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect, } // to avoid a hot loop, and to allow our node script to }); // process asynchronous requests in the meantime. // If you're also serving http, display a 503 error. connection.on('error', function(err) { console.log('db error', err); if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually handleDisconnect(); // lost due to either server restart, or a } else { // connnection idle timeout (the wait_timeout throw err; // server variable configures this) } }); } handleDisconnect();
7、转义查询值
为了避免SQL注入攻击,需要转义用户提交的数据。可以使用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) { // ... });
不同类型值的转换结果
Numbers 不变
Booleans 转换为字符串 'true' / 'false'
Date 对象转换为字符串 'YYYY-mm-dd HH:ii:ss'
Buffers 转换为是6进制字符串
Strings 不变
Arrays => ['a', 'b'] 转换为 'a', 'b'
嵌套数组 [['a', 'b'], ['c', 'd']] 转换为 ('a', 'b'), ('c', 'd')
Objects 转换为 key = 'val' pairs. 嵌套对象转换为字符串.
undefined / null ===> NULL
NaN / Infinity 不变. MySQL 不支持这些值, 除非有工具支持,否则插入这些值会引起错误.
转换实例:
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'
或者手动转换
var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL"); console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'
8、转换查询标识符
如果不能信任SQL标识符(数据库名、表名、列名),可以使用转换方法mysql.escapeId(identifier);
var sorter = 'date'; var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter); console.log(query); // SELECT * FROM posts ORDER BY `date`
支持转义多个
var sorter = 'date'; var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter); console.log(query); // SELECT * FROM posts ORDER BY `posts`.`date`
可以使用??作为标识符的占位符
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
9、准备查询
可以使用mysql.format来准备查询语句,该函数会自动的选择合适的方法转义参数。
var sql = "SELECT * FROM ?? WHERE ?? = ?"; var inserts = ['users', 'id', userId]; sql = mysql.format(sql, inserts);
10、自定义格式化函数
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" });
11、获取插入行的id
当使用自增主键时获取插入行id,如:
connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) { if (err) throw err; console.log(result.insertId); });
12、流处理
有时你希望选择大量的行并且希望在数据到达时就处理他们,你就可以使用这个方法
var query = connection.query('SELECT * FROM posts'); query .on('error', function(err) { // Handle error, an 'end' event will be emitted after this as well }) .on('fields', function(fields) { // the field packets for the rows to follow }) .on('result', function(row) { // Pausing the connnection is useful if your processing involves I/O connection.pause(); processRow(row, function() { connection.resume(); }); }) .on('end', function() { // all rows have been received });
13、混合查询语句(多语句查询)
因为混合查询容易被SQL注入攻击,默认是不允许的,可以使用:
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: console.log(results[0]); // [{1: 1}] console.log(results[1]); // [{2: 2}] });
同样可以使用流处理混合查询结果:
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) });
如果其中一个查询语句出错,Error对象会包含err.index指示错误语句的id,整个查询也会终止。
混合查询结果的流处理方式是做实验性的,不稳定。
14、事务处理
connection级别的简单事务处理
connection.beginTransaction(function(err) { if (err) { throw err; } connection.query('INSERT INTO posts SET title=?', title, function(err, result) { if (err) { connection.rollback(function() { throw err; }); } var log = 'Post ' + result.insertId + ' added'; connection.query('INSERT INTO log SET data=?', log, function(err, result) { if (err) { connection.rollback(function() { throw err; }); } connection.commit(function(err) { if (err) { connection.rollback(function() { throw err; }); } console.log('success!'); }); }); }); });
15、错误处理
err.code = string err.fatal => boolean
The above is the detailed content of Introduction to using node to operate mysql database instance. For more information, please follow other related articles on the PHP Chinese website!