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