1 根据id范围进行分区

CREATE TABLE `person1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10611038 DEFAULT CHARSET=utf8

PARTITION BY RANGE(id)(
    PARTITION p0 VAlUES LESS THAN(100),
    PARTITION p1 VALUES LESS THAN(200),
    PARTITION p2 VALUES LESS THAN(300),
    PARTITION p3 VALUES LESS THAN(400),
    PARTITION p4 values less than maxvalue

);

 

2 根据时间进行分区

 CREATE TABLE part_date
          (  c1 int default NULL,
      c2 varchar(30) default NULL,
      c3 date default NULL) engine=myisam
     partition by range (to_days(c3))
     (PARTITION p0 VALUES LESS THAN (to_days('2022-03-01')),
     PARTITION p1 VALUES LESS THAN (to_days('2022-04-01')) ,
     PARTITION p11 VALUES LESS THAN MAXVALUE );

注意：必须要用日期转换函数，否则失效

 

3 查看分区个数，以及分区的行数

select partition_name, PARTITION_DESCRIPTION, PARTITION_EXPRESSION, table_rows from information_schema.partitions where table_name = '表名'