オートナンバー型のカラムを含むテーブルをパーティショニング化する方法

非パーティショニングテーブルを後から、パーティショニングする際にかならずハマるのが”オートインクリメント列を含む”テーブルに関する、パーティショニングの時です
その回避策について、以下にまとめました。ご参考になればと思います。

例えば以下のようなテーブルの、updated_at をレンジパーティションキーとして、変更したいなんていう場合に

## 現在のテーブル定義

```sql
CREATE TABLE `test_ptable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `updated_at` datetime DEFAULT NULL COMMENT 'UTC-DATE',
  `domain` varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
;
```

### こんな変更を加えると・・・・

```sql
alter table test_ptable
PARTITION BY RANGE COLUMNS(updated_at) 
(
PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB
) ;
```

### こんな感じで怒られて作ることができません

```
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
```

つまり、パーティショニングには、プライマリキーが含まれていないといけないってことなのですが、例えば、そのプライマリーキーが、上記の表のようにオートインクリメントだったケースが多いのではないかと思います。

どうすればいいのか？・・

### プライマリーキーを一旦DROPしちゃって作りなおそう・・

はい、残念、エラーになります

```sql
 ALTER TABLE test_ptable DROP PRIMARY KEY;
```

じゃあ、オートインクリメントは諦めて、全部作りなおそう・・ってすでに稼働中のサービスだった場合なんか、そんなに簡単にできないですよね・・

ってことで。どうやればいいのか？

## 回避策

### ユニーク索引を作成する

ひとまずユニーク索引を作成します。複合ユニーク索引を作成するわけですが、その際に、レンジに含めたいカラムと、プライマリーキーを加えてください

```
create unique index ix01_test_ptable on test_ptable (id,updated_at) ;
```
### プライマリーキーをDROPする

既存のプライマリーキーを先ほどと同様の手順でDROPします

```
ALTER TABLE test_ptable DROP PRIMARY KEY;
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0
```

どうでしょうか？こんどは、DROPできたかと思います

### 再度プライマリーキーを作成する

新規作成し直すプライマリーキーは、updated_at を含めます
```
ALTER TABLE test_ptable ADD PRIMARY KEY (id,updated_at) ;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0
```

### 索引の削除

別に削除してもしなくてもいいのですが、同様のカラム構成で、プライマリキーと索引があるというのも気持ち悪いので、索引のほうは消してしまいましょう

* 現在の状態

```sql
 CREATE TABLE `test_ptable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
  `domain` varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
  PRIMARY KEY (`id`,`updated_at`),
  UNIQUE KEY `ix01_test_ptable` (`id`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
```
* 索引をDROPします

```sql
drop index ix01_test_ptable on test_ptable ;
```

* 削除後の結果

```sql
CREATE TABLE `test_ptable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
  `domain` varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
  PRIMARY KEY (`id`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 ;
``` 

## レンジパーティショニング化を再度チャレンジ

こんどはうまくいきましたね。

```sql
alter table test_ptable
PARTITION BY RANGE COLUMNS(updated_at) 
(
PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB
) ;
Query OK, 0 rows affected (0.10 sec)               
Records: 0  Duplicates: 0  Warnings: 0
```

## レンジパーティショニング化後のテーブル定義

こんな感じに変わったかと思います。

```
CREATE TABLE `test_ptable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
  `domain` varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
  PRIMARY KEY (`id`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(updated_at)
(PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
 PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
 PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB) */
```

如何でしょうか？お試しください。

作業前は予めテーブルはバックアップとっておいてくださいね