MySQL分區(qū)表語(yǔ)法解讀
MySQL分區(qū)表語(yǔ)法
1.創(chuàng)建分區(qū)表
分區(qū)鍵需要和主鍵設(shè)置為復(fù)合主鍵,分區(qū)表不可直接轉(zhuǎn)換成非分區(qū)表,需要重新建非分區(qū)表并導(dǎo)入數(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í)候會(huì)同時(shí)刪除數(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.利用存儲(chǔ)過(guò)程批量修改非分區(qū)表為分區(qū)表
- 創(chuàng)建聯(lián)合主鍵存儲(chǔ)過(guò)程,先設(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)建按年自動(dòng)分區(qū)存儲(chǔ)過(guò)程
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)建按月自動(dòng)分區(qū)存儲(chǔ)過(guò)程
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 ;
-- 查詢存儲(chǔ)過(guò)程 show procedure status like 'auto_create_partition%'; -- 執(zhí)行聯(lián)合主鍵 CALL auto_create_pk('table_a','a_time','時(shí)間'); -- 執(zhí)行按年自動(dòng)分區(qū) CALL auto_create_partition_year('table_b','b_time'); -- 執(zhí)行按月自動(dòng)分區(qū) CALL auto_create_partition_month('table_c','c_time');
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
解決ERROR?1129?(HY000):?Host?‘xxx‘?is?blocked?because?
這篇文章主要介紹了解決ERROR?1129?(HY000):?Host?‘xxx‘?is?blocked?because?of?many問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教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'解決方法,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-03-03mysql斷電后無(wú)法啟動(dòng)的問(wèn)題小結(jié)
這篇文章主要介紹了mysql斷電后無(wú)法啟動(dòng)的問(wèn)題小結(jié),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2024-04-04MySQL?優(yōu)化利器?SHOW?PROFILE?的實(shí)現(xiàn)原理及細(xì)節(jié)展示
這篇文章主要介紹了MySQL優(yōu)化利器SHOW?PROFILE的實(shí)現(xiàn)原理,通過(guò)實(shí)例代碼展示SHOW PROFILE的用法,需要的朋友可以參考下2024-12-12MySQL深入詳解delete與Truncate及drop的使用區(qū)別
對(duì)于drop、truncate和delete雖然簡(jiǎn)單,但是真要使用或者面試時(shí)候問(wèn)到還是需要有一定的總結(jié),下面這篇文章主要給大家介紹了關(guān)于mysql中drop、truncate與delete區(qū)別的相關(guān)資料,需要的朋友可以參考下2022-07-07Windows安裝MySQL8.0時(shí)的報(bào)錯(cuò)匯總及解決方案
據(jù)說(shuō)安裝MySQL是無(wú)數(shù)數(shù)據(jù)庫(kù)初學(xué)者的噩夢(mèng),我在安裝的時(shí)候也是查了很多資料,但是很多畢竟每個(gè)人的電腦有各自不同的情況,大家的報(bào)錯(cuò)也不盡相同,所以也是很長(zhǎng)時(shí)間之后才安裝成功,所以本文給大家匯總了Windows安裝MySQL8.0時(shí)的報(bào)錯(cuò)解決方案,需要的朋友可以參考下2024-09-09