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

結果は、

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

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

MariaDB: AUTO_INCREMENTをリセット、もしくは任意の数字から始める方法

AUTO_INCREMENTを使ってテストをしている時に、TABLEをDELETEしてもその前が2で終わっていたら3から始まってしまいます。

本番ではいいのですが、やはりテストでは1から始まって欲しいときが多々あると思います。

すぐに忘れてしまうので自分用にメモ。

ALTER TABLE table_hoge AUTO_INCREMENT = 1;

応用して15から始めたいときは、

ALTER TABLE table_hoge AUTO_INCREMENT = 15;

 

MySQL: 外部結合してDELETEでエラー

外部結合する場合はどのTABLEで削除するのかが不明になるのでTABLEを指定してあげないとエラーを起こす。

DELETE table_1 FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.id WHERE table_2.id IS NULL;

もちろんASを使っても可能。

DELETE a FROM table_1 AS a LEFT JOIN table_2 AS b ON a.id = b.id WHERE b.id IS NULL;

 

PHP:MySQLのトランザクションの雛形

自分用にメモです。
PHPとMySQLでトランザクションする時の雛形です。
面倒なのでコピペ用にメモ。
ちなみに今となってはデフォになっていたと思いますが、トランザクションはInnoDBでないと意味がありません。
以前のデフォであるMyISAMでは意味なし。

//トランザクション開始
$lock_record='START TRANSACTION';
$query_lock=mysql_query($lock_record, $connect);

$update='UPDATE hoge SET hage=1 WHERE hogehage=1';
$query_update=mysql_query($update, $connect);

//ロック解除
$lock_record='COMMIT';
$query_lock=mysql_query($lock_record, $connect);