· 8 years ago · Feb 19, 2018, 02:50 PM
1DROP TABLE IF EXISTS `simple_table`;
2CREATE TABLE `simple_table` (
3 `date` date NOT NULL,
4 `item_id` bigint(8) NOT NULL DEFAULT '0',
5 PRIMARY KEY (`date`,`item_id`)
6) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
7
8DROP TABLE IF EXISTS `partitioned_table`;
9CREATE TABLE `partitioned_table` (
10 `date` date NOT NULL,
11 `item_id` bigint(8) NOT NULL DEFAULT '0',
12 PRIMARY KEY (`date`,`item_id`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY RANGE ( TO_DAYS(`date`))
14(PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = InnoDB,
15 PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = InnoDB);
16
17INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
18INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);
19INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
20INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);
21
22*************************** 1. row ***************************
23 Name: partitioned_table
24 Engine: InnoDB
25 Version: 10
26 Row_format: Compact
27 Rows: 6
28Avg_row_length: 5461
29 Data_length: 32768
30Max_data_length: 0
31Index_length: 0
32 Data_free: 0
33Auto_increment: NULL
34 Create_time: 2018-02-19 14:36:29
35 Update_time: NULL
36 Check_time: NULL
37 Collation: utf8_unicode_ci
38 Checksum: NULL
39Create_options: partitioned
40 Comment:
41*************************** 2. row ***************************
42 Name: simple_table
43 Engine: InnoDB
44 Version: 10
45 Row_format: Compact
46 Rows: 6
47Avg_row_length: 2730
48 Data_length: 16384
49Max_data_length: 0
50Index_length: 0
51 Data_free: 0
52Auto_increment: NULL
53 Create_time: 2018-02-19 14:36:29
54 Update_time: NULL
55 Check_time: NULL
56 Collation: utf8_unicode_ci
57 Checksum: NULL
58Create_options:
59 Comment: