【教程】使用 mysql 模块连接数据库

mysql 驱动模块是用 JavaScript编写的 MySQL 驱动，用于 Node.js环境下。它无须编译，基于 MIT 许可。使用 npm 即可进行安装。

npm install mysql

一、创建数据库连接 createConnection()

入门使用，看下面这个示例：

// 导入模块
var mysql = require('mysql')
// 配置项
var options = {
    host: 'localhost',
    user: 'root',
    password: '******',
    database: 'my_db'
}
var connection = mysql.createConnection(options)

更多配置项，参见 mysql 模块的官方手册。

Every method you invoke on a connection is queued and executed in sequence. Closing the connection is done using end() which makes sure all remaining queries are executed before sending a quit packet to the mysql server.

两种方式建立 mysql 数据库连接的方式

connection.connect(function(err) {
    if (err) {
        console.log(err)
        return
    }
    console.log('connection id is ' + connection.threadId)
})

connection.query('SELECT * FROM test', function(err, res, fields) {
    if (err) throw err
    console.log('查询结果', res)
})

以上两种方式，都可以创建 mysql 连接，这取决于你的开发习惯（任选其一即可），这两种方式都支持 err 捕获与处理。

Any type of connection error (handshake or network) is considered a fatal error, see the Error Handling section for more information.

end() destroy()

有两种方式结束一个 connection 连接，end() 和 destroy()，前者在结束连接的同时，还会以回调的方式返回查询数据包，而后者不会。

connection.end()    // 关闭连接

切换当前用户

mysql 模块还提供了切换用户的功能，这允许你在不中断底层socket连接的情况下修改当前用户和其它方面的连接。

connection.changeUser({user: 'john', password: '***', database: 'test', charset: ''}, function(err) {
    if (err) throw err
})

二、创建连接池 createPool()

连接池创建

相比一个一个地创建并管理 connection，使用连接池的方式创建连接，会更加高效。代码示例如下：

var mysql = require('mysql')
const options = {
    connectionLimit: 10,
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: '********',
    database: 'sys'
}
var pool = mysql.createPool(options)

pool.getConnection(function(err, conn) {
    if (err) throw err
    conn.query('select * from session', function(err,data,fields) {
        if (err) throw err
        if (res) res.end(JSON.stringify(data))
        conn.release()  // 释放连接，丢回池子中去
        // conn.destroy()   关闭并彻底移除这个连接
    })
})

This is a shortcut for the pool.getConnection() -> connection.query() -> connection.release() code flow. Using pool.getConnection() is useful to share connection state for subsequent queries. This is because two calls to pool.query() may use two different connections and run in parallel.

创建连接池时，除了接受和 createConnection() 一样的参数外，还接受4个额外的参数，参见 mysql 模块的文档手册。

连接池事件

线程程可以监听四个事件，分别是 acquire / connection / enqueue / release 事件。

pool.on('release', function(conn) {
    console.log('Connection %d released.', conn.threadId)
})

关闭连接池中的所有连接

当使用连接池完毕时，你可以关闭掉连接池中的所有连接。

pool.end(function(err) {})

三、创建连接池集群 createPoolCluster()

PoolCluster provides multiple hosts connection. (group & retry & selector) 连接多台MySQL服务器。常用API 如下：

// 创建连接池集群
var poolCluster = mysql.createPoolCluster()
// 添加 host配置连接
poolCluster.add(config)
poolCluster.add('master', masterConfig)
poolCluster.add('slave1', slave1Config)
poolCluster.add('slave2', slave2Config)
// 删除一个 host连接
poolCluster.remove('slave2')
// 连接
poolCluster.getConnection(function(err, conn) {})
poolCluster.getConnection('master', function(err, conn) {})
// 连接、执行查询
var pool = poolCluster.of('*')
pool.getConnection(function(err, conn) {})
pool.query(function(err, res, fields) {})
// 关闭连接集群
poolCluster.end(function(err) {})

四、执行查询任务

The most basic way to perform a query is to call the .query() method on an object (like a Connection, Pool, or PoolNamespace instance).

语法一：
connection.query('SELECT * FROM `books` WHERE `author` = "David"', function(err, data, fields) { })

语法二：
connection.query('SELECT * FROM `books` WHERE `author` = ?', ["David"], function(err, data, fields) { })

语法三：
connection.query({
    sql: 'SELECT * FROM `books` WHERE `author` = ?',
    timeout: 40000,
    values: ["David"]
}, function(err, data, fields) {
    // handle data
})

语法四：
connection.query({
    sql: 'SELECT * FROM `books` WHERE `author` = ?',
    timeout: 40000
}, ["David"], function(err, data, fields) {
    // handle data
})

