複数のレコードをまとめて登録する

app.js

var formRouter = require('./routes/form');
app.use('/form', formRouter);

db.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'sample',
  password: 'password',
  namedPlaceholders: true, // 設定必須
});

module.exports = pool;

routes/form.js

var express = require('express');
var router = express.Router();
const pool = require('../content_db');

router.get('/', function(req, res, next){
  (async () => {
try {
  const [results, fields] = await pool.query('SELECT * FROM content');
  let data = { content: results };
  res.render('form/index', data);
} catch (err) {
  console.log(err);
}
  })();
});

router.get('/add', (req, res, next) => {
  res.render('form/add');
});

router.post('/add', (req, res, next) => {
  let txt = req.body['txt[]'];
  const rows = txt.map((txt) => [txt]);

  (async () => {
try {
  const [results, fields] = await pool.query('insert into content(txt) values :rows', {rows});
  res.redirect('/form');
} catch (err) {
  console.log(err);
}
  })();
});

module.exports = router;

views/form/index.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>form - index</title>
</head>
<body>
  <div id="wrapper">
<div id="header">
  <h1>form Index</h1>
</div>
<div id="content">
  <ul>
    <li><a href="/form/add">作成</a></li>
  </ul>
  <table>
    <tr>
      <th>ID</th>
      <th>text</th>
    </tr>
    <% for(let i in content){ %>
      <% let obj = content[i]; %>
      <tr>
        <td><%= obj.id %></td>
        <td><%= obj.txt %></td>
      </tr>
    <% } %>
  </table>
</div>
  </div>
</body>
</html>

views/form/add.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
  <title>form - Add</title>
</head>
<body>
  <div id="main">
<div id="header">
  <h1>form Add</h1>
</div>
<form method="post" action="/form/add">
  <p>text:<input type="text" name="txt[]" id="name"></p>
  <p>text:<input type="text" name="txt[]" id="name"></p>
  <p><input type="submit" value="作成"></p>
</form>
  </div>
</body>
</html>

複数のレコードをそれぞれ更新する

id	name
1	Joe
2	Alex
3	Mike

上記のような content テーブルがあり、「 id が 1 の name カラムを太郎」、「 id が 2 の name カラムを次郎」、「 id が 3 の name カラムを三郎」にそれぞれ一度に変更したい場合は次のようにします。

script.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'sample',
  password: 'password',
  namedPlaceholders: true, // 設定必須
});

(async () => {
  try {
let number = [1, 2, 3]
let data = ['太郎','次郎','三郎']
await pool.query("UPDATE content SET  name = ELT(FIELD(id,:number), :data) WHERE id IN (:number)", {number, data});
  } catch (err) {
console.log(err);
  }
  pool.end();
})();