MySQL: エラー`this is incompatible with sql_mode=only_full_group_by`回避方法

#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hoge' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

このエラー、何度も何度も手を止められて、その都度解決策は違います。

もうムリぽ。

MySQL5.6まではこれでもイけました。
でもバージョン5.7から標準のSQL仕様になったためこのエラーを出すようになりました。

つまり今までがMySQLの方言だったのですが、もう方言で結構という場合の解決策です。

SET session sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

を頭に入れるだけ。
SET sessionにすればold_modeに一々戻す必要がなくなります。

Node.jsならまんま、

const sqlSet = `SET session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"`;
await conn.query(sqlSet);

const sql = `SELECT id, name FROM hoge GROUP BY foo`;
const [rows] = await conn.query(sql);

と書けばイケます。

エラーの原因ですが、例えば上の例だと、fooでGROUP BYすると複数のidとnameがあるのに、何を一つだけ表示すればいいのかわかんなーい、とSQLは言ってくるのです。

そりゃそうなんだけどテキトーでいいよ、という今までの場合のほうが圧倒的に多いってことですね。

node.js: mongoDBに繋げて日付で検索

JavaScriptで日時の取り扱いって本当に面倒くさいですよね。
node.jsとmongoDBで日時を指定して検索したい時、単純に

col.find({ "datetime": ISODate("2018-12-11T04:31:09Z") });

と書いても動いてくれません。

日時をISO形式のobjectで渡せば動くのですが、んじゃISO形式のobjectで渡すのってどうやるんだよ、とか・・・wワッカンネー

今回仕事でバックエンドを担当していて、フロントから「2019/01/18」という形式で日付を渡されました。
これを単純にobjectにしてみます。

const date = '2019/01/18';
const result = new Date(date);
console.log(result); // 2019-01-17T15:00:00.000Z

一見日時がズレてるように見えますが9時間前、つまりUTCのISO形式のobjectになっているので、mongoDBにそのまま渡せば日本時間で検索できます。

JavaScriptはブラウザの種類やサーバーなどの環境で、返される日時がUTCだったりします。

その場合resultが2019-01-18T00:00:00.000Zになると思うのですが、そのまま使うと日本時間ではなくなります。

強制的にUTCにされたらいったんUNIXtimeに変換し、momentモジュールを使用して、UTCに変換してからobjectに変換するといいと思います。

const moment = require('moment');

const date = '2019/01/18';
const unixTime = Date.parse(date); // 1547737200000
const momentTime = moment(unixTime); // moment("2019-01-18T00:00:00.000")
const result = new Date(momentTime); // 2019-01-17T15:00:00.000Z
console.log(result);

面倒くさいっすな〜・・・。

momentのオフィシャル・ドキュメントはこちら。
https://momentjs.com/docs/

objectに変換した日時を使ってmongoDBで2019/01/18以降のデータを検索する場合は、以下のようなコードになります。

'use strict';

const MongoClient = require('mongodb').MongoClient;

const main = async () => {
  const client = await MongoClient.connect('mongodb://localhost:27017', { useNewUrlParser: true });
  const col = client.db('hoge').collection('foo');

  // 日時をobjectに変換
  const date = '2019/01/18';
  const dateObj = new Date(date); // 2019-01-17T15:00:00.000Z

  const query = { 'date': { '$gte': dateObj } };
  const result = await col.find(query).toArray();
  console.log(result);
  client.close();
};

main();

node.jsはすぐに古い仕様を切り捨てるので、古い記事にはくれぐれもご注意を。

node.js: mongoDBで指定したフィールドのみ表示、ソート、リミットを設定する

node.jsは日々バージョンアップしていくので、巷に溢れたサンプルコードがすぐ役立たずになります。
node.jsがバージョンアップすると、モジュールもスゴいスピードでバージョンアップしていきます。

mongoDBで表示するフィールドを指定して、ソートしてfindしたかっただけなのですが、あまりにも古いソースが多かったので備忘録です。

mongodb driverの公式サイト最新版(v3)はこちら。
http://mongodb.github.io/node-mongodb-native/3.0/api/Collection.html

下記は表示させるフィールドを指定して、フィールド「user_id」でソート(降順)、10件のみ表示、ただしフィールド「_id」は非表示にするという例です。

'use strict';

const MongoClient = require('mongodb').MongoClient;

const main = async () => {
  const client = await MongoClient.connect('mongodb://localhost:27017', { useNewUrlParser: true });
  const db = client.db('hoge');
  const col = db.collection('foo');

  const project = {
    _id: 0,
    user_id: 1,
    mail_address: 1
  };
  const sort = [['user_id', -1]];
  const limit = 10;

  const data = await col.find().project(project).sort(sort).limit(limit).toArray();
  client.close();
};

main();

今までずっと古いサンプルコードに騙されていたのですが、mongodb driver Ver.3ではフィールドを表示、非表示する場合「fields」プロパティではなくて「project」メソッドです。

sortさせる場合昇順は1、降順は-1です。

オプションをfind()の中に入れるのではなくて、芋づる形式で書くんですね。

他にもいっぱい罠がありそうなので、当たり前ですが公式マニュアルを読みましょう! 見にくいのが仇ですがw

node.js:mongoDB接続の雛形(ES2017準拠)

node.jsでmongoDBに接続する際のひな型、現時点での最新版です。

このような↓collectionを予め作っておくとします。

