MySQL

以前の職場ではPostgreSQLを使っていたが、今の職場ではMySQLを使っている。 一応、今進めているプロジェクトでどんなDBMSを使うかは一任されていたが、会社としてメインで使っているのがMySQLだったので選択。

以下、気が付いたことを記す。

MySQLには、FULL OUTER JOINが無い

これはちょっと驚いた。というか、SQL標準とは一体・・・。

一応、LEFT OUTER JOIN を2回（または読みやすくLEFT,RIGHT一回ずつ等）とUNIONで実現できる。

    SELECT lf1,rf2 FROM lt LEFT OUTER JOIN rt ON lt.lf1 = rt.rf1

    UNION

    SELECT lf1,rf2 FROM lt RIGHT OUTER JOIN rt ON lt.lf1 = rt.rf1

MySQLの結合アルゴリズムはNested Loop（と、その派生アルゴリズム）のみ

数百万レコードを対象にして複数のフィールドでGROUP BYすると厳しい。 この点、PostgreSQLはメモリを犠牲にしてHASH JOINしてくれるのでありがたいですね（場合によってはNested Loopの数百倍以上高速）

MySQLにはgenerate_series的なものが無い

一応、ユーザー変数を使ってそれっぽいことはできる。 が、生成したいレコード数分が存在しているテーブル（仮想テーブルでもOK）を利用する必要があるので、 information_schemaを使うかunionとcross joinで仮想テーブルを爆発させるかしなければならない。

歯抜けデータ補完で適用されることが多いのは日付関連だとは思うが、いっそのこと実テーブルを作っておくべきではある。

以下に、私がお勧めするカレンダーテーブル作成文を記す。

    CREATE TABLE date_calendar( date date );


    INSERT INTO date_calendar(date)

    SELECT
      @date:='1500-01-01' as date
    UNION ALL
    SELECT
      @date:=DATE_ADD(@date, INTERVAL 1 DAY)
    FROM
      (
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj1
      CROSS JOIN
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj2
      CROSS JOIN
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj3
      CROSS JOIN
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj4
      CROSS JOIN
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj5
      CROSS JOIN
    (
      (SELECT 1 AS f1,1) UNION (SELECT 2,2) UNION (SELECT 3,3) UNION (SELECT 4,4) UNION (SELECT 5,5) UNION
      (SELECT 6,6) UNION (SELECT 7,7) UNION (SELECT 8,8) UNION (SELECT 9,9) UNION (SELECT 10,10)
    ) AS cj6
      )
    WHERE
      @date < '2500-01-01';

とりあえずこれだけの範囲をカバーしていれば問題ないだろうし、 レコード数も数十万程度（＝数十万日）なのでパフォーマンス上も大した問題にならないだろう。

prev: 趣味のアクアリウムについて
next: Perlは過小評価されすぎでは。
created at : 2020-04-01 15:21:40
updated at : 2020-04-01 15:21:40
author : Toshiaki Yokoda