バッチ処理で、自動でパーティション追加、データ登録、パーティション削除をする処理を作り、それに必要なSQL部分を抜粋しました。
やりたいこと（実際に作った処理とは異なります）
- あるゲームのプレイ結果を、日時で集計し、集計結果を画面に表示したい。
- バッチ処理で、集計し、結果を過去1週間分を保持し、過去分は毎日削除したい。
- 最新のパーティションからデータを取得したい。
事前準備
テーブルを作る
CREATE TABLE IF NOT EXISTS `game_point_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` INT NOT NULL COMMENT 'ユーザーID',
`point` INT UNSIGNED NOT NULL COMMENT 'ポイント',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
PRIMARY KEY (`id`, `created_at`))
ENGINE = InnoDB
PARTITION BY RANGE COLUMNS(created_at)(
PARTITION p20210113 VALUES LESS THAN ('2021-01-13'),
- パーティションの種類は、「RANGE」にします。
- パーティションのキーには、created_atを使います。
- パーティションのキーは、PRIMARY KEYにしないといけないので、id、created_atをプライマリーキーに指定します。
- パーティションはバッチ処理で追加しますが、１つだけ事前に作っておきます。
バッチ処理
以下の順で処理します。
1. 追加するパーティション名を決める。
ここでは日付とします。（例:2021年1月23日なら、p20210123）
2. パーティションを追加する
ALTER TABLE game_point_log ADD PARTITION (
PARTITION [パーティション名] VALUES LESS THAN ('[日付条件、どの日時までを対象にするか？]')
);
例：
ALTER TABLE game_point_log ADD PARTITION (
PARTITION p20210124 VALUES LESS THAN ('2021-01-24')
);
- 日付をキーに作ります。
- LESS THANは、既存のパーティションの条件〜指定した日付までの間のデータを入れるという意味なので、今日の日付データを入れたい場合は、翌日の日付にすると良いです。
3. データを登録する
DB側でパーティションに入れてくれるので、通常のINSERT文で登録できます。
4. 古くなったパーティションを削除する
古くなったパーティションを一発で削除するコマンドはない（たぶん・・・。）
ので、今使っているパーティションを取得して、その中から削除したいパーティションを取得します。
EXPLAIN PARTITIONS SELECT * FROM game_point_log
SQLを実行すると、partitionsという項目で、カンマ区切りでパーティション名が取得できます。
例：p20210124,p20210125,p20210126
それをカンマで分割で配列化し並び替えたりして消したい等でパーティションを探し、
ALTER TABLE game_point_log DROP PARTITION [パーティション名]
例：
ALTER TABLE game_point_log DROP PARTITION p20210124
で削除します。
取得処理（最新のパーティションからデータ取得）
最新のパーティションを自動で選択して取得してくるコマンドはない（たぶん・・・。）
ので、取得する時も、古くなったパーティションを削除する時に使ったEXPLAINコマンドを使って、partitionsを取得し、自分で探します・・。
調べたパーティションを指定して、
SELECT * FROM game_point_log PARTITION ([パーティション名]);
例:
SELECT * FROM game_point_log PARTITION (p20210113);
とすると、対象のパーティションに入っているデータ（対象の日付データのみ）が取得できるので、where文なしで取得でき便利です。