range类型分区说明: 行数据基于一个给定的连续区间的列值放入分区 重要提示: 分区键必须包含在表的所有主键、唯一键中 可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换，然后分区。

举例一：按天创建range类型时间分区表

普通表建表sql如下:

CREATE TABLE `t_test_task_result` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `batch_id` bigint NOT NULL DEFAULT '0' COMMENT '批次id',
  `task_id` bigint NOT NULL DEFAULT '0' COMMENT '任务id',
  `position_detail_task_id` bigint NOT NULL DEFAULT '0' COMMENT '详情任务id',
  `url` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'url',
  `file_path` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '文件路径',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 1-成功 2-反爬 3-异常 4-命中缓存',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_id_create_time` (`position_detail_task_id`,`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='爬取增量任务结果表'

A、全新的空表创建range类型时间分区表

CREATE TABLE `t_test_task_result` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `batch_id` bigint NOT NULL DEFAULT '0' COMMENT '批次id',
  `task_id` bigint NOT NULL DEFAULT '0' COMMENT '任务id',
  `position_detail_task_id` bigint NOT NULL DEFAULT '0' COMMENT '详情任务id',
  `url` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'url',
  `file_path` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '文件路径',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 1-成功 2-反爬 3-异常 4-命中缓存',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_id_create_time` (`position_detail_task_id`,`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='爬取增量任务结果表'
PARTITION BY RANGE(TO_DAYS(`create_time`))
(
    PARTITION p20220218 VALUES LESS THAN (TO_DAYS('2022-02-18')),
    PARTITION p20220219 VALUES LESS THAN (TO_DAYS('2022-02-19')),
    PARTITION p20220220 VALUES LESS THAN (TO_DAYS('2022-02-20')),
    PARTITION p20220221 VALUES LESS THAN (TO_DAYS('2022-02-21')),
    PARTITION p20220222 VALUES LESS THAN (TO_DAYS('2022-02-22')),
    PARTITION p20220223 VALUES LESS THAN (TO_DAYS('2022-02-23')),
    PARTITION p20220224 VALUES LESS THAN (TO_DAYS('2022-02-24')),
    PARTITION p20220225 VALUES LESS THAN (TO_DAYS('2022-02-25')),
    PARTITION p20220226 VALUES LESS THAN (TO_DAYS('2022-02-26')),
    PARTITION p20220227 VALUES LESS THAN (TO_DAYS('2022-02-27')),
    PARTITION p20220228 VALUES LESS THAN (TO_DAYS('2022-02-28')),
    PARTITION p20220301 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    PARTITION p20220302 VALUES LESS THAN (TO_DAYS('2022-03-02')),
    PARTITION p20220303 VALUES LESS THAN (TO_DAYS('2022-03-03'))
	);

对应的建表表结构如下:

root@tidb04 22:01:  [testdb]> show create table t_test_task_result\G
*************************** 1. row ***************************
       Table: t_test_task_result
Create Table: CREATE TABLE `t_test_task_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `batch_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '批次id',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '任务id',
  `position_detail_task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '详情任务id',
  `url` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'url',
  `file_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '文件路径',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 1-成功 2-反爬 3-异常 4-命中缓存',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_id_create_time` (`position_detail_task_id`,`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='爬取增量任务结果表'
/*!50100 PARTITION BY RANGE (to_days(`create_time`))
(PARTITION p20220218 VALUES LESS THAN (738569) ENGINE = InnoDB,
 PARTITION p20220219 VALUES LESS THAN (738570) ENGINE = InnoDB,
 PARTITION p20220220 VALUES LESS THAN (738571) ENGINE = InnoDB,
 PARTITION p20220221 VALUES LESS THAN (738572) ENGINE = InnoDB,
 PARTITION p20220222 VALUES LESS THAN (738573) ENGINE = InnoDB,
 PARTITION p20220223 VALUES LESS THAN (738574) ENGINE = InnoDB,
 PARTITION p20220224 VALUES LESS THAN (738575) ENGINE = InnoDB,
 PARTITION p20220225 VALUES LESS THAN (738576) ENGINE = InnoDB,
 PARTITION p20220226 VALUES LESS THAN (738577) ENGINE = InnoDB,
 PARTITION p20220227 VALUES LESS THAN (738578) ENGINE = InnoDB,
 PARTITION p20220228 VALUES LESS THAN (738579) ENGINE = InnoDB,
 PARTITION p20220301 VALUES LESS THAN (738580) ENGINE = InnoDB,
 PARTITION p20220302 VALUES LESS THAN (738581) ENGINE = InnoDB,
 PARTITION p20220303 VALUES LESS THAN (738582) ENGINE = InnoDB */

