-- 1.Delete table
drop table t_test;

-- ===================================================================================
-- 2.Create a table and partition it,The field of the created partition must be a primary key,Or there is no primary key in the table when creating the partition
-- 2.1 One way:Tables created with partitions
create table t_test (
        id int,
        dates timestamp
)partition by range (unix_timestamp(dates)) (
        -- Less than 2018-01-01 Of
        partition p1 values less than (unix_timestamp('2018-01-01')),
        partition p2 values less than (unix_timestamp('2018-02-01')),
        partition p3 values less than (unix_timestamp('2018-03-01')),
        -- Greater than 2018-03-01 Of
        partition p4 values less than maxvalue
);

-- ===================================================================================
-- 2.2 Mode two:Create tables and partitions separately
-- 2.2.1 Building tables
create table `t_test`  (
  `id` int(11) not null,
  `dates` datetime(0) not null on update current_timestamp(0),
  primary key (`id`, `dates`)
);

-- 3. Modify partition information
alter table t_test partition by range (to_days(dates)) (
    -- Less than 2020-01-01 Of
    partition p1 values less than (to_days('2020-01-01')),
    partition p2 values less than (to_days('2020-02-01')),
    partition p3 values less than (to_days('2020-03-01')),
    partition p4 values less than (to_days('2020-04-01')),
    -- Greater than 2020-04-01 Of
    partition p5 values less than maxvalue
);

-- ===================================================================================
-- 4. Delete and add new partition(Note: if the previous last partition was partition pnow values less than maxvalue; The partition should be deleted first,Then execute the new partition statement,Then add it back to the partition)
-- 4.1 Delete a partition(Be careful:When deleting a partition,All data in this partition will also be deleted;)
alter table t_test drop partition p5;
-- 4.2 Add a new partition
alter table t_test add partition (partition p6 values less than (to_days('2020-05-01')));
-- 4.3 Add a new partition(Those that do not meet the other partition conditions are stored in this partition)
alter table t_test add partition (partition p7 values less than maxvalue);

-- ===================================================================================
-- 5.Query how many partitions this table has
-- 5.1 Query the corresponding quantity of each partition
select
    partition_name part, partition_expression expr, partition_description descr, 
    from_days(partition_description) expirydate, table_rows 
from
    information_schema.`partitions`
where
    table_name='t_test'; 

-- 6.Create test data
-- Less than 2020-01-01 2 strip
insert into `t_test` values ('1', '2018-01-02 15:00:00');
insert into `t_test` values ('2', '2019-12-02 15:00:00');
-- 2020-01-01 Up to 2020-02-01 1 strip
insert into `t_test` values ('3', '2020-01-02 16:00:00');
-- 2020-02-01 Up to 2020-03-01 2 strip
insert into `t_test` values ('4', '2020-02-03 15:00:00');
insert into `t_test` values ('5', '2020-02-03 15:00:00');
-- 2020-03-01 Up to 2020-04-01 1 strip
insert into `t_test` values ('6', '2020-03-03 15:00:00');
-- 2020-04-01 Up to 2020-05-01 1 strip
insert into `t_test` values ('7', '2020-04-03 15:00:00');
-- Greater than 2020-05-01 4 strip
insert into `t_test` values ('8', '2020-05-03 15:00:00');
insert into `t_test` values ('8', '2020-06-03 15:00:00');
insert into `t_test` values ('8', '2020-06-06 15:00:00');
insert into `t_test` values ('8', '2021-01-01 15:00:00');

-- 6.Query data
select * from t_test;