1. MySQL创建带分区的表的sql语句

create table check_work_v2_suppl
(id                  varchar(50)                         not null comment '主键id',day_id              date                                not null comment '被补卡日期，yyyy-MM-dd格式日期分区字段',project_id          varchar(50)                         null comment '项目id',user_id             varchar(50)                         not null comment '打卡用户id',leader_id           varchar(50)                         not null comment '打卡用户直属上级领导id',clock_in_time       time                                not null comment '上班/下班打卡时间',clock_in_location   varchar(500)                        not null comment '打卡地点',clock_in_type       int(2)                              not null comment '打卡类型：0-签到，1-签退',attendance_type     int(2)                              not null comment '考勤类型：0-日常考勤，1-项目考勤',job_content         text                                null comment '工作内容',deliverables        text                                null comment '交付物',deliverables_status varchar(50)                         null comment '交付物状态',approval_status       int(2)    default 0                 not null comment '补卡流程审批状态：0-审批中，1-审批完成',update_time         timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '记录被更新时间',create_time         timestamp default CURRENT_TIMESTAMP not null comment '记录被创建时间',version             bigint    default 0                 not null comment '版本号',primary key (id, day_id)
)ENGINE = InnoDB
DEFAULT CHARSET = utf8 comment '考勤补卡表：存储实际的补卡记录'/*!50100 PARTITION BY RANGE (TO_DAYS(day_id))(PARTITION p20200701 VALUES LESS THAN (737972) ENGINE = InnoDB,PARTITION p20200702 VALUES LESS THAN (737973) ENGINE = InnoDB,PARTITION p20200703 VALUES LESS THAN (737974) ENGINE = InnoDB,PARTITION p20200704 VALUES LESS THAN (737975) ENGINE = InnoDB,PARTITION p20200705 VALUES LESS THAN (737976) ENGINE = InnoDB,PARTITION p20200706 VALUES LESS THAN (737977) ENGINE = InnoDB,PARTITION p20200707 VALUES LESS THAN (737978) ENGINE = InnoDB,PARTITION p20200708 VALUES LESS THAN (737979) ENGINE = InnoDB,PARTITION p20200709 VALUES LESS THAN (737980) ENGINE = InnoDB,PARTITION p20200710 VALUES LESS THAN (737981) ENGINE = InnoDB,PARTITION p20200711 VALUES LESS THAN (737982) ENGINE = InnoDB,PARTITION p20200712 VALUES LESS THAN (737983) ENGINE = InnoDB,PARTITION p20200713 VALUES LESS THAN (737984) ENGINE = InnoDB,PARTITION p20200714 VALUES LESS THAN (737985) ENGINE = InnoDB,PARTITION p20200715 VALUES LESS THAN (737986) ENGINE = InnoDB) */

2. 查看见表语句

show create table check_work_v2_suppl

3. 单独添加分区

alter table check_work_v2 PARTITION BY RANGE (TO_DAYS(day_id)) (PARTITION p20200701 VALUES LESS THAN (TO_DAYS('20200701')),PARTITION p20200702 VALUES LESS THAN (TO_DAYS('20200702')),PARTITION p20200703 VALUES LESS THAN (TO_DAYS('20200703')),PARTITION p20200704 VALUES LESS THAN (TO_DAYS('20200704')),PARTITION p20200705 VALUES LESS THAN (TO_DAYS('20200705')),PARTITION p20200706 VALUES LESS THAN (TO_DAYS('20200706')),PARTITION p20200707 VALUES LESS THAN (TO_DAYS('20200707')),PARTITION p20200708 VALUES LESS THAN (TO_DAYS('20200708')),PARTITION p20200709 VALUES LESS THAN (TO_DAYS('20200709')),PARTITION p20200710 VALUES LESS THAN (TO_DAYS('20200710')));alter table check_work_v2 add partition (PARTITION p20200712 VALUES LESS THAN (TO_DAYS('20200712')))
alter table check_work_v2 add partition (PARTITION p20200713 VALUES LESS THAN (TO_DAYS('20200713')))
alter table check_work_v2 add partition (PARTITION p20200714 VALUES LESS THAN (TO_DAYS('20200714')))
alter table check_work_v2 add partition (PARTITION p20200715 VALUES LESS THAN (TO_DAYS('20200715')))

4. 创建联合主键

alter table check_work_v2 add primary key (id,day_id);

5. 查询分区

selectpartition_name part,partition_expression expr,partition_description descr,table_rows
from information_schema.partitions  wheretable_schema = schema()
and table_name='check_work_v2';show variables like '%partition%';