New partition operation for MySql data partition operation

If you want to partition on an already built table, mysql will give you an error if you add partitions using alter:

ERROR 1505 <HY000> Partition management on a not partitioned table is not possible

The correct method is to create a new table with partition, structure 1, and then use insert into partition table select * from original table;

Test creates partition table files

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

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 ' ),

(10, 'lava lamp', '1998-12-25 ' );

Query the data in P2

select * from tr where purchased between '1995-01-01 '  and '2004-12-31 ' ;

If you delete P2, deleting the P2 partition also deletes all the data under it

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 the data is inserted again, the data from the original P2 is inserted into P3

INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12 ' );

ALTER TABLE tr DROP PARTITION p3;

SELECT * FROM tr WHERE purchased  BETWEEN '1995-01-01 '  AND '2004-12-31 ' ;

Create a new test table

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)

);

Use alter table tablename add partition to add the partition at the end

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

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(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) */

 

REORGANIZE PARTITION is used to merge and split the data, and the data is not lost.
(detailed reference source: https: / / www ofstack. com article / 42544 htm)
If adding in this way would cause an error, you can only do it in another way, merging and splitting partitions.