此语句有锁表的情况，请慎重使用

1、需要设置主键

ALTER TABLE `xxxx_out` PARTITION BY RANGE (to_seconds(`out_date`))PARTITIONS 120 (PARTITION p202001 VALUES LESS THAN (63747734400) ENGINE = InnoDB,PARTITION p202002 VALUES LESS THAN (63750240000) ENGINE = InnoDB,PARTITION p202003 VALUES LESS THAN (63752918400) ENGINE = InnoDB,PARTITION p202004 VALUES LESS THAN (63755510400) ENGINE = InnoDB,PARTITION p202005 VALUES LESS THAN (63758188800) ENGINE = InnoDB,PARTITION p202006 VALUES LESS THAN (63760780800) ENGINE = InnoDB,PARTITION p202007 VALUES LESS THAN (63763459200) ENGINE = InnoDB,PARTITION p202008 VALUES LESS THAN (63766137600) ENGINE = InnoDB,PARTITION p202009 VALUES LESS THAN (63768729600) ENGINE = InnoDB,PARTITION p202010 VALUES LESS THAN (63771408000) ENGINE = InnoDB,PARTITION p202011 VALUES LESS THAN (63774000000) ENGINE = InnoDB,PARTITION p202012 VALUES LESS THAN (63776678400) ENGINE = InnoDB,PARTITION p202101 VALUES LESS THAN (63779356800) ENGINE = InnoDB,PARTITION p202102 VALUES LESS THAN (63781776000) ENGINE = InnoDB,PARTITION p202103 VALUES LESS THAN (63784454400) ENGINE = InnoDB,PARTITION p202104 VALUES LESS THAN (63787046400) ENGINE = InnoDB,PARTITION p202105 VALUES LESS THAN (63789724800) ENGINE = InnoDB,PARTITION p202106 VALUES LESS THAN (63792316800) ENGINE = InnoDB,PARTITION p202107 VALUES LESS THAN (63794995200) ENGINE = InnoDB,PARTITION p202108 VALUES LESS THAN (63797673600) ENGINE = InnoDB,PARTITION p202109 VALUES LESS THAN (63800265600) ENGINE = InnoDB,PARTITION p202110 VALUES LESS THAN (63802944000) ENGINE = InnoDB,PARTITION p202111 VALUES LESS THAN (63805536000) ENGINE = InnoDB,PARTITION p202112 VALUES LESS THAN (63808214400) ENGINE = InnoDB,PARTITION p202201 VALUES LESS THAN (63810892800) ENGINE = InnoDB,PARTITION p202202 VALUES LESS THAN (63813312000) ENGINE = InnoDB,PARTITION p202203 VALUES LESS THAN (63815990400) ENGINE = InnoDB,PARTITION p202204 VALUES LESS THAN (63818582400) ENGINE = InnoDB,PARTITION p202205 VALUES LESS THAN (63821260800) ENGINE = InnoDB,PARTITION p202206 VALUES LESS THAN (63823852800) ENGINE = InnoDB,PARTITION p202207 VALUES LESS THAN (63826531200) ENGINE = InnoDB,PARTITION p202208 VALUES LESS THAN (63829209600) ENGINE = InnoDB,PARTITION p202209 VALUES LESS THAN (63831801600) ENGINE = InnoDB,PARTITION p202210 VALUES LESS THAN (63834480000) ENGINE = InnoDB,PARTITION p202211 VALUES LESS THAN (63837072000) ENGINE = InnoDB,PARTITION p202212 VALUES LESS THAN (63839750400) ENGINE = InnoDB,PARTITION p202301 VALUES LESS THAN (63842428800) ENGINE = InnoDB,PARTITION p202302 VALUES LESS THAN (63844848000) ENGINE = InnoDB,PARTITION p202303 VALUES LESS THAN (63847526400) ENGINE = InnoDB,PARTITION p202304 VALUES LESS THAN (63850118400) ENGINE = InnoDB,PARTITION p202305 VALUES LESS THAN (63852796800) ENGINE = InnoDB,PARTITION p202306 VALUES LESS THAN (63855388800) ENGINE = InnoDB,PARTITION p202307 VALUES LESS THAN (63858067200) ENGINE = InnoDB,PARTITION p202308 VALUES LESS THAN (63860745600) ENGINE = InnoDB,PARTITION p202309 VALUES LESS THAN (63863337600) ENGINE = InnoDB,PARTITION p202310 VALUES LESS THAN (63866016000) ENGINE = InnoDB,PARTITION p202311 VALUES LESS THAN (63868608000) ENGINE = InnoDB,PARTITION p202312 VALUES LESS THAN (63871286400) ENGINE = InnoDB,PARTITION p202401 VALUES LESS THAN (63873964800) ENGINE = InnoDB,PARTITION p202402 VALUES LESS THAN (63876470400) ENGINE = InnoDB,PARTITION p202403 VALUES LESS THAN (63879148800) ENGINE = InnoDB,PARTITION p202404 VALUES LESS THAN (63881740800) ENGINE = InnoDB,PARTITION p202405 VALUES LESS THAN (63884419200) ENGINE = InnoDB,PARTITION p202406 VALUES LESS THAN (63887011200) ENGINE = InnoDB,PARTITION p202407 VALUES LESS THAN (63889689600) ENGINE = InnoDB,PARTITION p202408 VALUES LESS THAN (63892368000) ENGINE = InnoDB,PARTITION p202409 VALUES LESS THAN (63894960000) ENGINE = InnoDB,PARTITION p202410 VALUES LESS THAN (63897638400) ENGINE = InnoDB,PARTITION p202411 VALUES LESS THAN (63900230400) ENGINE = InnoDB,PARTITION p202412 VALUES LESS THAN (63902908800) ENGINE = InnoDB,PARTITION p202501 VALUES LESS THAN (63905587200) ENGINE = InnoDB,PARTITION p202502 VALUES LESS THAN (63908006400) ENGINE = InnoDB,PARTITION p202503 VALUES LESS THAN (63910684800) ENGINE = InnoDB,PARTITION p202504 VALUES LESS THAN (63913276800) ENGINE = InnoDB,PARTITION p202505 VALUES LESS THAN (63915955200) ENGINE = InnoDB,PARTITION p202506 VALUES LESS THAN (63918547200) ENGINE = InnoDB,PARTITION p202507 VALUES LESS THAN (63921225600) ENGINE = InnoDB,PARTITION p202508 VALUES LESS THAN (63923904000) ENGINE = InnoDB,PARTITION p202509 VALUES LESS THAN (63926496000) ENGINE = InnoDB,PARTITION p202510 VALUES LESS THAN (63929174400) ENGINE = InnoDB,PARTITION p202511 VALUES LESS THAN (63931766400) ENGINE = InnoDB,PARTITION p202512 VALUES LESS THAN (63934444800) ENGINE = InnoDB,PARTITION p202601 VALUES LESS THAN (63937123200) ENGINE = InnoDB,PARTITION p202602 VALUES LESS THAN (63939542400) ENGINE = InnoDB,PARTITION p202603 VALUES LESS THAN (63942220800) ENGINE = InnoDB,PARTITION p202604 VALUES LESS THAN (63944812800) ENGINE = InnoDB,PARTITION p202605 VALUES LESS THAN (63947491200) ENGINE = InnoDB,PARTITION p202606 VALUES LESS THAN (63950083200) ENGINE = InnoDB,PARTITION p202607 VALUES LESS THAN (63952761600) ENGINE = InnoDB,PARTITION p202608 VALUES LESS THAN (63955440000) ENGINE = InnoDB,PARTITION p202609 VALUES LESS THAN (63958032000) ENGINE = InnoDB,PARTITION p202610 VALUES LESS THAN (63960710400) ENGINE = InnoDB,PARTITION p202611 VALUES LESS THAN (63963302400) ENGINE = InnoDB,PARTITION p202612 VALUES LESS THAN (63965980800) ENGINE = InnoDB,PARTITION p202701 VALUES LESS THAN (63968659200) ENGINE = InnoDB,PARTITION p202702 VALUES LESS THAN (63971078400) ENGINE = InnoDB,PARTITION p202703 VALUES LESS THAN (63973756800) ENGINE = InnoDB,PARTITION p202704 VALUES LESS THAN (63976348800) ENGINE = InnoDB,PARTITION p202705 VALUES LESS THAN (63979027200) ENGINE = InnoDB,PARTITION p202706 VALUES LESS THAN (63981619200) ENGINE = InnoDB,PARTITION p202707 VALUES LESS THAN (63984297600) ENGINE = InnoDB,PARTITION p202708 VALUES LESS THAN (63986976000) ENGINE = InnoDB,PARTITION p202709 VALUES LESS THAN (63989568000) ENGINE = InnoDB,PARTITION p202710 VALUES LESS THAN (63992246400) ENGINE = InnoDB,PARTITION p202711 VALUES LESS THAN (63994838400) ENGINE = InnoDB,PARTITION p202712 VALUES LESS THAN (63997516800) ENGINE = InnoDB,PARTITION p202801 VALUES LESS THAN (64000195200) ENGINE = InnoDB,PARTITION p202802 VALUES LESS THAN (64002700800) ENGINE = InnoDB,PARTITION p202803 VALUES LESS THAN (64005379200) ENGINE = InnoDB,PARTITION p202804 VALUES LESS THAN (64007971200) ENGINE = InnoDB,PARTITION p202805 VALUES LESS THAN (64010649600) ENGINE = InnoDB,PARTITION p202806 VALUES LESS THAN (64013241600) ENGINE = InnoDB,PARTITION p202807 VALUES LESS THAN (64015920000) ENGINE = InnoDB,PARTITION p202808 VALUES LESS THAN (64018598400) ENGINE = InnoDB,PARTITION p202809 VALUES LESS THAN (64021190400) ENGINE = InnoDB,PARTITION p202810 VALUES LESS THAN (64023868800) ENGINE = InnoDB,PARTITION p202811 VALUES LESS THAN (64026460800) ENGINE = InnoDB,PARTITION p202812 VALUES LESS THAN (64029139200) ENGINE = InnoDB,PARTITION p202901 VALUES LESS THAN (64031817600) ENGINE = InnoDB,PARTITION p202902 VALUES LESS THAN (64034236800) ENGINE = InnoDB,PARTITION p202903 VALUES LESS THAN (64036915200) ENGINE = InnoDB,PARTITION p202904 VALUES LESS THAN (64039507200) ENGINE = InnoDB,PARTITION p202905 VALUES LESS THAN (64042185600) ENGINE = InnoDB,PARTITION p202906 VALUES LESS THAN (64044777600) ENGINE = InnoDB,PARTITION p202907 VALUES LESS THAN (64047456000) ENGINE = InnoDB,PARTITION p202908 VALUES LESS THAN (64050134400) ENGINE = InnoDB,PARTITION p202909 VALUES LESS THAN (64052726400) ENGINE = InnoDB,PARTITION p202910 VALUES LESS THAN (64055404800) ENGINE = InnoDB,PARTITION p202911 VALUES LESS THAN (64057996800) ENGINE = InnoDB,PARTITION p202912 VALUES LESS THAN (64060675200) ENGINE = InnoDB);

2、查看分区表数据

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'xxxx_out'; SELECT table_name,partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_schema = 'db_name' and table_name in('xxxx_out') and partition_name is not null order by table_name,partition_name;  SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'xxxx_out' and TABLE_SCHEMA='db_name' and PARTITION_DESCRIPTION<=TO_SECONDS('2022-06-01') order by PARTITION_DESCRIPTION; -- 查询一个分区的数据select * from xxxx_out partition (p20200501); -- 删除分区ALTER TABLE xxxx_out DROP PARTITION p202909;