MySQL partition table (RPM)

View partition status

1. SELECT * from information_schema. Partitions WHERE table_name=' table_name ';

Partition_name: Name of the partition

Partition_method: Type of partition

Table_rows: Number of partitioned data bars

Range Partition:

2. Create table T (
   
4. ID int) engine=innodb
   
6. Partition by range (ID) (
   
8. Partition P0 values less than ,--id p0 partition
   
10. Partition P1 values less than ()); --id greater than or equal to 10 less than 20 join P1 partition

Add partition

1. Alter table R add partition (partition P2 values less than MaxValue); -- all add P2 partitions greater than or equal to 20

2. CREATE TABLE ' W ' (
   
4. ' money ' INT(Ten) UNSIGNED not NULL,
   
6. ' Date ' DATETIME DEFAULT NULL
   
8. ) ENGINE=innodb DEFAULT CHARSET=utf8
   
10. PARTITION by RANGE (DATE)--The defined rule can also be a function
    
12. (PARTITION p2010 VALUES less THAN ),
    
14. PARTITION p2001 VALUES less THAN (+),
    
16. PARTITION p2012 VALUES less THAN (+));

Delete Partition

1. Alter table w drop partition p2010;

--Deleting a partition, the partition's data is also deleted

----------------------------------------------------------------------

List partition

2. CREATE TABLE M (
   
4. A INT,
   
6. b INT) ENGINE=innnodb
   
8. PARTITION by LIST (b)(
   
10. PARTITION p0 VALUES in (1, 2, 3, 4, 5),
    
12. PARTITION p1 VALUES in (6, 7, 8, 9, ten));
    
16. Insert INTO m values (1, 6), (2, 7), (3, one),(4, 9)

--The inserted value (3,11) does not match, and if it is the InnoDB engine, subsequent (4,9) qualifying conditions are not inserted into the table,

If the MyISAM engine is in the back (4,9), it will be inserted into the table

----------------------------------------------------------------------

< Strong style= "font-family: Song body; font-size:10.5pt; line-height:13.5pt; " >hash Partition

1. create table m_hash (
   
2. a int
3. b datetime) Engine=innnodb
   
4. PARTITION by HASH (year) )-- "Partition by hash (expr )
5. partitions 4

For example: Add partition algorithm mod (expr, number of partitions 4) =0 join P0

1. PARTITION by LINEAR HASH (year(b))

--unlike hash only algorithm, return is the same value

----------------------------------------------------------------------

Columns Partition

mysql5.5 started support as an evolution of the range partition and list partition, supporting int,smallint,tinyint,bigint. date,datetime. char,varchar,binary,varbinary Type

2. CREATE TABLE T_columns_range(
   
4. A INT,
   
6. b DATETIME) ENGINE=innodb
   
8. PARTITION by RANGE COLUMNS (b)-can also PARTITION by less COLUMNS (b)
   
10. (PARTITION p0 VALUES less THAN(' 2009-01-01 '),
    
12. PARTITION p1 VALUES less THAN(' 2010-01-01 '));