PARTITION 按照月份分区

废话不多说，先上分区SQL

CREATE TABLE `demo`(
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
    `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间',
    `age` bigint(20) NOT NULL  COMMENT '年龄',
     PRIMARY KEY (`id`,`time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 COMMENT = 'user表' ROW_FORMAT = Compact 
    PARTITION BY RANGE (to_days(`time`)) (
    PARTITION	p202101	VALUES	LESS	THAN	(to_days('2021-02-01')),
    PARTITION	p202102	VALUES	LESS	THAN	(to_days('2021-03-01')),
    PARTITION	p202103	VALUES	LESS	THAN	(to_days('2021-04-01')),
    PARTITION	p202104	VALUES	LESS	THAN	(to_days('2021-05-01')),
    PARTITION	p202105	VALUES	LESS	THAN	(to_days('2021-06-01'))
);

查坎分区是否被应用

EXPLAIN PARTITIONS SELECT * FROM `user`

踩坑点

1、分区字段类型错误，timestamp 和 varchar 替换为datetime

 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

2、主键字段错误，分区字段必须添加到主键字段中

 A PRIMARY KEY must include all columns in the table's partitioning function

3、函数不支持，使用支出函数即可

This partition function is not allowed

mysql 5.6 支持
官网地址：https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-functions.html

ABS()
CEILING() (see CEILING() and FLOOR())
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR() (see CEILING() and FLOOR())
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()