mysql - Node.js mysql 事务

Question

谁能提供我如何在 Node.js 中实现 MySQL 事务的示例。我正在尝试使用 node-mysql 驱动程序和 node-mysql-queue。

据我所知，使用 node-mysql-queue 大大降低了 Node.js 的异步特性，因为新查询必须等到现有查询完成。为了解决这个问题，有没有人尝试将 node-mysql-queue 与 node-mysql 的连接池功能结合起来。即为每个新的 http 请求启动一个新的 mysql 连接，并在各个连接上启动事务队列？

Answer 1

更新

请参阅下面的编辑了解 async/await 语法

---

我花了一些时间写了一个node mysql给出的事务示例的通用版本，所以我想在这里分享一下。我使用 Bluebird 作为我的 promise 库，并用它来“ promise ”连接对象，这大大简化了异步逻辑。

const Promise = ('bluebird');
const mysql = ('mysql');

/**
 * Run multiple queries on the database using a transaction. A list of SQL queries
 * should be provided, along with a list of values to inject into the queries.
 * @param  {array} queries     An array of mysql queries. These can contain `?`s
 *                              which will be replaced with values in `queryValues`.
 * @param  {array} queryValues An array of arrays that is the same length as `queries`.
 *                              Each array in `queryValues` should contain values to
 *                              replace the `?`s in the corresponding query in `queries`.
 *                              If a query has no `?`s, an empty array should be provided.
 * @return {Promise}           A Promise that is fulfilled with an array of the
 *                              results of the passed in queries. The results in the
 *                              returned array are at respective positions to the
 *                              provided queries.
 */
function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            'Number of provided queries did not match the number of provided query values arrays'
        )
    }

    const connection = mysql.createConnection(databaseConfigs);
    Promise.promisifyAll(connection);
    return connection.connectAsync()
    .then(connection.beginTransactionAsync())
    .then(() => {
        const queryPromises = [];

        queries.forEach((query, index) => {
            queryPromises.push(connection.queryAsync(query, queryValues[index]));
        });
        return Promise.all(queryPromises);
    })
    .then(results => {
        return connection.commitAsync()
        .then(connection.endAsync())
        .then(() => {
            return results;
        });
    })
    .catch(err => {
        return connection.rollbackAsync()
        .then(connection.endAsync())
        .then(() => {
            return Promise.reject(err);
        });
    });
}

如果您想按照问题中的建议使用池，您可以使用 myPool.getConnection(...) 轻松切换 createConnection 行，然后切换connection.end 行与 connection.release().

---

编辑

我使用 mysql2 库(与 mysql 相同的 api，但支持 promise)和新的 async/await 运算符对代码进行了另一次迭代。就是这样

const mysql = require('mysql2/promise')

/** See documentation from original answer */
async function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            'Number of provided queries did not match the number of provided query values arrays'
        )
    }
    const connection = await mysql.createConnection(databaseConfigs)
    try {
        await connection.beginTransaction()
        const queryPromises = []

        queries.forEach((query, index) => {
            queryPromises.push(connection.query(query, queryValues[index]))
        })
        const results = await Promise.all(queryPromises)
        await connection.commit()
        await connection.end()
        return results
    } catch (err) {
        await connection.rollback()
        await connection.end()
        return Promise.reject(err)
    }
}