B、把已经存在数据的普通表在线转换成range类型时间分区表：

按照天创建range类型时间分区表要修改普通表主键id 为联合主键(id,create_time)

alter table t_test_task_result  DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`,`create_time`); #要锁表

把已经存在数据的普通表转换成 按照天划分的时间分区中,这样的话 属于那边的数据就会自动归到对应的各自的分区中区。具体sql如下:

alter table  t_test_task_result
PARTITION BY RANGE(TO_DAYS(`create_time`))
(
    PARTITION p20220218 VALUES LESS THAN (TO_DAYS('2022-02-18')),
    PARTITION p20220219 VALUES LESS THAN (TO_DAYS('2022-02-19')),
    PARTITION p20220220 VALUES LESS THAN (TO_DAYS('2022-02-20')),
    PARTITION p20220221 VALUES LESS THAN (TO_DAYS('2022-02-21')),
	PARTITION p20220222 VALUES LESS THAN (TO_DAYS('2022-02-22')),
    PARTITION p20220223 VALUES LESS THAN (TO_DAYS('2022-02-23')),
    PARTITION p20220224 VALUES LESS THAN (TO_DAYS('2022-02-24')),
    PARTITION p20220225 VALUES LESS THAN (TO_DAYS('2022-02-25')),
    PARTITION p20220226 VALUES LESS THAN (TO_DAYS('2022-02-26')),
    PARTITION p20220227 VALUES LESS THAN (TO_DAYS('2022-02-27')),
    PARTITION p20220228 VALUES LESS THAN (TO_DAYS('2022-02-28')),
    PARTITION p20220301 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    PARTITION p20220302 VALUES LESS THAN (TO_DAYS('2022-03-02')),
    PARTITION p20220303 VALUES LESS THAN (TO_DAYS('2022-03-03')),
	);

**执行完成之后的建表sql如下:

root@tidb04 21:56:  [testdb]> show create table t_test_task_result\G
*************************** 1. row ***************************
       Table: t_test_task_result
Create Table: CREATE TABLE `t_test_task_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `batch_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '批次id',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '任务id',
  `position_detail_task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '详情任务id',
  `url` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'url',
  `file_path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '文件路径',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 1-成功 2-反爬 3-异常 4-命中缓存',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_id_create_time` (`position_detail_task_id`,`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11170535 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='爬取增量任务结果表'
/*!50100 PARTITION BY RANGE (to_days(`create_time`))
(PARTITION p20220218 VALUES LESS THAN (738569) ENGINE = InnoDB,
 PARTITION p20220219 VALUES LESS THAN (738570) ENGINE = InnoDB,
 PARTITION p20220220 VALUES LESS THAN (738571) ENGINE = InnoDB,
 PARTITION p20220221 VALUES LESS THAN (738572) ENGINE = InnoDB,
 PARTITION p20220222 VALUES LESS THAN (738573) ENGINE = InnoDB,
 PARTITION p20220223 VALUES LESS THAN (738574) ENGINE = InnoDB,
 PARTITION p20220224 VALUES LESS THAN (738575) ENGINE = InnoDB,
 PARTITION p20220225 VALUES LESS THAN (738576) ENGINE = InnoDB,
 PARTITION p20220226 VALUES LESS THAN (738577) ENGINE = InnoDB,
 PARTITION p20220227 VALUES LESS THAN (738578) ENGINE = InnoDB,
 PARTITION p20220228 VALUES LESS THAN (738579) ENGINE = InnoDB,
 PARTITION p20220301 VALUES LESS THAN (738580) ENGINE = InnoDB,
 PARTITION p20220302 VALUES LESS THAN (738581) ENGINE = InnoDB,
 PARTITION p20220303 VALUES LESS THAN (738582) ENGINE = InnoDB) */

查看分区对应的记录数: ##p20220218分区存放的是2月17号全天的数据,但是原表2月17号无数据，所以对应的p20220218分区记录是0

root@tidb04 22:18:  [testdb]> desc  select * from t_test_task_result where create_time >='2022-02-17 00:00:00' and   create_time <='2022-02-17 23:59:59' ;
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table                           | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test_task_result | p20220218  | range | idx_create_time | idx_create_time | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

root@tidb04 22:18:  [testdb]> select * from t_test_task_result where create_time >='2022-02-17 00:00:00' and   create_time <='2022-02-17 23:59:59' ;
Empty set (0.00 sec)

root@tidb04 22:19:  [testdb]>

##p20220219 分区存的2月18号全天的数据

root@tidb04 22:16:  [testdb]> desc  select * from t_test_task_result where create_time >='2022-02-18 00:00:00' and   create_time <='2022-02-18 23:59:59' ;
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table                           | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_test_task_result | p20220219  | ALL  | idx_create_time | NULL | NULL    | NULL | 5859 |   100.00 | Using where |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

##p20220301 分区存的2月28号全天的数据

root@tidb04 22:14:  [testdb]> desc  select * from t_test_task_result where create_time >='2022-02-28 00:00:00' and   create_time <='2022-02-28 23:59:59' ;
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                           | partitions | type | possible_keys   | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_test_task_result | p20220301  | ALL  | idx_create_time | NULL | NULL    | NULL | 2850688 |    50.00 | Using where |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

##p20220302分区存放的是3月1号全天的数据

root@tidb04 22:13:  [testdb]> desc  select * from t_test_task_result where create_time >='2022-03-01 00:00:00' and   create_time <='2022-03-01 23:59:59' ;
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table                           | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_test_task_result | p20220302  | ALL  | idx_create_time | NULL | NULL    | NULL |  584 |   100.00 | Using where |
+----+-------------+---------------------------------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

##p20220303 分区记录是空的,存放的是3月2号的数据

root@tidb04 22:14:  [testdb]> desc  select * from t_test_task_result where create_time >='2022-03-02 00:00:00' and   create_time <='2022-03-02 23:59:59' ;
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table                           | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_test_task_result | p20220303  | range | idx_create_time | idx_create_time | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------------------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

root@tidb04 22:14:  [testdb]>  select * from t_test_task_result where create_time >='2022-03-02 00:00:00' and   create_time <='2022-03-02 23:59:59' ;
Empty set (0.00 sec)

举例二、按月创建分区表

###创建复合主键:

alter table t_test_position_task DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`,`create_time`);
dba_user@tidb04 23:59:  [db]> alter table t_test_position_task DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`,`create_time`);

Query OK, 0 rows affected (6.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

###t_test_position_task 按月进行分区

alter table  t_test_position_task
PARTITION BY RANGE(TO_DAYS(`create_time`))
(
    PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
    PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    PARTITION p202203 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
    PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
    PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01'))
	);
	
	
	dba_user@tidb04 00:17:  [db]> alter table  t_test_position_task
    -> PARTITION BY RANGE(TO_DAYS(`create_time`))
    -> (
    ->     PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
    ->     PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    ->     PARTITION p202203 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    ->     PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
    ->     PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
    ->     PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01'))
    -> );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
报错了

###查看源表结构：

	dba_user@tidb04 00:49:  [db]>  show create table t_test_position_task\G
*************************** 1. row ***************************
       Table: t_test_position_task
Create Table: CREATE TABLE `t_test_position_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务id',
  `batch_id` bigint NOT NULL DEFAULT '0' COMMENT '批次id',
  `channel` tinyint NOT NULL DEFAULT '0' COMMENT '渠道:1-BOSS,2-拉钩,3-51Job,4-猎聘,5-智联',
  `city` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '城市',
  `condition_id` bigint NOT NULL COMMENT '组合id',
  `url` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'URL',
  `operate_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '执行时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_condition_id_operate_time` (`condition_id`,`operate_time`) USING BTREE COMMENT '索引',
  KEY `idx_operate_time` (`operate_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1037560 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='增量任务表'
1 row in set (0.00 sec)

###表结构 timestamp 导致的分区表报错，解决办法： 修改字段类型datetime create_time datetime update_time datetime

dba_user@tidb04 22:15:  [db]> show create table t_test_position_task\G
*************************** 1. row ***************************
       Table: t_test_position_task
Create Table: CREATE TABLE `t_test_position_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务id',
  `batch_id` bigint NOT NULL DEFAULT '0' COMMENT '批次id',
  `channel` tinyint NOT NULL DEFAULT '0' COMMENT '渠道:1-BOSS,2-拉钩,3-51Job,4-猎聘,5-智联',
  `city` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '城市',
  `source` tinyint NOT NULL DEFAULT '1' COMMENT '来源,1增量搜索职位列表，2-全量职位列表，3-热招职位列表',
  `condition_id` bigint NOT NULL COMMENT '组合id',
  `url` varchar(5000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'URL',
  `operate_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '执行时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_condition_id_operate_time` (`condition_id`,`operate_time`) USING BTREE COMMENT '索引',
  KEY `idx_operate_time` (`operate_time`) USING BTREE COMMENT '索引',
  KEY `idx_batch_id` (`batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1141418 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='职位列表任务表'
1 row in set (0.00 sec)

alter table  t_test_position_task
PARTITION BY RANGE(TO_DAYS(`create_time`))
(
    PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
    PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    PARTITION p202203 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
    PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
    PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01'))
	);

###对表进行分区执行过程:

dba_user@tidb04 22:16:  [db]> alter table  t_test_position_task
    -> PARTITION BY RANGE(TO_DAYS(`create_time`))
    -> (
    ->     PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
    ->     PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
    ->     PARTITION p202203 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    ->     PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
    ->     PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
    ->     PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01'))
    -> );
Query OK, 868368 rows affected (10.53 sec)
Records: 868368  Duplicates: 0  Warnings: 0

root@tidb04 22:45:  [testdb]>  SELECT TABLE_SCHEMA,  TABLE_NAME, PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_METHOD,ARTITION_DESCRIPTION,TABLE_ROWS,CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH, CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH, CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE  FROM INFORMATION_SCHEMA.PARTITIONS  WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA','performance_schema') AND PARTITION_NAME IS NOT NULL  AND TABLE_SCHEMA='testdb' AND TABLE_NAME='t_test_position_task_result'; 
+--------------+-------------------------------+----------------+------------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME                    | PARTITION_NAME | PARTITION_EXPRESSION   | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+-------------------------------+----------------+------------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| testdb     | t_test_position_task_result | p202201        | to_days(`create_time`) | RANGE            | 738552                |          0 | 0.02M       | 0.05M        | 0.06M      |
| testdb     | t_test_position_task_result | p202202        | to_days(`create_time`) | RANGE            | 738580                |     725753 | 228.80M     | 56.66M       | 285.45M    |
| testdb     | t_test_position_task_result | p202203        | to_days(`create_time`) | RANGE            | 738611                |     266987 | 63.61M      | 25.55M       | 89.16M     |
| testdb     | t_test_position_task_result | p202204        | to_days(`create_time`) | RANGE            | 738641                |          0 | 0.02M       | 0.05M        | 0.06M      |
| testdb     | t_test_position_task_result | p202205        | to_days(`create_time`) | RANGE            | 738672                |          0 | 0.02M       | 0.05M        | 0.06M      |
| testdb     | t_test_position_task_result | p202206        | to_days(`create_time`) | RANGE            | 738702                |          0 | 0.02M       | 0.05M        | 0.06M      |
+--------------+-------------------------------+----------------+------------------------+------------------+-----------------------+------------+-------------+--------------+------------+
6 rows in set (0.00 sec)
###查看月分区表信息:
root@tidb04 22:50:  [testdb]> desc select * from t_test_position_task_result  where create_time >= '2022-02-18 00:00:00' and create_time <= '2022-02-28 23:59:59';
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                         | partitions | type | possible_keys   | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_test_position_task_result | p202202    | ALL  | idx_create_time | NULL | NULL    | NULL | 725753 |    50.00 | Using where |
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

root@tidb04 22:50:  [testdb]> 
root@tidb04 22:50:  [testdb]> desc select * from t_test_position_task_result  where create_time >= '2022-03-01 00:00:00' and create_time <= '2022-03-31 23:59:59';
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                         | partitions | type | possible_keys   | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_test_position_task_result | p202203    | ALL  | idx_create_time | NULL | NULL    | NULL | 266987 |    50.00 | Using where |
+----+-------------+-------------------------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

举例三、按季度创建分区表

CREATE TABLE `s_device_change_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `operate_bill_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联单据ID',
  `operate_bill_type` tinyint(2) NOT NULL DEFAULT '0' COMMENT '关联单据类型',
  `operate_bill_sn` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '操作单据号',
  `operate_bill_flag` tinyint(2) NOT NULL DEFAULT '0' COMMENT '操作单据标识：0:正向操作；1：反向操作',
  `business_bill_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '业务单据ID',
  `business_bill_sn` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '业务单据号',
  `operate_type` tinyint(2) NOT NULL DEFAULT '0' COMMENT '操作类型,1:变更库存状态,2:变更仓库,3:变更sn,4:变更SKU,5:变更价值量',
  `device_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '资产ID',
  `is_depreciation` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否折旧（0：否，1：是）',
  `sku_type` tinyint(2) NOT NULL DEFAULT '0' COMMENT '商品类型（0:配件 , 1:设备 , 2:耗材）',
  `origin_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '操作前状态/仓库/sn/价值量/sku',
  `change_balance` decimal(21,2) DEFAULT '0.00' COMMENT '变更价值量',
  `current_warehouse_id` int(11) NOT NULL DEFAULT '0' COMMENT '操作后仓库',
  `current_sku_id` bigint(11) NOT NULL DEFAULT '0' COMMENT '操作后sku',
  `current_sn` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '操作后序列号',
  `current_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '操作后状态',
  `current_balance` decimal(21,2) NOT NULL DEFAULT '0.00' COMMENT '操作后价值',
  `current_subject` int(4) NOT NULL DEFAULT '1001' COMMENT '设备当前主体',
  `operate_date` int(15) NOT NULL DEFAULT '0' COMMENT '操作日期（yyyyMMdd）',
  `created_date` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`,`created_date`),
  KEY `idx_device_id` (`device_id`,`operate_type`) USING BTREE,
  KEY `idx_device_stock_operat` (`operate_bill_type`,`operate_bill_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE(TO_DAYS(`created_date`))
(
    PARTITION p2018q4 VALUES LESS THAN (TO_DAYS('2019-01-01')),

    PARTITION p2019q1 VALUES LESS THAN (TO_DAYS('2019-04-01')),
    PARTITION p2019q2 VALUES LESS THAN (TO_DAYS('2019-07-01')),
    PARTITION p2019q3 VALUES LESS THAN (TO_DAYS('2019-10-01')),
    PARTITION p2019q4 VALUES LESS THAN (TO_DAYS('2020-01-01')),

    PARTITION p2020q1 VALUES LESS THAN (TO_DAYS('2020-04-01')),
    PARTITION p2020q2 VALUES LESS THAN (TO_DAYS('2020-07-01')),
    PARTITION p2020q3 VALUES LESS THAN (TO_DAYS('2020-10-01')),
    PARTITION p2020q4 VALUES LESS THAN (TO_DAYS('2021-01-01')),

    PARTITION p2021q1 VALUES LESS THAN (TO_DAYS('2021-04-01')),
    PARTITION p2021q2 VALUES LESS THAN (TO_DAYS('2021-07-01')),
    PARTITION p2021q3 VALUES LESS THAN (TO_DAYS('2021-10-01')),
    PARTITION p2021q4 VALUES LESS THAN (TO_DAYS('2022-01-01')),

    PARTITION p2022q1 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    PARTITION p2022q2 VALUES LESS THAN (TO_DAYS('2022-07-01')),
    PARTITION p2022q3 VALUES LESS THAN (TO_DAYS('2022-10-01')),
    PARTITION p2022q4 VALUES LESS THAN (TO_DAYS('2023-01-01'))
);

举例四：按周进行分区

alter table  t_company_info_log
PARTITION BY RANGE(TO_DAYS(`create_time`))
(
    PARTITION p202207w VALUES LESS THAN (TO_DAYS('2022-02-20')),
    PARTITION p202208w VALUES LESS THAN (TO_DAYS('2022-02-27')),
    PARTITION p202209w VALUES LESS THAN (TO_DAYS('2022-03-06')),
    PARTITION p202210w VALUES LESS THAN (TO_DAYS('2022-03-13'))
	);
	
root@tidb04 23:03:  [testdb]> alter table  t_company_info_log
    -> PARTITION BY RANGE(TO_DAYS(`create_time`))
    -> (
    ->     PARTITION p202207w VALUES LESS THAN (TO_DAYS('2022-02-20')),
    ->     PARTITION p202208w VALUES LESS THAN (TO_DAYS('2022-02-27')),
    ->     PARTITION p202209w VALUES LESS THAN (TO_DAYS('2022-03-06')),
    ->     PARTITION p202210w VALUES LESS THAN (TO_DAYS('2022-03-13'))
    -> );


Query OK, 5657859 rows affected (4 min 19.94 sec)
Records: 5657859  Duplicates: 0  Warnings: 0



root@tidb04 23:27:  [testdb]> desc select * from t_company_info_log where create_time >= '2022-02-18 00:00:00' and create_time <= '2022-02-19 23:59:59' ;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_company_info_log | p202207w   | ALL  | NULL          | NULL | NULL    | NULL | 79438 |    11.11 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@tidb04 23:29:  [testdb]> desc select * from t_company_info_log where create_time >= '2022-02-20 00:00:00' and create_time <= '2022-02-26 23:59:59' ;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_company_info_log | p202208w   | ALL  | NULL          | NULL | NULL    | NULL | 1692429 |    11.11 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查看实例中的分区表相关信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       count(PARTITION_NAME) AS PARTITION_COUNT,
       sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
       CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
         TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;