Node.js MySQL2 如何编写事务

方法一,

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

START TRANSACTION 和 COMMIT 不可以使用 conn.execute,

MySQL limits which statements can be done in prepared statements, start transaction is not allowed. See SQL Syntax Allowed In Prepared Statements and here is a demonstration.

execute is always using and caching prepared statements. This is good if the query is complex (MySQL doesn't have to parse it every time) or takes arguments (for security).

However, if your query is simple and does not take any arguments there's no need to prepare it. Use query which just runs the SQL. This both avoids the error you're getting, and it avoids filling up the prepared statement cache.

方法二,

 let sql = 'select * from tb_user limit 1';
 let values = [];

 const pool = mysql.createPool({
  host: config.database.host,
  port: config.database.port,
  database: config.database.database,
  user: config.database.username,
  password: config.database.password,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
 });

 let conn = null;
 try {
  conn = await pool.getConnection();
  await conn.beginTransaction();

  const [response, meta] = await conn.query(sql);
  await conn.execute(sql, values);
  await conn.execute(sql, values);
  console.log(response);

  await conn.commit();
 } catch (error) {
  if (conn) await conn.rollback();
  throw error;
 } finally {
  if (conn) await conn.release();
 }



真诚赞赏,手留余香
赞赏
PHP
WordPress 支持事务
2022-06-15
前端框架,前端
Vue3 挂载全局方法
2022-06-23
ngtwewy
随机推荐
Nginx 的 location 设置
XXS(跨站脚本攻击) 的防御 (HTML注入)
如何创建一个NPM Package
MySQL 处理高并发,防止库存超卖(乐观锁和悲观锁)
HTTP 消息头 Content-Disposition
Debian11 安装笔记2:编译安装PHP
中文正则表达式匹配
实现图片懒加载的两种方法
Koa.js 中间件 koa-body
ReferenceError: __dirname is not defined in ES module scope

微信联系我

夜间模式切换
回到顶部