CREATE TABLE `trips` (
`id` bigint(20) UNSIGNED NOT NULL COMMENT 'Unique trip Id',
`trip_category` int(11) UNSIGNED NOT NULL COMMENT 'Trip category',
`trip_month` int(11) UNSIGNED NOT NULL COMMENT 'Trip month',
`trip_date` date NOT NULL COMMENT 'The trip date'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'The trips'
PARTITION BY RANGE(trip_month) SUBPARTITION BY HASH(DAY(trip_date)) (
PARTITION p_month_Jan
VALUES
LESS THAN (0) (
SUBPARTITION p_month_Jan_day_0,
SUBPARTITION p_month_Jan_day_1,
SUBPARTITION p_month_Jan_day_2,
SUBPARTITION p_month_Jan_day_3,
SUBPARTITION p_month_Jan_day_4,
SUBPARTITION p_month_Jan_day_5,
SUBPARTITION p_month_Jan_day_6,
SUBPARTITION p_month_Jan_day_7,
SUBPARTITION p_month_Jan_day_8,
SUBPARTITION p_month_Jan_day_9,
SUBPARTITION p_month_Jan_day_10,
SUBPARTITION p_month_Jan_day_11,
SUBPARTITION p_month_Jan_day_12,
SUBPARTITION p_month_Jan_day_13,
SUBPARTITION p_month_Jan_day_14,
SUBPARTITION p_month_Jan_day_15,
SUBPARTITION p_month_Jan_day_16,
SUBPARTITION p_month_Jan_day_17,
SUBPARTITION p_month_Jan_day_18,
SUBPARTITION p_month_Jan_day_19,
SUBPARTITION p_month_Jan_day_20,
SUBPARTITION p_month_Jan_day_21,
SUBPARTITION p_month_Jan_day_22,
SUBPARTITION p_month_Jan_day_23,
SUBPARTITION p_month_Jan_day_24,
SUBPARTITION p_month_Jan_day_25,
SUBPARTITION p_month_Jan_day_26,
SUBPARTITION p_month_Jan_day_27,
SUBPARTITION p_month_Jan_day_28,
SUBPARTITION p_month_Jan_day_29,
SUBPARTITION p_month_Jan_day_30,
SUBPARTITION p_month_Jan_day_31
),
PARTITION p_month_Feb
VALUES
LESS THAN (1) (
SUBPARTITION p_month_Feb_day_0,
SUBPARTITION p_month_Feb_day_1,
SUBPARTITION p_month_Feb_day_2,
SUBPARTITION p_month_Feb_day_3,
SUBPARTITION p_month_Feb_day_4,
SUBPARTITION p_month_Feb_day_5,
SUBPARTITION p_month_Feb_day_6,
SUBPARTITION p_month_Feb_day_7,
SUBPARTITION p_month_Feb_day_8,
SUBPARTITION p_month_Feb_day_9,
SUBPARTITION p_month_Feb_day_10,
SUBPARTITION p_month_Feb_day_11,
SUBPARTITION p_month_Feb_day_12,
SUBPARTITION p_month_Feb_day_13,
SUBPARTITION p_month_Feb_day_14,
SUBPARTITION p_month_Feb_day_15,
SUBPARTITION p_month_Feb_day_16,
SUBPARTITION p_month_Feb_day_17,
SUBPARTITION p_month_Feb_day_18,
SUBPARTITION p_month_Feb_day_19,
SUBPARTITION p_month_Feb_day_20,
SUBPARTITION p_month_Feb_day_21,
SUBPARTITION p_month_Feb_day_22,
SUBPARTITION p_month_Feb_day_23,
SUBPARTITION p_month_Feb_day_24,
SUBPARTITION p_month_Feb_day_25,
SUBPARTITION p_month_Feb_day_26,
SUBPARTITION p_month_Feb_day_27,
SUBPARTITION p_month_Feb_day_28,
SUBPARTITION p_month_Feb_day_29,
SUBPARTITION p_month_Feb_day_30,
SUBPARTITION p_month_Feb_day_31
),
PARTITION p_month_Mar
VALUES
LESS THAN (2) (
SUBPARTITION p_month_Mar_day_0,
SUBPARTITION p_month_Mar_day_1,
SUBPARTITION p_month_Mar_day_2,
SUBPARTITION p_month_Mar_day_3,
SUBPARTITION p_month_Mar_day_4,
SUBPARTITION p_month_Mar_day_5,
SUBPARTITION p_month_Mar_day_6,
SUBPARTITION p_month_Mar_day_7,
SUBPARTITION p_month_Mar_day_8,
SUBPARTITION p_month_Mar_day_9,
SUBPARTITION p_month_Mar_day_10,
SUBPARTITION p_month_Mar_day_11,
SUBPARTITION p_month_Mar_day_12,
SUBPARTITION p_month_Mar_day_13,
SUBPARTITION p_month_Mar_day_14,
SUBPARTITION p_month_Mar_day_15,
SUBPARTITION p_month_Mar_day_16,
SUBPARTITION p_month_Mar_day_17,
SUBPARTITION p_month_Mar_day_18,
SUBPARTITION p_month_Mar_day_19,
SUBPARTITION p_month_Mar_day_20,
SUBPARTITION p_month_Mar_day_21,
SUBPARTITION p_month_Mar_day_22,
SUBPARTITION p_month_Mar_day_23,
SUBPARTITION p_month_Mar_day_24,
SUBPARTITION p_month_Mar_day_25,
SUBPARTITION p_month_Mar_day_26,
SUBPARTITION p_month_Mar_day_27,
SUBPARTITION p_month_Mar_day_28,
SUBPARTITION p_month_Mar_day_29,
SUBPARTITION p_month_Mar_day_30,
SUBPARTITION p_month_Mar_day_31
),
PARTITION p_month_Apr
VALUES
LESS THAN (3) (
SUBPARTITION p_month_Apr_day_0,
SUBPARTITION p_month_Apr_day_1,
SUBPARTITION p_month_Apr_day_2,
SUBPARTITION p_month_Apr_day_3,
SUBPARTITION p_month_Apr_day_4,
SUBPARTITION p_month_Apr_day_5,
SUBPARTITION p_month_Apr_day_6,
SUBPARTITION p_month_Apr_day_7,
SUBPARTITION p_month_Apr_day_8,
SUBPARTITION p_month_Apr_day_9,
SUBPARTITION p_month_Apr_day_10,
SUBPARTITION p_month_Apr_day_11,
SUBPARTITION p_month_Apr_day_12,
SUBPARTITION p_month_Apr_day_13,
SUBPARTITION p_month_Apr_day_14,
SUBPARTITION p_month_Apr_day_15,
SUBPARTITION p_month_Apr_day_16,
SUBPARTITION p_month_Apr_day_17,
SUBPARTITION p_month_Apr_day_18,
SUBPARTITION p_month_Apr_day_19,
SUBPARTITION p_month_Apr_day_20,
SUBPARTITION p_month_Apr_day_21,
SUBPARTITION p_month_Apr_day_22,
SUBPARTITION p_month_Apr_day_23,
SUBPARTITION p_month_Apr_day_24,
SUBPARTITION p_month_Apr_day_25,
SUBPARTITION p_month_Apr_day_26,
SUBPARTITION p_month_Apr_day_27,
SUBPARTITION p_month_Apr_day_28,
SUBPARTITION p_month_Apr_day_29,
SUBPARTITION p_month_Apr_day_30,
SUBPARTITION p_month_Apr_day_31
),
PARTITION p_month_Mai
VALUES
LESS THAN (4) (
SUBPARTITION p_month_Mai_day_0,
SUBPARTITION p_month_Mai_day_1,
SUBPARTITION p_month_Mai_day_2,
SUBPARTITION p_month_Mai_day_3,
SUBPARTITION p_month_Mai_day_4,
SUBPARTITION p_month_Mai_day_5,
SUBPARTITION p_month_Mai_day_6,
SUBPARTITION p_month_Mai_day_7,
SUBPARTITION p_month_Mai_day_8,
SUBPARTITION p_month_Mai_day_9,
SUBPARTITION p_month_Mai_day_10,
SUBPARTITION p_month_Mai_day_11,
SUBPARTITION p_month_Mai_day_12,
SUBPARTITION p_month_Mai_day_13,
SUBPARTITION p_month_Mai_day_14,
SUBPARTITION p_month_Mai_day_15,
SUBPARTITION p_month_Mai_day_16,
SUBPARTITION p_month_Mai_day_17,
SUBPARTITION p_month_Mai_day_18,
SUBPARTITION p_month_Mai_day_19,
SUBPARTITION p_month_Mai_day_20,
SUBPARTITION p_month_Mai_day_21,
SUBPARTITION p_month_Mai_day_22,
SUBPARTITION p_month_Mai_day_23,
SUBPARTITION p_month_Mai_day_24,
SUBPARTITION p_month_Mai_day_25,
SUBPARTITION p_month_Mai_day_26,
SUBPARTITION p_month_Mai_day_27,
SUBPARTITION p_month_Mai_day_28,
SUBPARTITION p_month_Mai_day_29,
SUBPARTITION p_month_Mai_day_30,
SUBPARTITION p_month_Mai_day_31
),
PARTITION p_month_Jun
VALUES
LESS THAN (5) (
SUBPARTITION p_month_Jun_day_0,
SUBPARTITION p_month_Jun_day_1,
SUBPARTITION p_month_Jun_day_2,
SUBPARTITION p_month_Jun_day_3,
SUBPARTITION p_month_Jun_day_4,
SUBPARTITION p_month_Jun_day_5,
SUBPARTITION p_month_Jun_day_6,
SUBPARTITION p_month_Jun_day_7,
SUBPARTITION p_month_Jun_day_8,
SUBPARTITION p_month_Jun_day_9,
SUBPARTITION p_month_Jun_day_10,
SUBPARTITION p_month_Jun_day_11,
SUBPARTITION p_month_Jun_day_12,
SUBPARTITION p_month_Jun_day_13,
SUBPARTITION p_month_Jun_day_14,
SUBPARTITION p_month_Jun_day_15,
SUBPARTITION p_month_Jun_day_16,
SUBPARTITION p_month_Jun_day_17,
SUBPARTITION p_month_Jun_day_18,
SUBPARTITION p_month_Jun_day_19,
SUBPARTITION p_month_Jun_day_20,
SUBPARTITION p_month_Jun_day_21,
SUBPARTITION p_month_Jun_day_22,
SUBPARTITION p_month_Jun_day_23,
SUBPARTITION p_month_Jun_day_24,
SUBPARTITION p_month_Jun_day_25,
SUBPARTITION p_month_Jun_day_26,
SUBPARTITION p_month_Jun_day_27,
SUBPARTITION p_month_Jun_day_28,
SUBPARTITION p_month_Jun_day_29,
SUBPARTITION p_month_Jun_day_30,
SUBPARTITION p_month_Jun_day_31
),
PARTITION p_month_Jul
VALUES
LESS THAN (6) (
SUBPARTITION p_month_Jul_day_0,
SUBPARTITION p_month_Jul_day_1,
SUBPARTITION p_month_Jul_day_2,
SUBPARTITION p_month_Jul_day_3,
SUBPARTITION p_month_Jul_day_4,
SUBPARTITION p_month_Jul_day_5,
SUBPARTITION p_month_Jul_day_6,
SUBPARTITION p_month_Jul_day_7,
SUBPARTITION p_month_Jul_day_8,
SUBPARTITION p_month_Jul_day_9,
SUBPARTITION p_month_Jul_day_10,
SUBPARTITION p_month_Jul_day_11,
SUBPARTITION p_month_Jul_day_12,
SUBPARTITION p_month_Jul_day_13,
SUBPARTITION p_month_Jul_day_14,
SUBPARTITION p_month_Jul_day_15,
SUBPARTITION p_month_Jul_day_16,
SUBPARTITION p_month_Jul_day_17,
SUBPARTITION p_month_Jul_day_18,
SUBPARTITION p_month_Jul_day_19,
SUBPARTITION p_month_Jul_day_20,
SUBPARTITION p_month_Jul_day_21,
SUBPARTITION p_month_Jul_day_22,
SUBPARTITION p_month_Jul_day_23,
SUBPARTITION p_month_Jul_day_24,
SUBPARTITION p_month_Jul_day_25,
SUBPARTITION p_month_Jul_day_26,
SUBPARTITION p_month_Jul_day_27,
SUBPARTITION p_month_Jul_day_28,
SUBPARTITION p_month_Jul_day_29,
SUBPARTITION p_month_Jul_day_30,
SUBPARTITION p_month_Jul_day_31
),
PARTITION p_month_Aug
VALUES
LESS THAN (7) (
SUBPARTITION p_month_Aug_day_0,
SUBPARTITION p_month_Aug_day_1,
SUBPARTITION p_month_Aug_day_2,
SUBPARTITION p_month_Aug_day_3,
SUBPARTITION p_month_Aug_day_4,
SUBPARTITION p_month_Aug_day_5,
SUBPARTITION p_month_Aug_day_6,
SUBPARTITION p_month_Aug_day_7,
SUBPARTITION p_month_Aug_day_8,
SUBPARTITION p_month_Aug_day_9,
SUBPARTITION p_month_Aug_day_10,
SUBPARTITION p_month_Aug_day_11,
SUBPARTITION p_month_Aug_day_12,
SUBPARTITION p_month_Aug_day_13,
SUBPARTITION p_month_Aug_day_14,
SUBPARTITION p_month_Aug_day_15,
SUBPARTITION p_month_Aug_day_16,
SUBPARTITION p_month_Aug_day_17,
SUBPARTITION p_month_Aug_day_18,
SUBPARTITION p_month_Aug_day_19,
SUBPARTITION p_month_Aug_day_20,
SUBPARTITION p_month_Aug_day_21,
SUBPARTITION p_month_Aug_day_22,
SUBPARTITION p_month_Aug_day_23,
SUBPARTITION p_month_Aug_day_24,
SUBPARTITION p_month_Aug_day_25,
SUBPARTITION p_month_Aug_day_26,
SUBPARTITION p_month_Aug_day_27,
SUBPARTITION p_month_Aug_day_28,
SUBPARTITION p_month_Aug_day_29,
SUBPARTITION p_month_Aug_day_30,
SUBPARTITION p_month_Aug_day_31
),
PARTITION p_month_Sep
VALUES
LESS THAN (8) (
SUBPARTITION p_month_Sep_day_0,
SUBPARTITION p_month_Sep_day_1,
SUBPARTITION p_month_Sep_day_2,
SUBPARTITION p_month_Sep_day_3,
SUBPARTITION p_month_Sep_day_4,
SUBPARTITION p_month_Sep_day_5,
SUBPARTITION p_month_Sep_day_6,
SUBPARTITION p_month_Sep_day_7,
SUBPARTITION p_month_Sep_day_8,
SUBPARTITION p_month_Sep_day_9,
SUBPARTITION p_month_Sep_day_10,
SUBPARTITION p_month_Sep_day_11,
SUBPARTITION p_month_Sep_day_12,
SUBPARTITION p_month_Sep_day_13,
SUBPARTITION p_month_Sep_day_14,
SUBPARTITION p_month_Sep_day_15,
SUBPARTITION p_month_Sep_day_16,
SUBPARTITION p_month_Sep_day_17,
SUBPARTITION p_month_Sep_day_18,
SUBPARTITION p_month_Sep_day_19,
SUBPARTITION p_month_Sep_day_20,
SUBPARTITION p_month_Sep_day_21,
SUBPARTITION p_month_Sep_day_22,
SUBPARTITION p_month_Sep_day_23,
SUBPARTITION p_month_Sep_day_24,
SUBPARTITION p_month_Sep_day_25,
SUBPARTITION p_month_Sep_day_26,
SUBPARTITION p_month_Sep_day_27,
SUBPARTITION p_month_Sep_day_28,
SUBPARTITION p_month_Sep_day_29,
SUBPARTITION p_month_Sep_day_30,
SUBPARTITION p_month_Sep_day_31
),
PARTITION p_month_Oct
VALUES
LESS THAN (9) (
SUBPARTITION p_month_Oct_day_0,
SUBPARTITION p_month_Oct_day_1,
SUBPARTITION p_month_Oct_day_2,
SUBPARTITION p_month_Oct_day_3,
SUBPARTITION p_month_Oct_day_4,
SUBPARTITION p_month_Oct_day_5,
SUBPARTITION p_month_Oct_day_6,
SUBPARTITION p_month_Oct_day_7,
SUBPARTITION p_month_Oct_day_8,
SUBPARTITION p_month_Oct_day_9,
SUBPARTITION p_month_Oct_day_10,
SUBPARTITION p_month_Oct_day_11,
SUBPARTITION p_month_Oct_day_12,
SUBPARTITION p_month_Oct_day_13,
SUBPARTITION p_month_Oct_day_14,
SUBPARTITION p_month_Oct_day_15,
SUBPARTITION p_month_Oct_day_16,
SUBPARTITION p_month_Oct_day_17,
SUBPARTITION p_month_Oct_day_18,
SUBPARTITION p_month_Oct_day_19,
SUBPARTITION p_month_Oct_day_20,
SUBPARTITION p_month_Oct_day_21,
SUBPARTITION p_month_Oct_day_22,
SUBPARTITION p_month_Oct_day_23,
SUBPARTITION p_month_Oct_day_24,
SUBPARTITION p_month_Oct_day_25,
SUBPARTITION p_month_Oct_day_26,
SUBPARTITION p_month_Oct_day_27,
SUBPARTITION p_month_Oct_day_28,
SUBPARTITION p_month_Oct_day_29,
SUBPARTITION p_month_Oct_day_30,
SUBPARTITION p_month_Oct_day_31
),
PARTITION p_month_Nov
VALUES
LESS THAN (10) (
SUBPARTITION p_month_Nov_day_0,
SUBPARTITION p_month_Nov_day_1,
SUBPARTITION p_month_Nov_day_2,
SUBPARTITION p_month_Nov_day_3,
SUBPARTITION p_month_Nov_day_4,
SUBPARTITION p_month_Nov_day_5,
SUBPARTITION p_month_Nov_day_6,
SUBPARTITION p_month_Nov_day_7,
SUBPARTITION p_month_Nov_day_8,
SUBPARTITION p_month_Nov_day_9,
SUBPARTITION p_month_Nov_day_10,
SUBPARTITION p_month_Nov_day_11,
SUBPARTITION p_month_Nov_day_12,
SUBPARTITION p_month_Nov_day_13,
SUBPARTITION p_month_Nov_day_14,
SUBPARTITION p_month_Nov_day_15,
SUBPARTITION p_month_Nov_day_16,
SUBPARTITION p_month_Nov_day_17,
SUBPARTITION p_month_Nov_day_18,
SUBPARTITION p_month_Nov_day_19,
SUBPARTITION p_month_Nov_day_20,
SUBPARTITION p_month_Nov_day_21,
SUBPARTITION p_month_Nov_day_22,
SUBPARTITION p_month_Nov_day_23,
SUBPARTITION p_month_Nov_day_24,
SUBPARTITION p_month_Nov_day_25,
SUBPARTITION p_month_Nov_day_26,
SUBPARTITION p_month_Nov_day_27,
SUBPARTITION p_month_Nov_day_28,
SUBPARTITION p_month_Nov_day_29,
SUBPARTITION p_month_Nov_day_30,
SUBPARTITION p_month_Nov_day_31
),
PARTITION p_month_Dec
VALUES
LESS THAN (11) (
SUBPARTITION p_month_Dec_day_0,
SUBPARTITION p_month_Dec_day_1,
SUBPARTITION p_month_Dec_day_2,
SUBPARTITION p_month_Dec_day_3,
SUBPARTITION p_month_Dec_day_4,
SUBPARTITION p_month_Dec_day_5,
SUBPARTITION p_month_Dec_day_6,
SUBPARTITION p_month_Dec_day_7,
SUBPARTITION p_month_Dec_day_8,
SUBPARTITION p_month_Dec_day_9,
SUBPARTITION p_month_Dec_day_10,
SUBPARTITION p_month_Dec_day_11,
SUBPARTITION p_month_Dec_day_12,
SUBPARTITION p_month_Dec_day_13,
SUBPARTITION p_month_Dec_day_14,
SUBPARTITION p_month_Dec_day_15,
SUBPARTITION p_month_Dec_day_16,
SUBPARTITION p_month_Dec_day_17,
SUBPARTITION p_month_Dec_day_18,
SUBPARTITION p_month_Dec_day_19,
SUBPARTITION p_month_Dec_day_20,
SUBPARTITION p_month_Dec_day_21,
SUBPARTITION p_month_Dec_day_22,
SUBPARTITION p_month_Dec_day_23,
SUBPARTITION p_month_Dec_day_24,
SUBPARTITION p_month_Dec_day_25,
SUBPARTITION p_month_Dec_day_26,
SUBPARTITION p_month_Dec_day_27,
SUBPARTITION p_month_Dec_day_28,
SUBPARTITION p_month_Dec_day_29,
SUBPARTITION p_month_Dec_day_30,
SUBPARTITION p_month_Dec_day_31
)
)