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();
 }



声明:本站所有文章和图片,如无特殊说明,均为原创发布,转载请注明出处。
随机推荐
JavaScript DOM 元素增删改
JavaScript EventSource 服务器发送事件 Server-Sent Events(SSE)
WordPress 后台添加菜单
WordPress RESTful API 的授权方式
Node.js stream 流
JavaScript audio 教程
Node.js crypto 模块
JavaScript 操作表单