mysql分区基本语法

分区创建表sql

CREATE TABLE `tb_original` (
`id` varchar(50) NOT NULL,
`task_source` varchar(200) DEFAULT NULL,
`address_id` varchar(50) DEFAULT NULL,
`norm_address` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
`address_freq` varchar(200) DEFAULT NULL,
`adcode` varchar(255) DEFAULT NULL,
`zno_code` varchar(200) DEFAULT NULL,
`team_code` varchar(200) DEFAULT NULL,
`worker_id` varchar(200) DEFAULT NULL COMMENT '原始表',
`city_code` varchar(200) DEFAULT NULL,
`sss_teamcode` varchar(200) DEFAULT NULL,
`sss_znocode` varchar(200) DEFAULT NULL,
`sch_teamcode` varchar(200) DEFAULT NULL,
`sch_znocode` varchar(200) DEFAULT NULL,
`insert_date` datetime DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
`file_name` varchar(50) DEFAULT NULL,
`city_adcode` varchar(200) NOT NULL,
PRIMARY KEY (`id`,city_adcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST COLUMNS(city_adcode) (
PARTITION p000 VALUES IN ('000'),
PARTITION p010 VALUES IN ('010'),
PARTITION p020 VALUES IN ('020'),
PARTITION p021 VALUES IN ('021'),
PARTITION p027 VALUES IN ('027'),
PARTITION p028 VALUES IN ('028'),
PARTITION p512 VALUES IN ('512'),
PARTITION p571 VALUES IN ('571'),
PARTITION p755 VALUES IN ('755'),
PARTITION p769 VALUES IN ('769')
);

insert into tb_original select *,'010' from tb_original_010;
insert into tb_original select *,'020' from tb_original_020;
insert into tb_original select *,'021' from tb_original_021;

查看分区情况

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_original'

#新增和删除分区
ALTER TABLE tb_original  ADD PARTITION (PARTITION p111 VALUES IN ('111'));

ALTER TABLE tb_original  DROP PARTITION p111;