SELECT

.js

const sql = "SELECT * FROM sample WHERE id = ?";
const data = [1];
const query = connection.query(sql, data, (err, results, fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); // SELECT * FROM testdb.sample WHERE id = 1

フィールド名に使う場合は**??**を指定する。

.js

const sql = "SELECT ?? FROM ?? WHERE id = ?";
const columns = ['id', 'name', 'email'];
const data = [columns, 'users', 1]
const query = connection.query(sql, data, (err, results, fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); // SELECT id, name, email FROM users WHERE id = 1

INSERT

複数カラムを１つの**?**で対応できます。

.js

const sql = "INSERT INTO posts SET ?"
const post  = {id: 1, title: 'Hello MySQL'};
const query = connection.query(sql, post, (error, results, fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'

複数行のinsertを行う場合は**(?)**を使用する
※insertするレコード数分必要。

.js

const sql = "INSERT INTO users(name, age, sex) VALUES(?),(?),(?)"
const data = [['boy', 23, 'man'], ['girl', 14, 'woman'], ['guy', 42, 'man']];
const query = connection.query(sql, data, (error,results,fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); 
/*
INSERT INTO
 users(name, age, sex) 
VALUES 
 ('boy', 23, 'man'),
 ('girl', 14, 'woman'),
 ('guy', 42, 'man')'
*/

##UPDATE

.js

const sql = "INSERT INTO users SET name = ? WHERE id = ?"
const data  = ['佐藤', 1];
const query = connection.query(sql, data, (error, results, fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); // INSERT INTO users SET name = '佐藤' WHERE id = 1

複数行もinsertと同じ。
onduplicate key updateのサンプル

.js

const sql = "INSERT INTO users(name, age, sex) VALUES(?),(?) ON DUPLICATE KEY UPDATE age = VALUES(age) "
const data = [['boy', 25, 'man'], ['girl', 16, 'woman']];
const query = connection.query(sql, data, (error,results,fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); 
/*
INSERT INTO
 users(name, age, sex) 
VALUES 
 ('boy', 25'man'),
 ('girl', 16, 'woman')
ON DUPLICATE KEY UPDATE age = VALUES(age)
*/