mysql2/promise：插入语句在事务查询中无效。

Question

感谢您的时间。我在使用mysql2/promise包创建事务查询时遇到了麻烦。

以下是查询语句：

    await db.execute(`START TRANSACTION`);
    await db.execute(`INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)`, [...userDetails]);
    await db.execute(`INSERT INTO account VALUES (?, ?, ?, ?, ?, ?)`, [...accountDetails]);
    await db.execute(`COMMIT`);

以下是我收到的错误信息：

Error: This command is not supported in the prepared statement protocol yet
code: 'ER_UNSUPPORTED_PS',
  errno: 1295,
  sql: 'START TRANSACTION',
  sqlState: 'HY000',
  sqlMessage: 'This command is not supported in the prepared statement protocol yet'

我想知道这是否与我的查询有关？我相信在事务块中使用INSERT语句应该是完全正确的。我还尝试将每个查询组合成一个字符串，但似乎也不起作用。

- Shockch4rge

---

错误出在 start transaction，而不是插入语句。这是 MySQL 还是 MariaDB？ - Schwern

请问您使用的是哪个版本的MySQL？ - Schwern

@Schwern 感谢您已经回答了问题，MySQL 8.0。 - Shockch4rge

Answer 1

MySQL限制了哪些语句可以在预处理语句中使用，start transaction是不允许的。参见SQL Syntax Allowed In Prepared Statements，这里有一个演示。

execute总是使用和缓存预处理语句。当查询复杂（MySQL不必每次解析）或需要参数（用于安全性）时，这是很好的选择。

然而，如果您的查询很简单且不需要任何参数，则无需准备它。使用query只运行SQL语句即可。这既避免了您所遇到的错误，也避免了填充预处理语句缓存。

    await db.query(`START TRANSACTION`);
    await db.execute(`INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)`, [...userDetails]);
    await db.execute(`INSERT INTO account VALUES (?, ?, ?, ?, ?, ?)`, [...accountDetails]);
    await db.query(`COMMIT`);

- Schwern

Answer 2

我认为问题的答案是正确的，但代码（例如）却不应该被使用。虽然这些语句可能会起作用，但它们并没有创建有效的事务，因为可能已经建立了与数据库的新连接。

正确的方法是获取一个连接句柄：

let conn = await this.getConnection();
await conn.query('START TRANSACTION');
// do your stuff here
await conn.execute('...')'
await conn.query('COMMIT');
conn.release();

这样可以确保有效的事务（START 和 COMMIT 应该在同一个连接上）。直接调用数据库对象并不会给你这个保证，除非你指定了只有一个连接。

- pizzamonster