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

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