MySQL分區(qū)表語法解讀
更新時間:2025年02月18日 10:45:33 作者:Why9310
MySQL分區(qū)表主要用于提高查詢效率,通過將數(shù)據(jù)分割成更小的部分進行管理,文章詳細介紹了如何創(chuàng)建、查詢、修改和存儲分區(qū)表,包括創(chuàng)建復合主鍵、按年份和月份分區(qū)、刪除分區(qū)、查詢分區(qū)數(shù)據(jù)以及利用存儲過程批量轉(zhuǎn)換非分區(qū)表為分區(qū)表等方法
MySQL分區(qū)表語法
1.創(chuàng)建分區(qū)表
分區(qū)鍵需要和主鍵設(shè)置為復合主鍵,分區(qū)表不可直接轉(zhuǎn)換成非分區(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') );
- 修改表結(jié)構(gòu),增加分區(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)合主鍵存儲過程,先設(shè)置聯(lián)合主鍵字段非空,再刪除原id去掉主鍵,再設(shè)置聯(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');
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
解決ERROR?1129?(HY000):?Host?‘xxx‘?is?blocked?because?
這篇文章主要介紹了解決ERROR?1129?(HY000):?Host?‘xxx‘?is?blocked?because?of?many問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04Can''t connect to local MySQL through socket ''/tmp/mysql.so
今天小編就為大家分享一篇關(guān)于Can't connect to local MySQL through socket '/tmp/mysql.sock'解決方法,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2019-03-03MySQL?優(yōu)化利器?SHOW?PROFILE?的實現(xiàn)原理及細節(jié)展示
這篇文章主要介紹了MySQL優(yōu)化利器SHOW?PROFILE的實現(xiàn)原理,通過實例代碼展示SHOW PROFILE的用法,需要的朋友可以參考下2024-12-12MySQL深入詳解delete與Truncate及drop的使用區(qū)別
對于drop、truncate和delete雖然簡單,但是真要使用或者面試時候問到還是需要有一定的總結(jié),下面這篇文章主要給大家介紹了關(guān)于mysql中drop、truncate與delete區(qū)別的相關(guān)資料,需要的朋友可以參考下2022-07-07