MySQL 範囲パーティションを作成する

作成日 2022.04.15
更新日 2022.10.15
mysql
mysql

MySQLで、範囲パーティションを作成する手順を記述してます。

1. 環境
2. 手順
3. パーティション作成
4. 範囲外のデータをinsert

環境

OS ubuntu21.10
MySQL Ver 8.0.27-0ubuntu0.21.10.1 for Linux on x86_64 ((Ubuntu))
MySQL Workbench 8.0.27

Mysql 基本的な使い方はこちら

手順

以下の「sample」テーブルを作成して、年度ごとにパーティションしてみます。

CREATE TABLE `sample` (
  `id` INT AUTO_INCREMENT,
  `name`  VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `created_at`) 
);

データは、ストアドで作成してます。作成方法は、ストアドで連続した日付データを作成するから確認できます。

SELECT * FROM sample;

実行結果

まずは現在のパーティションの状態を確認してみます。

SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_ORDINAL_POSITION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='sample';

実行結果を見ると、まだパーティションが作成されていないことがわかります。

パーティション作成

年度ごとにパーティションを作成してみます。

ALTER TABLE `sample`
PARTITION BY RANGE COLUMNS(`created_at`) (
  PARTITION  pat2020 VALUES LESS THAN ('2020-01-01 00:00:00'),
  PARTITION  pat2021 VALUES LESS THAN ('2021-01-01 00:00:00'),
  PARTITION  pat2022 VALUES LESS THAN ('2022-01-01 00:00:00'),
  PARTITION  pat2023 VALUES LESS THAN ('2023-01-01 00:00:00')
)

確認してみます。
※少し時間がたってから確認します。

SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_ORDINAL_POSITION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='sample';

パーティションが作成されていることが確認できます。

パーティションを作成すると、以下のようにパーティションを指定してクエリを実行することが可能です。

 SELECT COUNT(*) from sample
 PARTITION (`pat2021`);

実行結果

範囲外のデータをinsert

パーティションの範囲外にあるデータをinsertすることはできません。

insert into sample (name,created_at) values('data1001','2024-01-01');

insert into sample (name,created_at) values('data1001','2024-01-01')	
Error Code: 1526. Table has no partition for value from column_list	0.000 sec

updateも同様です。

update sample set created_at = '2024-01-01' where id = 1

update sample set created_at = '2024-01-01' where id = 1	
Error Code: 1526. Table has no partition for value from column_list	0.000 sec