あいつの日誌β

働きながら旅しています。

集計ログで使用するテーブルをパーティションするときのサンプル

SQL

あらすじ

テストデータを用意するのに適当 SQL を拝借しようと他人様のブログを漁りまして いろいろと自分ように編集したものを後日の自分のためにここに記述しておきます。

では参る

適当なデータベースをつくっておく

create database test_partition;
use test_partition;

テーブルを作成

CREATE TABLE logs (
    id INT NOT NULL AUTO_INCREMENT,
    action VARCHAR(1024) NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id, created),
    INDEX(action)
) ENGINE=InnoDB
PARTITION BY RANGE( TO_DAYS(created) ) (
    PARTITION p201501 VALUES LESS THAN ( TO_DAYS('2015-02-01') ),
    PARTITION p201502 VALUES LESS THAN ( TO_DAYS('2015-03-01') ),
    PARTITION p201503 VALUES LESS THAN ( TO_DAYS('2015-04-01') ),
    PARTITION p201504 VALUES LESS THAN ( TO_DAYS('2015-05-01') ),
    PARTITION p201505 VALUES LESS THAN ( TO_DAYS('2015-06-01') ),
    PARTITION p201506 VALUES LESS THAN ( TO_DAYS('2015-07-01') ),
    PARTITION p201507 VALUES LESS THAN ( TO_DAYS('2015-08-01') ),
    PARTITION p201508 VALUES LESS THAN ( TO_DAYS('2015-09-01') ),
    PARTITION p201509 VALUES LESS THAN ( TO_DAYS('2015-10-01') ),
    PARTITION p201510 VALUES LESS THAN ( TO_DAYS('2015-11-01') ),
    PARTITION p201511 VALUES LESS THAN ( TO_DAYS('2015-12-01') ),
    PARTITION p201512 VALUES LESS THAN ( TO_DAYS('2016-01-01') ),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

テストデータ投入

INSERT INTO logs (action, created) VALUES ('A', '2015-01-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-02-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-03-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-04-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-05-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-06-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-07-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-08-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-09-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-10-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-11-01');
INSERT INTO logs (action, created) VALUES ('A', '2015-12-01');
INSERT INTO logs (action, created) VALUES ('A', '2016-01-01');

確認

select count(*) from logs;
select PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME = 'logs';

パーティション削除

ALTER TABLE logs DROP PARTITION p201501;
select count(*) from logs;
select PARTITION_NAME from INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME = 'logs';

お掃除

drop database test_partition;

おしまい