MySQLでカレンダーテーブルを作り、歯抜けの日付を埋める方法

モチベーション

これは勤怠システムを開発していたときの話だ。
アルバイトスタッフの個人のデータの画面で 1週間の勤務スケジュールを表示するという要件に対して、テーブルが下記の通りに既に組まれていた。
この際に1週間の勤務スケジュールを作成しようとすると、アルバイトスタッフが休みの日や、未定の日が歯抜けになってしまって都合がよくなかった。
※条件：新しくテーブルを作成したり、変更は入れないこと。

# 出勤テーブル
CREATE TABLE `work ` (
  `staff_id` int(8) NOT NULL AUTO_INCREMENT,
  `work_date` date NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
);

出勤テーブル - 表

実現したいこと

このように1週間の勤務スケジュールを表示したい。 なお歯抜けの日付も休みとして画面に表示したい。

結論

カレンダーテーブルを作成して、それに出勤テーブルをLEFT JOINする

カレンダーテーブルを作成するといっても実際にテーブを作成するわけではなく、サブクエリで日付だけの

SELECT
    calender.calender_date,
    work.staff_id,
    work.start_time,
    work.end_time
FROM
    (
    SELECT
        @date := CURRENT_DATE() AS calender_date
    UNION
        ALLSELECT @date := DATE_ADD(@date, INTERVAL 1 DAY)
    FROM
        `work`
    WHERE
        @date < DATE_ADD(CURRENT_DATE(), INTERVAL 6 DAY)
    ) AS calender
LEFT JOIN work ON work.work_date = calender.calender_date
AND work.staff_id = '00000001';

このやり方がわかりやすく、速度面でも最適であった。

カレンダーテーブルを取得するときに参照するテーブルをinformation_schemaのCOLUMNSにしている記事も見かけた。 実際に速度を比較してみたところinformation_schemaの方が若干遅かったためworkテーブルを結合している。

information_schema.COLUMNS 0.0551 秒

work 0.0046 秒

information_schemaとは

MySQLが持つデータベースに関するメタデータなどを保存しているもので、 私はInnoDBのテーブルのレコードの件数を取ったり、全カラム名を取得したりするときにたまに使う。

所感

もちろん要件に適していれば、お好みのプログラミング言語で一週間分の日付を取得してループしても良いかと思う。 そのためにモジュールを読み込んだり計算式を書いたりして可読性がを落としたり、パフォーマンスがむしろ落ちてしまうことがあるかもしれない。 そんな時にクエリ一発書いて解決できたらどれだけ良いことかと。。 すべては要件と状況次第。