MySql DATA partition operation-new partition operation, mysql Data Partition added

MySql DATA partition operation-new partition operation, mysql Data Partition added

If you want to partition a created table and use alter to add a partition, mysql will prompt an error:
Copy codeThe Code is as follows:
ERROR 1505 <HY000> Partition management on a not partitioned table is not possible

The correct method is to create a new table with partitions with the same structure, and then use insert into Partition Table select * from original table;

Create a partition table File

Copy codeThe Code is as follows:
Create table tr (id INT, name VARCHAR (50), purchased DATE)
Partition by range (YEAR (purchased ))
(
PARTITION p0 values less than (1990 ),
PARTITION p1 values less than (1995 ),
PARTITION p2 values less than (2000 ),
PARTITION p3 values less than (2005)
);

Insert Test Data

Copy codeThe Code is as follows:
Insert into tr VALUES
(1, 'desk organiser ', '2017-10-15 ′),
(2, 'CD player', '2017-1993 ′),
(3, 'TV set', '2017-03-10 ′),
(4, 'bookcase', '2017-01-10 ′),
(5, 'Exercise bike ', '2017-2004 ′),
(6, 'sofa ', '2017-06-05 ′),
(7, 'popcorn Manual', '2017-11-22 ′),
(8, 'aquarium ', '2017-08-04 ′),
(9, 'Study desk ', '2017-09-16 ′),
(10, 'lava lamp ', '2017-12-25 ′);

Query data in P2

Copy codeThe Code is as follows:
Select * from tr where purchased between '2017-01-01 'and '2017-12-31 ′;

If P2 is deleted, all data under the P2 partition is also deleted.

Copy codeThe Code is as follows:
Alter table tr drop partition p2;
Show create table tr;
Create table 'tr '(
'Id' int (11) default null,
'Name' varchar (50) default null,
'Purchased' date DEFAULT NULL
) ENGINE = MyISAM default charset = utf8
/*! 50100 partition by range (YEAR (purchased ))
(PARTITION p0 values less than (1990) ENGINE = MyISAM,
PARTITION p1 values less than (1995) ENGINE = MyISAM,
PARTITION p3 values less than (2005) ENGINE = MyISAM )*/
 
When data is inserted again, the original P2 data is inserted into P3.

Copy codeThe Code is as follows:
Insert into tr VALUES (11, 'pencil holder', '2017-07-12 ′);
Alter table tr drop partition p3;
SELECT * FROM tr WHERE purchased BETWEEN '2017-01-01 'AND '2017-12-31 ′;

Create a new test table

Copy codeThe Code is as follows:
Create table members (
Id INT,
Fname VARCHAR (25 ),
Lname VARCHAR (25 ),
Dob DATE
)
Partition by range (YEAR (dob ))(
PARTITION p0 values less than (1970 ),
PARTITION p1 values less than (1980 ),
PARTITION p2 values less than (1990)
);

Directly add partitions to the end using alter table tablename add partition.

Copy codeThe Code is as follows:
Alter table members add partition (PARTITION p3 values less than (2000 ));

Copy codeThe Code is as follows:
Alter table members reorganize partition p0 (
Partition m0 values less than (1960 ),
Partition m1 values less than (1970)
);
Show create table members;
Create table 'members '(
'Id' int (11) default null,
'Fname' varchar (25) default null,
'Lname' varchar (25) default null,
'Dob' date DEFAULT NULL
) ENGINE = MyISAM default charset = utf8
/*! 50100 partition by range (YEAR (dob ))
(PARTITION m0 values less than (1960) ENGINE = MyISAM,
PARTITION m1 values less than (1970) ENGINE = MyISAM,
PARTITION p1 values less than (1980) ENGINE = MyISAM,
PARTITION p2 values less than (1990) ENGINE = MyISAM,
PARTITION p3 values less than (2000) ENGINE = MyISAM )*/
 
Data is not lost when you use reorganize partition to merge and split data.
(For details, refer to: http://www.bkjia.com/article/42544.htm)
If this method is used before addition, an error is reported. You can only use another method to merge and split partitions.