> db.test.find();
{ "_id" : ObjectId("5b548f2fb19c25cc3796e11c"), "name" : "福沢諭吉", "price" : "10000" }
{ "_id" : ObjectId("5b548f2fb19c25cc3796e11d"), "name" : "夏目漱石", "price" : "1000" }
{ "_id" : ObjectId("5b548f2fb19c25cc3796e11e"), "name" : "野口英世", "price" : "1000" }
{ "_id" : ObjectId("5b548f2fb19c25cc3796e11f"), "name" : "新渡戸稲造", "price" : "5000" }
{ "_id" : ObjectId("5b548f2fb19c25cc3796e120"), "name" : "樋口一葉", "price" : "5000" }

単純にmongoの中身を表示させる場合、

'use strict';

const MongoClient = require('mongodb').MongoClient;

const main = async () => {
  const client = await MongoClient.connect('mongodb://localhost:27017', { useNewUrlParser: true });
  const db = client.db('mongo_test');
  const col = db.collection('test');

  const data = await col.find().toArray();
  console.log(data);

  client.close();
};

main();

結果は、

[ { _id: 5b548f2fb19c25cc3796e11c, name: '福沢諭吉', price: '10000' },
  { _id: 5b548f2fb19c25cc3796e11d, name: '夏目漱石', price: '1000' },
  { _id: 5b548f2fb19c25cc3796e11e, name: '野口英世', price: '1000' },
  { _id: 5b548f2fb19c25cc3796e11f, name: '新渡戸稲造', price: '5000' },
  { _id: 5b548f2fb19c25cc3796e120, name: '樋口一葉', price: '5000' } ]

今度は繰り返しの場合の書き方です。
「福沢諭吉」だったらmongoDBのドキュメントを「聖徳太子」に置換してみましょう。

'use strict';

const MongoClient = require('mongodb').MongoClient;

const main = async () => {
  const client = await MongoClient.connect('mongodb://localhost:27017', { useNewUrlParser: true });
  const db = client.db('mongo_test');
  const col = db.collection('test');

  const cursor = col.find();
  while (await cursor.hasNext()) {
    const data = await cursor.next();

    if (data.name == '福沢諭吉') {
      const sub = { name: "聖徳太子" };
      await col.updateOne({ _id: data._id }, { $set: sub });
    }
  }
  client.close(); // コールバックしないのでawaitいらない
};

main();

mongoDBで確認すると、諭吉だった部分だけ

{ "_id" : ObjectId("5b548f2fb19c25cc3796e11c"), "name" : "聖徳太子", "price" : 10000 }

に変更されています。

async/awaitの使い方が本当に難しいのですが、ドキュメントの数を30万くらいに増やしたり、awaitをわざと外してみて挙動を確認すると、なんとなく段々とわかってきます、たぶん。。。

node.js:MySQL接続の雛形(ES2017準拠)

node.jsでMySQLに接続する際のひな型、現時点での最新版です。

このような↓TABLEを予め作っておくとします。

MariaDB [test]> select * from hoge;
+----+-----------------+
| id | fuga            |
+----+-----------------+
|  1 | 福沢諭吉        |
|  2 | 夏目漱石        |
|  3 | 野口英世        |
|  4 | 新渡戸稲造      |
|  5 | 樋口一葉        |
+----+-----------------+

 
■MySQLからデータを取得する場合

'use strict';

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

// MySQL接続設定
const config = {
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
};

const main = async () => {
  // MySQL接続
  const conn = await mysql.createConnection(config);

  let rows, fields;
  try {
    const sql = 'SELECT id FROM hoge WHERE fuga = ?';
    [rows, fields] = await conn.query(sql, ['夏目漱石']);
  } catch (err) {
    if (err) throw err;
  }
  console.log(rows, fields);

  // MySQL切断
  await conn.end();
};

main();

結果は、

[ TextRow { id: 2 } ] [ { catalog: 'def',
    schema: 'test',
    name: 'id',
    orgName: 'id',
    table: 'hoge',
    orgTable: 'hoge',
    characterSet: 63,
    columnLength: 11,
    columnType: 3,
    flags: 16899,
    decimals: 0 } ]

fieldsにはフィールド情報が入ってくるのですが、ほとんど必要ないので自分はいつも省略しています。

idの結果「2」だけ欲しい場合は、

console.log(rows[0].id)

 

■forEachで繰り返し処理をしたい場合

ES2017のasync/awaitは今のところforEachに対応していません。
仕方ないのでモジュールを使うわけですが、たくさんあります。

とりあえず自分はp-mapが一番早かったので使っていますが、似たような名前の別のモジュールがあるので気をつけてください。

それでは上記のTABLEで、値が「福沢諭吉」の場合「聖徳太子」に置換するプログラムを作ってみます。

'use strict';

const mysql = require('mysql2/promise');
const pMap = require('p-map');

// MySQL接続設定
const config = {
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
};

const main = async () => {
  // MySQL接続
  const conn = await mysql.createConnection(config);

  let rows;
  try {
    const sql = 'SELECT id FROM hoge';
    [rows] = await conn.query(sql);
  } catch (err) {
    if (err) throw err;
  }

  await pMap(rows, async data => {
    try {
      const sql2 = 'SELECT fuga FROM hoge WHERE id = ?';
      const [rows2] = await conn.query(sql2, [data.id]);

      let name = rows2[0].fuga;
      if (name == '福沢諭吉') {
        name = '聖徳太子';
      }
      console.log(name);
    } catch (err) {
      if (err) throw err;
    }
  });

  // MySQL切断
  await conn.end();
};

main();

結果は、

聖徳太子
夏目漱石
野口英世
新渡戸稲造
樋口一葉

諭吉が聖徳太子に置換されています。