5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Node.js MySQLのクエリでプレースホルダーを使う

Posted at

##前置き
Lambdaなどでmysqlを実行するにあたっての簡易メモ
仕様は公式から参照

.query(sqlString, values, callback)でプレースホルダー値を利用する
(connection作成は省略します。)

  • 第一引数のSQLに**?**を指定する。
  • 第二引数に配列を渡す。

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

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

.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)
*/
5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?