end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

pkeyがauto_incrementの場合、PARTITION BY RANGEは不可?

mysql 5.1では↓こうらしい。主キーを変更する訳にはいかないんですけど

mysql> desc juchuu;
+-------------------------+-------------+------+-----+---------+----------------+
| Field                   | Type        | Null | Key | Default | Extra          |
+-------------------------+-------------+------+-----+---------+----------------+
| anken_id                | int(11)     | YES  |     | NULL    |                |
| id                      | int(11)     | NO   | PRI | NULL    | auto_increment |
| official_id             | varchar(6)  | YES  | UNI | NULL    |                |
| juchuu_type             | varchar(16) | YES  |     | NULL    |                |
| mitsumori_date          | date        | YES  |     | NULL    |                |
| keiyaku_date            | date        | YES  |     | NULL    |                |
|  :                      |             |      |     |         |                |
+-------------------------+-------------+------+-----+---------+----------------+
53 rows in set (0.00 sec)

mysql> ALTER TABLE juchuu
    -> PARTITION BY RANGE( id ) (
    -> PARTITION p_0 VALUES LESS THAN ( 20000),
    -> PARTITION p_1 VALUES LESS THAN ( 40000),
    -> PARTITION p_2 VALUES LESS THAN ( 60000),
    -> PARTITION p_3 VALUES LESS THAN ( 80000),
    -> PARTITION p_4 VALUES LESS THAN (100000),
    -> PARTITION p_5 VALUES LESS THAN (120000),
    -> PARTITION p_6 VALUES LESS THAN (140000),
    -> PARTITION p_7 VALUES LESS THAN (160000),
    -> PARTITION p_8 VALUES LESS THAN (180000),
    -> PARTITION p_9 VALUES LESS THAN (200000),
    -> PARTITION p_max VALUES LESS THAN MAXVALUE
    -> );
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
mysql> 

って、UNIQUE制約を付けたofficial_idカラムのことを怒っているんですね。
partition by range...扱いが難しい