1.mysql对分区有严格的规定
mysql的字段中如果有 primary key,那么 作为分区的字段一定需要在 primary key 中
mysql> create table tb_user_login_test(id bigint primary key auto_increment,dtEventTime datetime , iUin bigint not null default 0 ,iAreaID int not null default 0,
-> index(iUin,iAreaID),index(dtEventTime))ENGINE=InnoDB DEFAULT CHARSET=utf8
-> PARTITION BY LIST (dayofmonth(dtEventTime))
-> (PARTITION p_11 VALUES IN (11) ENGINE = InnoDB,
-> PARTITION p_12 VALUES IN (12) ENGINE = InnoDB,
-> PARTITION p_13 VALUES IN (13) ENGINE = InnoDB,
-> PARTITION p_14 VALUES IN (14) ENGINE = InnoDB,
-> PARTITION p_15 VALUES IN (15) ENGINE = InnoDB,
-> PARTITION p_16 VALUES IN (16) ENGINE = InnoDB,
-> PARTITION p_17 VALUES IN (17) ENGINE = InnoDB,
-> PARTITION p_18 VALUES IN (18) ENGINE = InnoDB,
-> PARTITION p_19 VALUES IN (19) ENGINE = InnoDB,
-> PARTITION p_20 VALUES IN (20) ENGINE = InnoDB,
-> PARTITION p_21 VALUES IN (21) ENGINE = InnoDB,
-> PARTITION p_22 VALUES IN (22) ENGINE = InnoDB,
-> PARTITION p_23 VALUES IN (23) ENGINE = InnoDB,
-> PARTITION p_24 VALUES IN (24) ENGINE = InnoDB,
-> PARTITION p_25 VALUES IN (25) ENGINE = InnoDB,
-> PARTITION p_26 VALUES IN (26) ENGINE = InnoDB,
-> PARTITION p_27 VALUES IN (27) ENGINE = InnoDB,
-> PARTITION p_28 VALUES IN (28) ENGINE = InnoDB,
-> PARTITION p_29 VALUES IN (29) ENGINE = InnoDB,
-> PARTITION p_30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p_31 VALUES IN (31) ENGINE = InnoDB,
-> PARTITION p_1 VALUES IN (1) ENGINE = InnoDB,
-> PARTITION p_2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION p_3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION p_4 VALUES IN (4) ENGINE = InnoDB,
-> PARTITION p_5 VALUES IN (5) ENGINE = InnoDB,
-> PARTITION p_6 VALUES IN (6) ENGINE = InnoDB,
-> PARTITION p_7 VALUES IN (7) ENGINE = InnoDB,
-> PARTITION p_8 VALUES IN (8) ENGINE = InnoDB,
-> PARTITION p_9 VALUES IN (9) ENGINE = InnoDB,
-> PARTITION p_10 VALUES IN (10) ENGINE = InnoDB);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function