Official documents: http://www.w3resource.com/mysql/mysql-partition.php for the type of partition, and when you create a new table, it's good to do it as an example. This article is mainly to deal with historical data, that is, the table has a huge amount of data, such as two years, a day of data is g+, like this, we must early to deal with these tables without advance partitioning. According to the range partition, it is mainly partitioned by month and day. E.g.1: (1) CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date date not NULL, Cust_code VARCHAR (.) not NULL, amount DEC iMAL (8,2) not NULL); (2) INSERT into Sale_mast VALUES (1, ' 2013-01-02 ', ' C001 ', 125.56), (2, ' 2013-01-25 ', ' C003 ', 456.50), (3, ' 2013-02-15 ', ' C012 ', 365.00), (4, ' 2013-03-26 ', ' C345 ', 785.00), (5, ' 2013-04-19 ', ' C234 ', 656.00), (6, ' 2013-05-31 '), ' C743 ', 854.00), (7, ' 2013-06-11 ', ' C234 ', 542.00), (8, ' 2013-07-24 ', ' C003 ', 300.00), (8, ' 2013-08-02 ', ' C456 ', 475.20); Note: This table sale_mast is a table that already has data, called historical data; View the information of the pre-partition table:mysql> select Partition_name,table_rows from Information_ schema.partitions where table_name= ' sale_mast '; +----------------+------------+
| Partition_name | Table_rows |
+----------------+------------+
| NULL | 9 |
+----------------+------------+
1 row in Set (0.00 sec) Note: The values of the range partition must be of type int. The first partition: Then the sale_mast is partitioned on a monthly basis: ALTER TABLE sale_mast PARTITION by range (yea R (bill_date) *100+month (bill_date)) (PARTITION p0 values less THAN ((201304)), PARTITION P1 values less THAN ((201307)), P Artition P2 values less THAN ((201310)), PARTITION P3 values less THAN ((201401))); View post-partition information:mysql> Select Partition_nam E,table_rows from information_schema.partitions where table_name= ' sale_mast '; +----------------+------------+
| Partition_name | Table_rows |
+----------------+------------+
| P0 | 4 |
| P1 | 3 |
| P2 | 2 |
| P3 | 0 |
+----------------+------------+
4 rows in Set (0.01 sec) Second partition: Then partition the Sale_mast by day: Delete the table, re-create the table and inset into, and then partition by day: ALTER table Sale_mast PARTITION by RANGE (t O_days (Bill_date)) (PARTITION p0 values less THAN (to_days (' 2013-04-01 ')), PARTITION P1 values less THAN (to_days (' 2013-0 7-01 '), PARTITION P2 values less THAN (to_days (' 2013-10-01 ')), PARTITION P3 values less THAN (to_days (' 2014-01-01 ')); Mys Ql> Select Partition_name,table_rows from information_schema.partitions where table_name= ' sale_mast '; +----------- -----+------------+
| Partition_name | Table_rows |
+----------------+------------+
| P0 | 4 |
| P1 | 3 |
| P2 | 2 |
| P3 | 0 |
+----------------+------------+
4 rows in Set (0.00 sec). You can also create sub-partitions, which are updated next time.
MySQL partition (MySQL range partition, partitioning of historical data)