语法五：当占位符参数只有一个，且不是 null / undefined / 数组时，还可以像下面这种写法。

connection.query('SELECT * FROM `books` WHERE `author` = ?', 'David', function(err, data, fields) { })

Escaping query values

为了避免SQL注入攻击，你通常需要对用户提供的查询值先进行转换，再进行 SQL查询。使用 mysql.escape() / connection.escape() / pool.escape() 可以完成这一需求。

var userId = "xxxxx";  // 用户输入的 id

// 第一种写法：手动转换用户的入参
var esUserId = connection.escape(userId)  // 对用户入参先进行转换
var sql = 'SELECT * FROM `users` WHERE id =' + esUserId
connection.query(sql, function(err, data, fields) { })

// 另一种写法：下面这种写法，用户的入参 userId 会被 connection.escape() 自动地在内部进行转换。
connection.query('SELECT * FROM `users` WHERE id = ?', [userId], function(err,data,fields) { })

除了上述我们可以对用户输入的值进行转换，还可以对非用户输入的 SQL 关键字进行转换。使用 mysql.escapeId(), connection.escapeId() or pool.escapeId() 可以实现这一需求。如下示例：

var userId = 1
var columns = ['username', 'email']
connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err,data,fields) {})

这种写法时，其本质上在内部调用了 mysql.escapeId() 方法对 ??进行转换，无须我们手动操作。最后说明一点，你还可以自定义方法来转换这些用户的入参。

Getting the id of an inserted row

当你向表中插入数据时，你可以获取到插入行的ID。示例如下：

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

Getting the number of affected rows

当你执行插入、更新或者删除操作，你可以获取到受影响的行。示例如下：

connection.query('DELETE FROM posts WHERE title = "wrong"', function(err, data, fields) {
    if (err) throw err
    console.log(data.affectedRows)
})

Getting the number of changed rows

当执行"UPDATE"语句时，你可以获取取变化的行 data.changedRows。changedRows 不同于 affectedRows，前者指是“变化”，后者指的是“受影响”。

Getting the connection ID

我们还可以获取到连接对象的 ID，即 connection.threadId。

Executing queries in parallel 执行并行查询

MySQL协议规定，查询是按顺序的。这意味着，我们需要创建多个连接来执行并行查询。你可以使用连接池来管理这些连接，一种最为简单的实现方式是，在每一个来自客户端的HTTP请求中创建connection连接。

Streaming query rows 串流查询行

有时候你希望查询大量的行，并对查询的数据进行二次加工。你可像下面这样做：

var query = connection.query('SELECT * FROM posts')
query.on('error', function(err) {
    // handle error
}).on('fields', function(fields) {
    // 返回字段信息
}).on('result', function(row) {
    connection.pause()  // 临时暂停
    // 自定义方法，用于数据处理
    dataHandle(row, function() {
        // row数据处理
        connection.resume()  // 恢复查询、继续查询
    })
}).on('end', function() {
    // all rows have been received.
})

Piping results with Streams

connection.query('SELECT * FROM posts').stream({highWaterMark: 5}).pipe()

Joins with overlapping column names 联合

五、事务 Transactions

// 开始事务
connection.beginTransaction(function(err) {
    if (err) throw err
    connection.query('INSERT INTO posts SET title = ?', "job", function(err, data, fields) {
        if (err) { return connection.rollback(function() { throw err})}
        var log = 'post ' + data.insertId + ' added'
        // 把上一条查询记录，写入 log 表中去
        connection.query('INSERT INTO log SET data = ?', log, function(err, data, fields) {
            if (err) { return connection.rollback(function() { throw err})}
            // 事务 提交
            connection.commit(function(err) {
                if (err) { return connection.rollback(function() { throw err})}
                console.log('success!')
            })
        })
    })
})

六、Error handling 异常处理

致命的错误，会导致 query查询的 callback呈 pending状态。比如创建连接时使用了无效的端口号。当错误发生时，会触发error事件，如下示例监听 error事件：

connection.on('error', function(err) {
    console.log(err.code)
    console.log(err.errno)
    console.log(err.fatal)
    console.log(err.sqlState)
    console.log(err.sqlMessage)
})

七、Type casting 类型转换

为了方便，mysql驱动模块会自动默认地把mysql数据类型转换成JavaScript原生的数据类型。

mysql驱动模块默认已经开启了自动类型转换，当然你可以使用 typeCast: false选项，将其关闭。甚至你还可以自定义类型转换方法，来处理mysql数据类型到JavaScript数据类型之间的自定义转换。

更多知识点，参见 mysql 官方仓库：mysql 。

本篇结束！