MySQL分區(qū)表語法解讀

更新時間：2025年02月18日 10:45:33 作者：Why9310

MySQL分區(qū)表主要用于提高查詢效率,通過將數(shù)據(jù)分割成更小的部分進行管理,文章詳細介紹了如何創(chuàng)建、查詢、修改和存儲分區(qū)表,包括創(chuàng)建復合主鍵、按年份和月份分區(qū)、刪除分區(qū)、查詢分區(qū)數(shù)據(jù)以及利用存儲過程批量轉換非分區(qū)表為分區(qū)表等方法

MySQL分區(qū)表語法

1.創(chuàng)建分區(qū)表

分區(qū)鍵需要和主鍵設置為復合主鍵，分區(qū)表不可直接轉換成非分區(qū)表，需要重新建非分區(qū)表并導入數(shù)據(jù)

按年份

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')
);

修改表結構，增加分區(qū)

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')
                 );

刪除分區(qū)，注意：刪除分區(qū)的時候會同時刪除數(shù)據(jù)

ALTER TABLE partitioned_table_month DROP PARTITION p202407,p202408;

2.查詢

查看表分區(qū)

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';

查看分區(qū)數(shù)據(jù)

select * from partitioned_table PARTITION (p2024,p2025)

3.利用存儲過程批量修改非分區(qū)表為分區(qū)表

創(chuàng)建聯(lián)合主鍵存儲過程，先設置聯(lián)合主鍵字段非空，再刪除原id去掉主鍵，再設置聯(lián)合主鍵

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 ;

創(chuàng)建按年自動分區(qū)存儲過程

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 ;

創(chuàng)建按月自動分區(qū)存儲過程

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%';
-- 執(zhí)行聯(lián)合主鍵
CALL auto_create_pk('table_a','a_time','時間'); 
-- 執(zhí)行按年自動分區(qū)
CALL auto_create_partition_year('table_b','b_time'); 
-- 執(zhí)行按月自動分區(qū)
CALL auto_create_partition_month('table_c','c_time');