node-mysql小记

最近一直在折腾博客,之前用PHP封装的PDO类凑合着还能使用,迁移到Node之后又写了一个类似的模型类,谁知却经常炸掉,然后老老实实地去翻文档,有了下面的整理。

<!--more-->

参考:

备注:

  • 使用log4jsassert等库进行调试,下面示范代码中只保留核心代码。
  • 下面的mysql统指node下的mysql

1. 连接

1.1. 建立连接

可以调用createConnection()方法快速连接数据库

let mysql = require("mysql");

// 创建连接对象,后续操作均在conn上进行
let conn = mysql.createConnection({
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "database": "shymean",
});
// 建立连接,这一步不是必须的,在查询中会隐式建立连接
conn.connect(err=>{
    assert.ifError(err);
      // 可以获取连接id
    logger.info(`mysql connect at : ${this.conn.threadId}`);
});

1.2. 连接池

除了建立连接之外,也可以通过连接池来操作数据库。数据库连接池的概念并不是node mysql独有的,而是大部分数据库都具备的特性:

频繁的建立、关闭数据库,会极大的降低系统的性能。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用...

连接池这个概念我也不是十分清楚,查了相关资料,貌似更推荐使用连接池进行操作

let pool = mysql.createPool({
    connectionLimit : 10,
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "database": "shymean",
});

当建立连接池之后,就可以对数据库进行操作了(具体的query方法马上会提到)

// 直接使用query,每次会随机从连接池分配一个连接
pool.query(sql, values, cb);

// 某些操作需要保证在同一个连接上进行
pool.getConnection((err, conn)=>{
    assert.ifError(err);
    conn.query(sql, values, (err, res)=>{
        // todo
          // 释放连接到连接池
          conn.release();
    })        
})

下面来检测一下连接池

// promise化,这个函数在后面的实例代码中也会使用
function query(sql, values) {
    return new Promise((resolve, reject)=>{
        pool.getConnection((err, conn)=>{
            assert.ifError(err);
            conn.query(sql, values, (err, res)=>{
                assert.ifError(err);
                resolve(res);

                logger.debug(conn.threadId);
                conn.release();
            })
        })
    })
}
let table = "shymean_admin";

// mock
let tasks = [];
for (let i = 0; i < 3; ++i ){
    tasks.push(
        query(`SELECT * FROM ${table} WHERE id = ?`, { id: 1 })
    )
}

Promise.all(tasks).then(res=>{
    // ...
});

可以看见控制台会依次输出三条threadId,打开mysql控制台查看当前连接数

show processlist;

可以查看到对应的连接记录,Id字段即为控制台输出的连接id,,想了解更多可以查看processlist输出解释

1.3. 关闭连接

关闭连接可以使用 conn.end(err=>{})destroy()方法,区别在于destroy方法不接收回调函数而立即关闭连接。

关闭连接池可以调用pool.end(err=>{})来关闭连接池。

2. 操作

2.1. query

查询是最基本和最重要的操作,使用的接口是query,可以接收三种形式的参数

let table = "shymean_admin";

// 直接传入sql语句
conn.query(`SELECT id, name FROM ${table}`, (err, res)=>{
    assert.ifError(err);
    logger.debug(res);
});

// 使用占位符,跟PDO相似
// 关于占位符后面会再提到
conn.query(`SELECT ?? FROM ${table} WHERE name = ?`, [["id", "name"], "root"], (err, res)=>{
    assert.ifError(err);
    logger.debug(res);
});

// 传入配置
// 这里也可以将跟上面的占位符方式类一样,将values作为第二个参数传入,此时会覆盖配置中的values属性
conn.query({
    sql: `SELECT ?? FROM ${table} WHERE name = ?`,
    timeout: 1000,
    values: [["id", "name"], "root"]
}, (err, res)=>{
    assert.ifError(err);
    logger.debug(res);
});

为了防止sql注入,一般需要对查询值进行编码,对应的方法是mysql.escape()conn.escape()。如果使用了占位值的形式,则其实际也是使用了conn.escape方法。

2.2. 占位符

占位符是一个很方便的功能,后面重写的模型类会着重考虑这点。

除了默认?占位形式,还可以通过配置参数queryFormat自定义格式化方法,参考文档

文档中的示例展示了如何将占位符修改为:key的形式

// 修改queryFormat方法
conn.config.queryFormat = function (query, values) {
      // values是query方法的第二个参数
    if (!values) return query;

    return query.replace(/\:(\w+)/g, function (txt, key) {
          // 第一个参数是正则匹配到的真个字符串,即在sql语句中的占位符
        logger.debug(txt)
        // 后续参数依次为匹配到的分组
        logger.debug(key)

        if (values.hasOwnProperty(key)) {
            return this.escape(values[key]);
        }
        return txt;
    }.bind(this));
};

// 现在的占位格式化方式已修改,十分灵活
conn.query(`SELECT * FROM ${table} WHERE id = :id`, { id: 1 }, (err, res)=>{
    assert.ifError(err);
    logger.debug(res);
});

2.3. 查询值

sql语句的不少子句都可以看作是键值对的形式,比如WHEREVALUES等,这对于JS来说传入对象似乎是一件很合理的事情

query(`SELECT id, name FROM ${table} WHERE ?`, {id: 1}).then(res=>{
    logger.debug(res);
});

在进行INSERTUPDATE等操作时可以直接使用对象字面量来简化占位符(只需要一个就够了),不过这种情况对于WHERE操作的><等筛选条件就无能为力了

2.4. 多查询

可以在一条query语句中执行多次查询操作,

query(`SELECT 1; SELECT 2`).then(res=>{
    logger.debug(res[0]);
    logger.debug(res[1]);
}).catch(err=>{
    logger.debug(err);
})

需要在createConnection()createPool()的配置参数中配置multipleStatements: true,不过这项操作的意义感觉不是很大~

2.5. 事务

事务用来维护数据库的完整性,他保证成批的mysql操作要么完全执行,要么完全不执行。

使用事务的前提是数据库引擎得支持事务才行,这里采用InnoDBmysql提供了对应的方法来来建立数据库事务,

// START TRANSACTION
conn.beginTransaction((err)=>{
    assert.ifError(err);
    let thread = conn.query(sql, values, (err, res)=>{
        if (err){
            // ROLLBACK
            conn.rollback(()=>{
                throw err;
            });
        }

        // do something

        // COMMIT
        conn.commit((err)=>{
            if (err){
                conn.rollback(()=>{
                    throw err;
                });
            }
        })
    });
});

三个方法对应的就是MySQL的事务操作,不过事务这块我也不熟(好吧,数据库我根本就不会~)

2.6. 其他

增删改操作跟查询最大的区别在于对于返回值的需求不同,下面有几个很有用的特性:

  • 获取插入记录的id值res.insertId
  • 获取增删改操作影响的行数res.affectedRows
  • 获取更新操作改变的行数res.changedRows

3. 封装

之前把博客后台从PHP迁移到Node的时候写了一个简陋的数据库操作类,主要是对操作进行了Promise封装。起初能满足基本的需求,但是在开发过程中逐渐发现了一些问题

  • 每个模型实例都维护了一个连接对象,因此不能再调用之后关闭连接(压根不知道连接池这种东西~)
  • 为了满足链式调用,许多方法都只是为最后拼接的sql语句服务,也没有用占位符啥的
  • 没有考虑错误处理
  • 代码写的太烂了,强行面向对象~

这两天翻了mysqljs的文档之后,决定重写这个模型类,代码已经整合在github上,请多多指教。