nodejs中使用mysql

安装mysql

    npm i --save mysql

基本用法

创建管理一个连接

    var mysql = require('mysql');
    var connection = mysql.createConnection({
        host: 'aliyuncs.com',
        user: 'admin',
        password: 'pwd'
    });

    connection.connect(function(err) {
        if (err) {
            console.error('error connecting: ' + err.stack);
            return;
        }

        connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
            if (error) throw error;
            
            //do something


            connection.end();
        });
    });

转义

为了避免SQL注入攻击，在SQL查询中使用任何用户提供的数据之前，始终应该对其进行转义。

手动转义

mysql提供了mysql.escape(), connection.escape() 和 pool.escape()

var userId = 'user input values';
var sql    = 'SELECT * FROM users WHERE userId = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
    if (error) throw error;
    //do something
});

自动转义

使用?字符作为要转义的值的占位符

connection.query('UPDATE users SET name = ?, address = ? WHERE userId = ?', [inputName, inputAddress, userId], function (error, results, fields) {
    if (error) throw error;
    //do something
});

连接和连接池

当我们一个api接口需要连接到数据库进行增删改查的时候，如果每次去创建连接然后去关闭，会大大的消耗数据库服务的性能。

连接池

连接是由池延迟创建的，基本流程是pool.getConnection() -> connection.query() -> connection.release()，有助于共享连接状态以用于后续查询。

    var mysql = require('mysql');
    var pool  = mysql.createPool({
        connectionLimit: 10,
        host: 'aliyuncs.com',
        user: 'admin',
        password: 'pwd',
        database: 'db',
        timezone: 'Asia/Shanghai'
    });

    pool.getConnection(function(err, connection) {
        if (err) throw err; 

        connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
            connection.release();     //释放连接，以便其他请求使用

            if (error) throw error;

            //do something
        });
    });

连接池组

其实就是多个连接池的组合

创建一个连接池组

    var mysql = require('mysql');
    var poolCluster = mysql.createPoolCluster();

    poolCluster.add('pool1', {
        connectionLimit: 10,
        host: 'aliyuncs.com',
        user: 'admin',
        password: 'pwd',
        database: 'db',
        timezone: 'Asia/Shanghai',
        database: 'db1'
    });

    poolCluster.add('pool2', {
        connectionLimit: 10,
        host: 'aliyuncs.com',
        user: 'admin',
        password: 'pwd',
        database: 'db',
        timezone: 'Asia/Shanghai',
        database: 'db2'
    });

使用连接池

    poolCluster.getConnection('pool1', function (err, connection) {    //使用pool1池
        if (err) throw err; 

        connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
            connection.release();
            if (error) throw error;

            //do something
        });
    });

事务组

多个连接请求“合并”成一个流程，来保证程序的正确运行

为什么需要事务组？

场景：当前有一个抽奖活动，用户点击前端抽奖按钮后调用api接口
后端逻辑：去道具表扣掉用户道具 -> 去奖品表修改奖品为已发放给用户发放奖品 -> 再中奖记录表中生成一条中奖记录

试想一下，上面的三个步骤，任何一个步骤出问题都将导致重大bug

比如：道具扣掉了，但是礼物没发放，又比如礼物发放了没有记录

如何创建事务组

    ......
    connection.beginTransaction(function(err) {
        if (err) throw err;
        connection.query('UPDATE props SET ...?', ..., function (error, results, fields) {
            if (error) {
                return connection.rollback(function() {
                    throw error;
                });
            }


            connection.query('UPDATE SET ...', ..., function (error, results, fields) {
                if (error) {
                    return connection.rollback(function() {
                        throw error;
                    });
                }

                connection.query('INSERT INTO record ...', ..., function (error, results, fields) {
                    if (error) {
                        return connection.rollback(function() {
                            throw error;
                        });
                    }
                    connection.commit(function(err) {
                        if (err) {
                            return connection.rollback(function() {
                                throw err;
                            });
                        }
                        
                        //do something
                    });
                });
            });
        });
    });

以上代码，任何一个连接操作出现问题，执行回滚，整个流程都不会执行，只有当三个连接操作都成功，执行提交，改动才生效。

beginTransaction(), commit() 和 rollback() 分别对应了 START TRANSACTION, COMMIT, 和 ROLLBACK

利用Promise封装一个事务组方法

    ......
    var transactionQuery = (database, callback) => {
        return new Promise((resolve, reject) => {
            poolCluster.getConnection(database, function (err, connection) {
                if (err) {
                    reject({
                        msg: "系统内部错误，请稍后再试"
                    });
                    return;
                }

                connection.beginTransaction(function (err) {
                    if (err) {
                        reject({
                            msg: "系统内部错误，请稍后再试"
                        });
                        return;
                    }

                    callback.apply(null, [connection, resolve, reject]);
                });
            });
        });
    }

    //调用

    const res = await common.transactionQuery('pool1', (connection, resolve, reject) => {
            const rollback = (msg) => {
                connection.rollback(() => {
                    if (msg) {
                        reject({
                            msg: msg
                        });
                    }
                    connection.release();
                });
            }

            connection.query('UPDATE props SET ...?', ..., function (error, results, fields) {
                if (error) {
                    rollback('系统内部错误：10001');
                    return;
                }

                connection.query('UPDATE lucky SET ...', ..., function (error, results, fields) {
                    if (error) {
                        rollback('系统内部错误：10002');
                        return;
                    }

                    connection.query('INSERT INTO record ...', ..., function (error, results, fields) {
                        if (error) {
                            rollback('系统内部错误：10003');
                            return;
                        }

                        connection.commit(() => {
                            resolve();
                            connection.release();
                        });
                    });
                });
        });
    });

    console.log(res);