MySQL分区表语法

1.创建分区表
分区键需要和主键设置为复合主键，分区表不可直接转换成非分区表，需要重新建非分区表并导入数据

• 按年份

CREATE TABLE partitioned_table_year (
    id INT,
    content VARCHAR(50),
    created_time DATETIME,
                PRIMARY KEY (id,created_time)
) PARTITION BY RANGE(YEAR(created_time)) ( 
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);

• 按月份

CREATE TABLE partitioned_table_month (
    id INT,
    content VARCHAR(50),
    created_time DATETIME,
                PRIMARY KEY (id,created_time)
) PARTITION BY RANGE  COLUMNS(created_time)  (  
    PARTITION p202410 VALUES LESS THAN ('2024-11-01'),
    PARTITION p202411 VALUES LESS THAN ('2024-12-01'), 
    PARTITION p202412 VALUES LESS THAN ('2025-01-01')
);

• 修改表结构，增加分区

ALTER TABLE `partitioned_table_month` MODIFY COLUMN `created_time` datetime(0) NOT NULL ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `created_time`) USING BTREE;

ALTER TABLE partitioned_table_month PARTITION BY RANGE COLUMNS(created_time) (  
    PARTITION p202410 VALUES LESS THAN ('2024-11-01'),
    PARTITION p202411 VALUES LESS THAN ('2024-12-01'), 
    PARTITION p202412 VALUES LESS THAN ('2025-01-01')
                 );

• 删除分区，注意：删除分区的时候会同时删除数据

ALTER TABLE partitioned_table_month DROP PARTITION p202407,p202408;

2.查询

• 查看表分区

SELECT  
    TABLE_NAME,
    PARTITION_NAME, 
    PARTITION_METHOD, 
    PARTITION_EXPRESSION, 
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH 
FROM 
    information_schema.PARTITIONS
WHERE 
    TABLE_SCHEMA = 'xxx' and TABLE_NAME = 'partitioned_table_month';  

• 查看分区数据

 select * from partitioned_table PARTITION (p2024,p2025)

3.利用存储过程批量修改非分区表为分区表

• 创建联合主键存储过程，先设置联合主键字段非空，再删除原id去掉主键，再设置联合主键

DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_pk$$
CREATE PROCEDURE `auto_create_pk`(IN `table_name` varchar(64),IN `column_name` varchar(64),IN `column_comment` varchar(64))
BEGIN
	  SET @sql = CONCAT("ALTER TABLE `",table_name,"` MODIFY COLUMN `",column_name,"` datetime  NOT NULL COMMENT '",column_comment,"',
		DROP PRIMARY KEY,
		ADD PRIMARY KEY ( `id`, `",column_name,"` ) USING BTREE;");
		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
END$$ 
DELIMITER ;

• 创建按年自动分区存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition_year$$
CREATE PROCEDURE `auto_create_partition_year`(IN `table_name` varchar(64),IN `column_name` varchar(64))
BEGIN
		DECLARE partitioned LONGTEXT;
		DECLARE n INT;
		set n = 2025;
		set partitioned = '';
		WHILE n <= 2027 DO
			SET partitioned = CONCAT(partitioned,",PARTITION p",n," VALUES LESS THAN (",n+1,")");
			SET n = n + 1;
			END WHILE;  
		SET @sql = CONCAT ("ALTER TABLE ",table_name,"  PARTITION BY RANGE(YEAR(",column_name,")) (",SUBSTR(partitioned,2,LENGTH(partitioned)),");") ; 
		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
END$$ 
DELIMITER ;

• 创建按月自动分区存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition_month$$
CREATE PROCEDURE `auto_create_partition_month`(IN `table_name` varchar(64),IN `column_name` varchar(64))
BEGIN
		DECLARE partitioned LONGTEXT;
		DECLARE n INT;
		DECLARE m INT;
		set n = 2015;
		set partitioned = '';
		WHILE n <= 2030 DO
		set m = 1;
			WHILE m < 12 DO
				SET partitioned = CONCAT(partitioned,",PARTITION p",n,LPAD(m,2,0)," VALUES LESS THAN ('",n,"-",LPAD(m+1,2,0),"-01')");
				SET m = m + 1;
			END WHILE;  
			IF m = 12 THEN 
				SET partitioned = CONCAT(partitioned,",PARTITION p",n,"12 VALUES LESS THAN ('",n+1,"-01-01')");
			END IF;
			SET n = n + 1;
			END WHILE;   
		SET @sql = CONCAT ("ALTER TABLE ",table_name,"  PARTITION BY RANGE COLUMNS(",column_name,")  (",SUBSTR(partitioned,2,LENGTH(partitioned)),");") ; 
		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
END$$ 
DELIMITER ;

-- 查询存储过程
show procedure status like 'auto_create_partition%';
-- 执行联合主键
CALL auto_create_pk('table_a','a_time','时间'); 
-- 执行按年自动分区
CALL auto_create_partition_year('table_b','b_time'); 
-- 执行按月自动分区
CALL auto_create_partition_month('table_c','c_time');