If you want to partition on a table that has already been built, MySQL prompts for an error if you add the partition using alter:
Copy Code code as follows:
ERROR 1505 <HY000> Partition Management on a not partitioned the table is not possible
The correct approach is to create a new partitioned table with a consistent structure, then insert into the partition table select * from the original table;
Test create partition Table file
Copy Code code as follows:
CREATE TABLE tr (id INT, name VARCHAR (m), 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)
);
inserting test Data
Copy Code code as follows:
INSERT into TR VALUES
(1, ' desk organiser ', ' 2003-10-15′ '),
(2, ' CD player ', ' 1993-11-05′ '),
(3, ' TV set ', ' 1996-03-10′ '),
(4, ' bookcase ', ' 1982-01-10′ '),
(5, ' exercise bike ', ' 2004-05-09′ '),
(6, ' sofa ', ' 1987-06-05′ '),
(7, ' Popcorn maker ', ' 2001-11-22′ '),
(8, ' aquarium ', ' 1992-08-04′ '),
(9, ' study desk ', ' 1984-09-16′ '),
(' Lava lamp ', ' 1998-12-25′ ');
querying the data in P2
Copy Code code as follows:
SELECT * from TR where purchased between ' 1995-01-01′and ' 2004-12-31′;
If you delete P2, all data under it is deleted while the P2 partition is deleted
Copy Code code as follows:
ALTER TABLE TR drop partition p2;
Show CREATE TABLE tr;
CREATE TABLE ' tr ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar 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 (+) ENGINE = MyISAM) * *
Inserting Data again inserts the data from the original P2 into the P3
Copy Code code as follows:
INSERT into TR VALUES (one, ' Pencil holder ', ' 1995-07-12′);
ALTER TABLE tr DROP PARTITION P3;
SELECT * from TR WHERE purchased BETWEEN ' 1995-01-01′and ' 2004-12-31′;
to create a new test table
Copy Code code 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)
);
add a partition directly to the last face with ALTER TABLE TableName ADD partition
Copy Code code as follows:
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES less THAN (2000));
Copy Code code as follows:
ALTER TABLE members reorganize partition P0 into (
Partition M0 values less than (1960),
Partition M1 values less than (1970)
);
Show create TABLE members;
CREATE TABLE ' Members ' (
' id ' int (one) DEFAULT NULL,
' fname ' varchar DEFAULT NULL,
' lname ' varchar 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 (watts) ENGINE = MyISAM) * *
With REORGANIZE partition data is merged and split, the data is not lost.
(Detailed source reference: http://www.jb51.net/article/42544.htm)
If you add an error in this way before, you can only use a different way to merge the split partitions.
