Laravelでマイグレーションを実行する際にハマったのでメモ。
例として、注文データであるordersテーブルに対し、created_atの年単位でパーティションを作成したい場合。
実際のコード例
具体的なコード例は下記になります。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
Schema::create('orders', function (Blueprint $table) {
$table->unsignedBigInteger('id');
$table->string('item_code');
$table->integer('price');
// some columns...
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at', 0)->nullable();
$table->primary(['id', 'created_at']);
});
Schema::table('orders', function (Blueprint $table) {
$table->bigIncrements('id')->change();
});
DB::statement("ALTER TABLE `orders` PARTITION BY RANGE (unix_timestamp(created_at)) (
PARTITION p2020 VALUES LESS THAN (unix_timestamp('2021-01-01 00:00:00')),
PARTITION p2021 VALUES LESS THAN (unix_timestamp('2022-01-01 00:00:00')),
PARTITION p2022 VALUES LESS THAN (unix_timestamp('2023-01-01 00:00:00')),
PARTITION p2023 VALUES LESS THAN (unix_timestamp('2024-01-01 00:00:00')),
PARTITION p2024 VALUES LESS THAN (unix_timestamp('2025-01-01 00:00:00')),
PARTITION p2025 VALUES LESS THAN (unix_timestamp('2026-01-01 00:00:00')),
PARTITION p2026 VALUES LESS THAN (unix_timestamp('2027-01-01 00:00:00')),
PARTITION p2027 VALUES LESS THAN (unix_timestamp('2028-01-01 00:00:00')),
PARTITION p2028 VALUES LESS THAN (unix_timestamp('2029-01-01 00:00:00')),
PARTITION p2029 VALUES LESS THAN (unix_timestamp('2030-01-01 00:00:00')),
PARTITION p2030 VALUES LESS THAN (unix_timestamp('2031-01-01 00:00:00')),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
)");
|
ポイントとしては下記です。
Schema::create()でauto incrementなカラムを作成するとCREATE文でprimary keyになってしまうため、通常のintegerカラムを作成したあとでprimary keyを設定する必要がある- パーティションを貼るためにcreated_atカラムにデフォルト値が必要
- パーティション作成自体は該当メソッドが無いので
DB::statement()を使う必要がる
テーブルの確認
作成されたテーブルを show create table orders; のSQLを流して確認すると次のような内容となります。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE `orders` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`item_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (unix_timestamp(`created_at`))
(PARTITION `p2020` VALUES LESS THAN (1609426800) ENGINE = InnoDB,
PARTITION `p2021` VALUES LESS THAN (1640962800) ENGINE = InnoDB,
PARTITION `p2022` VALUES LESS THAN (1672498800) ENGINE = InnoDB,
PARTITION `p2023` VALUES LESS THAN (1704034800) ENGINE = InnoDB,
PARTITION `p2024` VALUES LESS THAN (1735657200) ENGINE = InnoDB,
PARTITION `p2025` VALUES LESS THAN (1767193200) ENGINE = InnoDB,
PARTITION `p2026` VALUES LESS THAN (1798729200) ENGINE = InnoDB,
PARTITION `p2027` VALUES LESS THAN (1830265200) ENGINE = InnoDB,
PARTITION `p2028` VALUES LESS THAN (1861887600) ENGINE = InnoDB,
PARTITION `p2029` VALUES LESS THAN (1893423600) ENGINE = InnoDB,
PARTITION `p2030` VALUES LESS THAN (1924959600) ENGINE = InnoDB,
PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
|