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
Jone
在什么样的花园里面,挖呀挖呀挖
种什么样的种子,开什么样的花
随机推荐
Node.js MySQL 连接池和事务
MySQL 批量插入数据时如何解决重复问题
Node.js 18.x 开始支持内置单元测试
MySQL 表名预处理
JavaScript 中 0.1 加 0.2 不等于 0.3 的原因和解决方法
支持 Selector API 的 HTML 解析器 node-html-parser
RESTful API 执行 delete 返回204无法获取 Body
使用 svg 作为背景图片
Linux 中 top 命令的 Load Average 含义
什么是 RESTful API 的幂等性

微信联系我

夜间模式切换
回到顶部