Interpretation of MySQL partition table syntax

MySQL partition table syntax

1. Create a partition table

The partition key needs to be set to the compound primary key and the partition table cannot be directly converted into a non-partitioned table. It is necessary to re-create the non-partitioned table and import the data.

By year

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

By month

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

Modify the table structure and add partitions

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

Delete partitions, note: when deleting partitions, data will be deleted at the same time

ALTER TABLE partitioned_table_month DROP PARTITION p202407,p202408;

2. Query

View table partition

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

View partition data

select * from partitioned_table PARTITION (p2024,p2025)

3. Use stored procedures to batch modify non-partitioned tables as partitioned tables

Create a joint primary key stored procedure, first set the joint primary key field not empty, then delete the original id and remove the primary key, and then set the joint primary key

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 ;

Create an annual automatic partitioning stored procedure

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 ;

Create a monthly automatic partitioning stored procedure

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 ;

-- Query stored procedures
show procedure status like 'auto_create_partition%';
-- Execute joint primary key
CALL auto_create_pk('table_a','a_time','time'); 
-- Perform automatic partitioning by year
CALL auto_create_partition_year('table_b','b_time'); 
-- Perform automatic partitioning by month
CALL auto_create_partition_month('table_c','c_time');

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.