欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時(shí)間:2025年02月18日 10:45:33   作者:Why9310  
MySQL分區(qū)表主要用于提高查詢效率,通過(guò)將數(shù)據(jù)分割成更小的部分進(jìn)行管理,文章詳細(xì)介紹了如何創(chuàng)建、查詢、修改和存儲(chǔ)分區(qū)表,包括創(chuàng)建復(fù)合主鍵、按年份和月份分區(qū)、刪除分區(qū)、查詢分區(qū)數(shù)據(jù)以及利用存儲(chǔ)過(guò)程批量轉(zhuǎn)換非分區(qū)表為分區(qū)表等方法

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?of?many問(wè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-04
  • Can''t connect to local MySQL through socket ''/tmp/mysql.sock''解決方法

    Can''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-03
  • MySQL復(fù)制優(yōu)點(diǎn)、原理詳解

    MySQL復(fù)制優(yōu)點(diǎn)、原理詳解

    本篇文章主要給大家詳細(xì)講解了MySQL復(fù)制優(yōu)點(diǎn)以及Mysql復(fù)制的原理知識(shí),對(duì)此有興趣的朋友學(xué)習(xí)下。
    2018-02-02
  • mysql斷電后無(wú)法啟動(dòng)的問(wèn)題小結(jié)

    mysql斷電后無(wú)法啟動(dòng)的問(wèn)題小結(jié)

    這篇文章主要介紹了mysql斷電后無(wú)法啟動(dòng)的問(wèn)題小結(jié),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧
    2024-04-04
  • MySQL性能優(yōu)化

    MySQL性能優(yōu)化

    MySQL是目前使用最多的開(kāi)源數(shù)據(jù)庫(kù),但是MySQL數(shù)據(jù)庫(kù)的默認(rèn)設(shè)置性能非常的差,僅僅是一個(gè)玩具數(shù)據(jù)庫(kù)。因此在產(chǎn)品中使用MySQL數(shù)據(jù)庫(kù)必須進(jìn)行必要的優(yōu)化
    2013-02-02
  • Windows下mysql5.7.18安裝配置教程

    Windows下mysql5.7.18安裝配置教程

    這篇文章主要為大家詳細(xì)介紹了Windows下mysql5.7.18安裝配置教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2017-07-07
  • MySQL之my.cnf配置文件圖文詳解

    MySQL之my.cnf配置文件圖文詳解

    my.cnf是mysql啟動(dòng)時(shí)加載的配置文件,一般會(huì)放在mysql的安裝目錄中,用戶也可以放在其他目錄加載,下面這篇文章主要給大家介紹了關(guān)于MySQL之my.cnf配置文件的相關(guān)資料,需要的朋友可以參考下
    2022-09-09
  • MySQL?優(yōu)化利器?SHOW?PROFILE?的實(shí)現(xiàn)原理及細(xì)節(jié)展示

    MySQL?優(yōu)化利器?SHOW?PROFILE?的實(shí)現(xiàn)原理及細(xì)節(jié)展示

    這篇文章主要介紹了MySQL優(yōu)化利器SHOW?PROFILE的實(shí)現(xiàn)原理,通過(guò)實(shí)例代碼展示SHOW PROFILE的用法,需要的朋友可以參考下
    2024-12-12
  • MySQL深入詳解delete與Truncate及drop的使用區(qū)別

    MySQL深入詳解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-07
  • Windows安裝MySQL8.0時(shí)的報(bào)錯(cuò)匯總及解決方案

    Windows安裝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

最新評(píng)論