Chapter聽18.聽Partitioning - MySQL 5.1参考手册中文版

鎸夌収RANGE鍒嗗尯鐨勮〃鏄€氳繃濡備笅涓€绉嶆柟寮忚繘琛屽垎鍖虹殑锛屾瘡涓垎鍖哄寘鍚偅浜涘垎鍖鸿〃杈惧紡鐨勫€间綅浜庝竴涓粰瀹氱殑杩炵画鍖洪棿鍐呯殑琛屻€傝繖浜涘尯闂磋杩炵画涓斾笉鑳界浉浜掗噸鍙狅紝浣跨敤VALUES LESS THAN鎿嶄綔绗︽潵杩涜瀹氫箟銆傚湪涓嬮潰鐨勫嚑涓緥瀛愪腑锛屽亣瀹氫綘鍒涘缓浜嗕竴涓涓嬬殑涓€涓〃锛岃琛ㄤ繚瀛樻湁20瀹堕煶鍍忓簵鐨勮亴鍛樿褰曪紝杩�20瀹堕煶鍍忓簵鐨勭紪鍙蜂粠1鍒�20銆�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)锛�

鏍规嵁浣犵殑闇€瑕侊紝杩欎釜琛ㄥ彲浠ユ湁澶氱鏂瑰紡鏉ユ寜鐓у尯闂磋繘琛屽垎鍖恒€備竴绉嶆柟寮忔槸浣跨敤store_id 鍒椼€備緥濡傦紝浣犲彲鑳藉喅瀹氶€氳繃娣诲姞涓€涓�PARTITION BY RANGE瀛愬彞鎶婅繖涓〃鍒嗗壊鎴�4涓尯闂达紝濡備笅鎵€绀�:

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN (21)

)锛�

鎸夌収杩欑鍒嗗尯鏂规锛屽湪鍟嗗簵1鍒�5宸ヤ綔鐨勯泧鍛樼浉瀵瑰簲鐨勬墍鏈夎琚繚瀛樺湪鍒嗗尯P0涓紝鍟嗗簵6鍒�10鐨勯泧鍛樹繚瀛樺湪P1涓紝渚濇绫绘帹銆傛敞鎰忥紝姣忎釜鍒嗗尯閮芥槸鎸夐『搴忚繘琛屽畾涔夛紝浠庢渶浣庡埌鏈€楂樸€傝繖鏄�PARTITION BY RANGE 璇硶鐨勮姹傦紱鍦ㄨ繖鐐逛笂锛屽畠绫讳技浜�C鎴�Java涓殑鈥�switch ... case鈥濊鍙ャ€�

瀵逛簬鍖呭惈鏁版嵁(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)鐨勪竴涓柊琛岋紝鍙互寰堝鏄撳湴纭畾瀹冨皢鎻掑叆鍒�p2鍒嗗尯涓紝浣嗘槸濡傛灉澧炲姞浜嗕竴涓紪鍙蜂负绗�21鐨勫晢搴楋紝灏嗕細鍙戠敓浠€涔堝憿锛熷湪杩欑鏂规涓嬶紝鐢变簬娌℃湁瑙勫垯鎶�store_id澶т簬20鐨勫晢搴楀寘鍚湪鍐咃紝鏈嶅姟鍣ㄥ皢涓嶇煡閬撴妸璇ヨ淇濆瓨鍦ㄤ綍澶勶紝灏嗕細瀵艰嚧閿欒銆� 瑕侀伩鍏嶈繖绉嶉敊璇紝鍙互閫氳繃鍦�CREATE TABLE璇彞涓�浣跨敤涓€涓€�catchall鈥� VALUES LESS THAN瀛�鍙ワ紝璇ュ瓙鍙ユ彁渚涚粰鎵€鏈夊ぇ浜庢槑纭寚瀹氱殑鏈€楂樺€肩殑鍊硷細

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)锛�

MAXVALUE 琛ㄧず鏈€澶х殑鍙兘鐨勬暣鏁板€笺€傜幇鍦紝store_id 鍒楀€煎ぇ浜庢垨绛変簬16锛堝畾涔変簡鐨勬渶楂樺€硷級鐨勬墍鏈夎閮藉皢淇濆瓨鍦ㄥ垎鍖�p3涓€傚湪灏嗘潵鐨勬煇涓椂鍊欙紝褰撳晢搴楁暟宸茬粡澧為暱鍒�25, 30, 鎴栨洿澶� 锛屽彲浠ヤ娇鐢�ALTER TABLE璇彞涓哄晢搴�21-25, 26-30,绛夌瓑澧炲姞鏂扮殑鍒嗗尯 锛堝叧浜庡浣曞疄鐜扮殑璇︾粏淇℃伅鍙傝18.3鑺傦紝鈥滃垎鍖虹鐞嗏€� )銆�

鍦ㄥ嚑涔庝竴鏍风殑缁撴瀯涓紝浣犺繕鍙互鍩轰簬闆囧憳鐨勫伐浣滀唬鐮佹潵鍒嗗壊琛紝涔熷氨鏄锛屽熀浜�job_code 鍒楀€肩殑杩炵画鍖洪棿銆備緥濡傗€斺€斿亣瀹�2浣嶆暟瀛楃殑宸ヤ綔浠ｇ爜鐢ㄦ潵琛ㄧず鏅€氾紙搴楀唴鐨勶級宸ヤ汉锛屼笁涓暟瀛椾唬鐮佽〃绀哄姙鍏鍜屾敮鎸佷汉鍛橈紝鍥涗釜鏁板瓧浠ｇ爜琛ㄧず绠＄悊灞傦紝浣犲彲浠ヤ娇鐢ㄤ笅闈㈢殑璇彞鍒涘缓璇ュ垎鍖鸿〃锛�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (job_code) (

    PARTITION p0 VALUES LESS THAN (100),

    PARTITION p1 VALUES LESS THAN (1000),

    PARTITION p2 VALUES LESS THAN (10000)

)锛�

鍦ㄨ繖涓緥瀛愪腑, 搴楀唴宸ヤ汉鐩稿叧鐨勬墍鏈夎灏嗕繚瀛樺湪鍒嗗尯p0涓紝鍔炲叕瀹ゅ拰鏀寔浜哄憳鐩稿叧鐨勬墍鏈夎淇濆瓨鍦ㄥ垎鍖�p1涓紝绠＄悊灞傜浉鍏崇殑鎵€鏈夎淇濆瓨鍦ㄥ垎鍖�p2涓€�

鍦�VALUES LESS THAN 瀛愬彞涓娇鐢ㄤ竴涓〃杈惧紡涔熸槸鍙兘鐨勩€傝繖閲屾渶鍊煎緱娉ㄦ剰鐨勯檺鍒舵槸MySQL 蹇呴』鑳藉璁＄畻琛ㄨ揪寮忕殑杩斿洖鍊间綔涓�LESS THAN (<)姣旇緝鐨勪竴閮ㄥ垎锛涘洜姝わ紝琛ㄨ揪寮忕殑鍊间笉鑳戒负NULL 銆傜敱浜庤繖涓師鍥狅紝闆囧憳琛ㄧ殑hired, separated, job_code,鍜�store_id鍒楀凡缁忚瀹氫箟涓洪潪绌猴紙NOT NULL锛夈€�

闄や簡鍙互鏍规嵁鍟嗗簵缂栧彿鍒嗗壊琛ㄦ暟鎹锛屼綘杩樺彲浠ヤ娇鐢ㄤ竴涓熀浜庝袱涓�DATE 锛堟棩鏈燂級涓殑涓€涓殑琛ㄨ揪寮忔潵鍒嗗壊琛ㄦ暟鎹€備緥濡傦紝鍋囧畾浣犳兂鍩轰簬姣忎釜闆囧憳绂诲紑鍏徃鐨勫勾浠芥潵鍒嗗壊琛紝涔熷氨鏄锛�YEAR(separated)鐨勫€笺€傚疄鐜拌繖绉嶅垎鍖烘ā寮忕殑CREATE TABLE 璇彞鐨勪竴涓緥瀛愬涓嬫墍绀猴細

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY RANGE (YEAR(separated)) (

    PARTITION p0 VALUES LESS THAN (1991),

    PARTITION p1 VALUES LESS THAN (1996),

    PARTITION p2 VALUES LESS THAN (2001),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)锛�

鍦ㄨ繖涓柟妗堜腑锛屽湪1991骞村墠闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p0涓紝1991骞村埌1995骞存湡闂�闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p1涓紝 1996骞村埌2000骞存湡闂�闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p2涓紝2000骞村悗闆囦剑鐨勬墍鏈夊伐浜虹殑淇℃伅淇濆瓨鍦�p3涓€�

RANGE鍒嗗尯鍦ㄥ涓嬪満鍚堢壒鍒湁鐢細

路         褰撻渶瑕佸垹闄も€滄棫鐨勨€濇暟鎹椂銆傚鏋滀綘浣跨敤涓婇潰鏈€杩戠殑閭ｄ釜渚嬪瓙缁欏嚭鐨勫垎鍖烘柟妗堬紝浣犲彧闇€绠€鍗曞湴浣跨敤 鈥�ALTER TABLE employees DROP PARTITION p0锛�鈥�鏉ュ垹闄ゆ墍鏈夊湪1991骞村墠灏卞凡缁忓仠姝㈠伐浣滅殑闆囧憳鐩稿搴旂殑鎵€鏈夎銆�(鏇村淇℃伅璇峰弬瑙�13.1.2鑺傦紝鈥淎LTER TABLE璇硶鈥� 鍜� 18.3鑺傦紝鈥滃垎鍖虹鐞嗏€�锛夈€�瀵逛簬鏈夊ぇ閲忚鐨勮〃锛岃繖姣旇繍琛屼竴涓鈥�DELETE FROM employees WHERE YEAR(separated) <= 1990锛�鈥濊繖鏍风殑涓€涓�DELETE鏌ヨ瑕佹湁鏁堝緱澶氥€�

路         鎯宠浣跨敤涓€涓寘鍚湁鏃ユ湡鎴栨椂闂村€硷紝鎴栧寘鍚湁浠庝竴浜涘叾浠栫骇鏁板紑濮嬪闀跨殑鍊肩殑鍒椼€�

路         缁忓父杩愯鐩存帴渚濊禆浜庣敤浜庡垎鍓茶〃鐨勫垪鐨勬煡璇€€備緥濡傦紝褰撴墽琛屼竴涓鈥�SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id锛�鈥濊繖鏍风殑鏌ヨ鏃讹紝MySQL鍙互寰堣繀閫熷湴纭畾鍙湁鍒嗗尯p2闇€瑕佹壂鎻忥紝杩欐槸鍥犱负浣欎笅鐨勫垎鍖轰笉鍙兘鍖呭惈鏈夌鍚堣WHERE瀛愬彞鐨勪换浣曡褰曘€�娉ㄩ噴锛氳繖绉嶄紭鍖栬繕娌℃湁鍦�MySQL 5.1婧愮▼搴忎腑鍚敤锛屼絾鏄紝鏈夊叧宸ヤ綔姝ｅ湪杩涜涓€�

MySQL涓殑LIST鍒嗗尯鍦ㄥ緢澶氭柟闈㈢被浼间簬RANGE鍒嗗尯銆傚拰鎸夌収RANGE鍒嗗尯涓€鏍凤紝姣忎釜鍒嗗尯蹇呴』鏄庣‘瀹氫箟銆傚畠浠殑涓昏鍖哄埆鍦ㄤ簬锛�LIST鍒嗗尯涓瘡涓垎鍖虹殑瀹氫箟鍜岄€夋嫨鏄熀浜庢煇鍒楃殑鍊间粠灞炰簬涓€涓€煎垪琛ㄩ泦涓殑涓€涓€硷紝鑰�RANGE鍒嗗尯鏄粠灞炰簬涓€涓繛缁尯闂村€肩殑闆嗗悎銆�LIST鍒嗗尯閫氳繃浣跨敤鈥�PARTITION BY LIST(expr)鈥�鏉ュ疄鐜帮紝鍏朵腑鈥�expr鈥� 鏄煇鍒楀€兼垨涓€涓熀浜庢煇涓垪鍊笺€佸苟杩斿洖涓€涓暣鏁板€肩殑琛ㄨ揪寮忥紝鐒跺悗閫氳繃鈥�VALUES IN (value_list)鈥濈殑鏂瑰紡鏉ュ畾涔夋瘡涓垎鍖猴紝鍏朵腑鈥�value_list鈥濇槸涓€涓€氳繃閫楀彿鍒嗛殧鐨勬暣鏁板垪琛ㄣ€�

娉ㄩ噴锛�鍦�MySQL 5.1涓紝褰撲娇鐢�LIST鍒嗗尯鏃讹紝鏈夊彲鑳藉彧鑳藉尮閰嶆暣鏁板垪琛ㄣ€�

涓嶅儚鎸夌収RANGE瀹氫箟鍒嗗尯鐨勬儏褰紝LIST鍒嗗尯涓嶅繀澹版槑浠讳綍鐗瑰畾鐨勯『搴忋€傚叧浜�LIST鍒嗗尯鏇磋缁嗙殑璇硶淇℃伅锛岃鍙傝€�13.1.5鑺傦紝鈥淐REATE TABLE璇硶鈥� 銆�

瀵逛簬涓嬮潰缁欏嚭鐨勪緥瀛愶紝鎴戜滑鍋囧畾灏嗚琚垎鍖虹殑琛ㄧ殑鍩烘湰瀹氫箟鏄€氳繃涓嬮潰鐨勨€�CREATE TABLE鈥濊鍙ユ彁渚涚殑锛�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)锛�

锛堣繖鍜�18.2.1鑺傦紝鈥淩ANGE鍒嗗尯鈥� 涓殑渚嬪瓙涓娇鐢ㄧ殑鏄悓涓€涓〃锛�銆� 

鍋囧畾鏈�20涓煶鍍忓簵锛屽垎甯冨湪4涓湁缁忛攢鏉冪殑鍦板尯锛屽涓嬭〃鎵€绀猴細

瑕佹寜鐓у睘浜庡悓涓€涓湴鍖哄晢搴楃殑琛屼繚瀛樺湪鍚屼竴涓垎鍖轰腑鐨勬柟寮忔潵鍒嗗壊琛紝鍙互浣跨敤涓嬮潰鐨勨€�CREATE TABLE鈥�璇彞锛�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY LIST(store_id)

    PARTITION pNorth VALUES IN (3,5,6,9,17),

    PARTITION pEast VALUES IN (1,2,10,11,19,20),

    PARTITION pWest VALUES IN (4,12,13,14,18),

    PARTITION pCentral VALUES IN (7,8,15,16)

)锛�

杩欎娇寰楀湪琛ㄤ腑澧炲姞鎴栧垹闄ゆ寚瀹氬湴鍖虹殑闆囧憳璁板綍鍙樺緱瀹规槗璧锋潵銆備緥濡傦紝鍋囧畾瑗垮尯鐨勬墍鏈夐煶鍍忓簵閮藉崠缁欎簡鍏朵粬鍏徃銆傞偅涔堜笌鍦ㄨタ鍖洪煶鍍忓簵宸ヤ綔闆囧憳鐩稿叧鐨勬墍鏈夎褰曪紙琛岋級鍙互浣跨敤鏌ヨ鈥�ALTER TABLE employees DROP PARTITION pWest锛�鈥濇潵杩涜鍒犻櫎锛屽畠涓庡叿鏈夊悓鏍蜂綔鐢ㄧ殑DELETE 锛堝垹闄わ級鏌ヨ鈥�DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)锛�鈥濇瘮璧锋潵锛岃鏈夋晥寰楀銆�

瑕佺偣锛氬鏋滆瘯鍥炬彃鍏ュ垪鍊硷紙鎴栧垎鍖鸿〃杈惧紡鐨勮繑鍥炲€硷級涓嶅湪鍒嗗尯鍊煎垪琛ㄤ腑鐨勪竴琛屾椂锛岄偅涔堚€�INSERT鈥�鏌ヨ灏嗗け璐ュ苟鎶ラ敊銆備緥濡傦紝鍋囧畾LIST鍒嗗尯鐨勯噰鐢ㄤ笂闈㈢殑鏂规锛屼笅闈㈢殑鏌ヨ灏嗗け璐ワ細

INSERT INTO employees VALUES 

    (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

杩欐槸鍥犱负鈥�store_id鈥�鍒楀€�21涓嶈兘鍦ㄧ敤浜庡畾涔夊垎鍖�pNorth, pEast, pWest,鎴�pCentral鐨勫€煎垪琛ㄤ腑鎵惧埌銆傝閲嶇偣娉ㄦ剰鐨勬槸锛�LIST鍒嗗尯娌℃湁绫讳技濡�鈥淰ALUES LESS THAN MAXVALUE鈥�杩欐牱鐨勫寘鍚叾浠栧€煎湪鍐呯殑瀹氫箟銆傚皢瑕佸尮閰嶇殑浠讳綍鍊奸兘蹇呴』鍦ㄥ€煎垪琛ㄤ腑鎵惧埌銆�

LIST鍒嗗尯闄や簡鑳藉拰RANGE鍒嗗尯缁撳悎璧锋潵鐢熸垚涓€涓鍚堢殑瀛愬垎鍖猴紝涓�HASH鍜�KEY鍒嗗尯缁撳悎璧锋潵鐢熸垚澶嶅悎鐨勫瓙鍒嗗尯涔熸槸鍙兘鐨勩€� 鍏充簬杩欐柟闈㈢殑璁ㄨ锛岃鍙傝€�18.2.5鑺傦紝鈥滃瓙鍒嗗尯鈥�銆�

HASH鍒嗗尯涓昏鐢ㄦ潵纭繚鏁版嵁鍦ㄩ鍏堢‘瀹氭暟鐩殑鍒嗗尯涓钩鍧囧垎甯冦€傚湪RANGE鍜�LIST鍒嗗尯涓紝蹇呴』鏄庣‘鎸囧畾涓€涓粰瀹氱殑鍒楀€兼垨鍒楀€奸泦鍚堝簲璇ヤ繚瀛樺湪鍝釜鍒嗗尯涓紱鑰屽湪HASH鍒嗗尯涓紝MySQL 鑷姩瀹屾垚杩欎簺宸ヤ綔锛屼綘鎵€瑕佸仛鐨勫彧鏄熀浜庡皢瑕佽鍝堝笇鐨勫垪鍊兼寚瀹氫竴涓垪鍊兼垨琛ㄨ揪寮忥紝浠ュ強鎸囧畾琚垎鍖虹殑琛ㄥ皢瑕佽鍒嗗壊鎴愮殑鍒嗗尯鏁伴噺銆�

瑕佷娇鐢�HASH鍒嗗尯鏉ュ垎鍓蹭竴涓〃锛岃鍦�CREATE TABLE 璇彞涓婃坊鍔犱竴涓€�PARTITION BY HASH (expr)鈥濆瓙鍙ワ紝鍏朵腑鈥�expr鈥濇槸涓€涓繑鍥炰竴涓暣鏁扮殑琛ㄨ揪寮忋€傚畠鍙互浠呬粎鏄瓧娈电被鍨嬩负MySQL 鏁村瀷鐨勪竴鍒楃殑鍚嶅瓧銆傛澶栵紝浣犲緢鍙兘闇€瑕佸湪鍚庨潰鍐嶆坊鍔犱竴涓€�PARTITIONS num鈥濆瓙鍙ワ紝鍏朵腑num 鏄竴涓潪璐熺殑鏁存暟锛屽畠琛ㄧず琛ㄥ皢瑕佽鍒嗗壊鎴愬垎鍖虹殑鏁伴噺銆�

渚嬪锛屼笅闈㈢殑璇彞鍒涘缓浜嗕竴涓娇鐢ㄥ熀浜庘€�store_id鈥�鍒楄繘琛� 鍝堝笇澶勭悊鐨勮〃锛岃琛ㄨ鍒嗘垚浜�4涓垎鍖猴細

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY HASH(store_id)

PARTITIONS 4锛�

濡傛灉娌℃湁鍖呮嫭涓€涓�PARTITIONS瀛愬彞锛岄偅涔堝垎鍖虹殑鏁伴噺灏嗛粯璁や负1銆� 渚嬪锛� 瀵逛簬NDB Cluster锛堢皣锛�琛紝榛樿鐨勫垎鍖烘暟閲忓皢涓�绨囨暟鎹妭鐐圭殑鏁伴噺鐩稿悓锛岃繖绉嶄慨姝ｅ彲鑳芥槸鑰冭檻浠讳綍MAX_ROWS 璁剧疆锛屼互渚跨‘淇濇墍鏈夌殑琛岄兘鑳藉悎閫傚湴鎻掑叆鍒板垎鍖轰腑銆傦紙鍙傝绗�17绔狅細MySQL绨�锛�銆�

濡傛灉鍦ㄥ叧閿瓧鈥�PARTITIONS鈥�鍚庨潰娌℃湁鍔犱笂鍒嗗尯鐨勬暟閲忥紝灏嗕細鍑虹幇璇硶閿欒銆�

鈥�expr鈥濊繕鍙互鏄竴涓繑鍥炰竴涓暣鏁扮殑SQL琛ㄨ揪寮忋€備緥濡傦紝涔熻浣犳兂鍩轰簬闆囩敤闆囧憳鐨勫勾浠芥潵杩涜鍒嗗尯銆傝繖鍙互閫氳繃涓嬮潰鐨勮鍙ユ潵瀹炵幇锛�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY HASH(YEAR(hired))

PARTITIONS 4锛�

鈥�expr鈥濊繕鍙互鏄�MySQL 涓湁鏁堢殑浠讳綍鍑芥暟鎴栧叾浠栬〃杈惧紡锛屽彧瑕佸畠浠繑鍥炰竴涓棦闈炲父鏁般€佷篃闈為殢鏈烘暟鐨勬暣鏁般€傦紙鎹㈠彞璇濊锛屽畠鏃㈡槸鍙樺寲鐨勪絾鍙堟槸纭畾鐨勶級銆備絾鏄簲褰撹浣忥紝姣忓綋鎻掑叆鎴栨洿鏂帮紙鎴栬€呭彲鑳藉垹闄わ級涓€琛岋紝杩欎釜琛ㄨ揪寮忛兘瑕佽绠椾竴娆★紱杩欐剰鍛崇潃闈炲父澶嶆潅鐨勮〃杈惧紡鍙兘浼氬紩璧锋€ц兘闂锛屽挨鍏舵槸鍦ㄦ墽琛屽悓鏃跺奖鍝嶅ぇ閲忚鐨勮繍绠楋紙渚嬪鎵归噺鎻掑叆锛夌殑鏃跺€欍€�

鏈€鏈夋晥鐜囩殑鍝堝笇鍑芥暟鏄彧瀵瑰崟涓〃鍒楄繘琛岃绠楋紝骞朵笖瀹冪殑鍊奸殢鍒楀€艰繘琛屼竴鑷村湴澧炲ぇ鎴栧噺灏忥紝鍥犱负杩欒€冭檻浜嗗湪鍒嗗尯鑼冨洿涓婄殑鈥滀慨鍓€濄€備篃灏辨槸璇达紝琛ㄨ揪寮忓€煎拰瀹冩墍鍩轰簬鐨勫垪鐨勫€煎彉鍖栬秺鎺ヨ繎锛�MySQL灏卞彲浠ヨ秺鏈夋晥鍦颁娇鐢ㄨ琛ㄨ揪寮忔潵杩涜HASH鍒嗗尯銆�

渚嬪锛屸€渄ate_col鈥� 鏄竴涓狣ATE锛堟棩鏈燂級绫诲瀷鐨勫垪锛岄偅涔堣〃杈惧紡TO_DAYS(date_col)灏卞彲浠ヨ鏄殢鍒椻€渄ate_col鈥濆€肩殑鍙樺寲鑰屽彂鐢熺洿鎺ョ殑鍙樺寲锛屽洜涓哄垪鈥渄ate_col鈥濆€肩殑姣忎釜鍙樺寲锛岃〃杈惧紡鐨勫€间篃灏嗗彂鐢熶笌涔嬩竴鑷寸殑鍙樺寲銆傝€岃〃杈惧紡YEAR(date_col)鐨勫彉鍖栧氨娌℃湁琛ㄨ揪寮廡O_DAYS(date_col)閭ｄ箞鐩存帴锛屽洜涓轰笉鏄垪鈥渄ate_col鈥濇瘡娆″彲鑳界殑鏀瑰彉閮借兘浣胯〃杈惧紡YEAR(date_col)鍙戠敓鍚岀瓑鐨勬敼鍙樸€傚嵆渚垮姝わ紝琛ㄨ揪寮廦EAR(date_col)涔熻繕鏄竴涓敤浜� 鍝堝笇鍑芥暟鐨勩€佸ソ鐨勫€欓€夎〃杈惧紡锛屽洜涓哄畠闅忓垪date_col鐨勪竴閮ㄥ垎鍙戠敓鐩存帴鍙樺寲锛屽苟涓斿垪date_col鐨勫彉鍖栦笉鍙兘寮曡捣琛ㄨ揪寮廦EAR(date_col)涓嶆垚姣斾緥鐨勫彉鍖栥€�

浣滀负瀵圭収锛屽亣瀹氭湁涓€涓被鍨嬩负鏁村瀷锛�INT锛夌殑銆佸垪鍚嶄负鈥渋nt_col鈥濈殑鍒椼€傜幇鍦ㄨ€冭檻琛ㄨ揪寮忊€淧OW(5-int_col,3) + 6鈥濄€傝繖瀵逛簬鍝堝笇鍑芥暟灏辨槸涓€涓笉濂界殑閫夋嫨锛屽洜涓衡€渋nt_col鈥濆€肩殑鍙樺寲骞朵笉鑳戒繚璇佽〃杈惧紡浜х敓鎴愭瘮渚嬬殑鍙樺寲銆傚垪 鈥渋nt_col鈥濈殑鍊煎彂鐢熶竴涓粰瀹氭暟鐩殑鍙樺寲锛屽彲鑳戒細寮曡捣琛ㄨ揪寮忕殑鍊间骇鐢熶竴涓緢澶т笉鍚岀殑鍙樺寲銆備緥濡傦紝鎶婂垪鈥渋nt_col鈥濈殑鍊间粠5鍙樹负6锛岃〃杈惧紡鐨勫€煎皢浜х敓鈥滐紞1鈥濈殑鏀瑰彉锛屼絾鏄妸鍒椻€渋nt_col鈥濈殑鍊间粠6鍙樹负7鏃讹紝琛ㄨ揪寮忕殑鍊煎皢浜х敓鈥滐紞7鈥濈殑鍙樺寲銆�

鎹㈠彞璇濊锛屽鏋滃垪鍊间笌琛ㄨ揪寮忓€间箣姣旂殑鏇茬嚎鍥捐秺鎺ヨ繎鐢辩瓑寮忊€�y=nx锛堝叾涓�n涓洪潪闆剁殑甯告暟锛夋弿缁樺嚭鐨勭洿绾匡紝鍒欒琛ㄨ揪寮忚秺閫傚悎浜� 鍝堝笇銆傝繖鏄洜涓猴紝琛ㄨ揪寮忕殑闈炵嚎鎬ц秺涓ラ噸锛屽垎鍖轰腑鏁版嵁浜х敓闈炲潎琛″垎甯冪殑瓒嬪娍涔熷皢瓒婁弗閲嶃€�

鐞嗚涓婅锛屽浜庢秹鍙婂埌澶氬垪鐨勮〃杈惧紡锛屸€滀慨鍓紙pruning锛夆€濅篃鏄彲鑳界殑锛屼絾鏄纭畾鍝簺閫備簬 鍝堝笇鏄潪甯稿洶闅惧拰鑰楁椂鐨勩€傚熀浜庤繖涓師鍥狅紝瀹為檯涓婁笉鎺ㄨ崘浣跨敤娑夊強鍒板鍒楃殑鍝堝笇琛ㄨ揪寮忋€�

褰撲娇鐢ㄤ簡鈥�PARTITION BY HASH鈥�鏃讹紝MySQL灏嗗熀浜庣敤鎴峰嚱鏁扮粨鏋滅殑妯℃暟鏉ョ‘瀹氫娇鐢ㄥ摢涓紪鍙风殑鍒嗗尯銆傛崲鍙ヨ瘽锛屽浜庝竴涓〃杈惧紡鈥�expr鈥濓紝灏嗚淇濆瓨璁板綍鐨勫垎鍖虹紪鍙蜂负N 锛屽叾涓€�N = MOD(expr, num)鈥�銆備緥濡傦紝鍋囧畾琛�t1 瀹氫箟濡備笅锛屽畠鏈�4涓垎鍖猴細

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

    PARTITION BY HASH( YEAR(col3) )

    PARTITIONS 4锛�

濡傛灉鎻掑叆涓€涓�col3鍒�鍊间负'2005-09-15'鐨勮褰曞埌琛�t1涓紝閭ｄ箞淇濆瓨璇ユ潯璁板綍鐨勫垎鍖虹‘瀹氬涓嬶細

MOD(YEAR('2005-09-01'),4)

=  MOD(2005,4)

=  1

MySQL 5.1 杩樻敮鎸佷竴涓绉颁负鈥�linear hashing锛堢嚎鎬у搱甯屽姛鑳斤級鈥濈殑鍙橀噺锛屽畠浣跨敤涓€涓洿鍔犲鏉傜殑绠楁硶鏉ョ‘瀹氭柊琛屾彃鍏ュ埌宸茬粡鍒嗗尯浜嗙殑琛ㄤ腑鐨勪綅缃€傚叧浜庤繖绉嶇畻娉曠殑鎻忚堪锛岃鍙傝18.2.3.1鑺傦紝鈥淟INEAR HASH鍒嗗尯鈥� 銆�

姣忓綋鎻掑叆鎴栨洿鏂颁竴鏉¤褰曪紝鐢ㄦ埛鍑芥暟閮借璁＄畻涓€娆°€傚綋鍒犻櫎璁板綍鏃讹紝鐢ㄦ埛鍑芥暟涔熷彲鑳借杩涜璁＄畻锛岃繖鍙栧喅浜庢墍澶勭殑鐜銆�

娉ㄩ噴锛�濡傛灉灏嗚鍒嗗尯鐨勮〃鏈変竴涓敮涓€鐨勯敭锛岄偅涔堢敤鏉ヤ綔涓�HASH鐢ㄦ埛鍑芥暟鐨勮嚜鍙樻暟鎴栬€呬富閿殑column_list鐨勮嚜鍙樻暟鐨勪换鎰忓垪閮藉繀椤绘槸閭ｄ釜閿殑涓€閮ㄥ垎銆�

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY LINEAR HASH(YEAR(hired))

PARTITIONS 4锛�

2.           V = POWER(2, CEILING(LOG(2, num)))

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

    PARTITION BY LINEAR HASH( YEAR(col3) )

    PARTITIONS 6;

V = POWER(2, CEILING(LOG(2,7))) = 8

N = YEAR('2003-04-14') & (8 - 1)

   = 2003 & 7

   = 3

(3 >= 6 涓哄亣锛�FALSE锛�: 璁板綍灏嗚淇濆瓨鍒�#3鍙峰垎鍖轰腑)

V = 8

N = YEAR('1998-10-19') & (8-1)

  = 1998 & 7

  = 6

(6 >= 4 涓虹湡锛�TRUE锛�: 杩橀渶瑕侀檮鍔犵殑姝ラ)

N = 6 & CEILING(5 / 2)

  = 6 & 3

  = 2

(2 >= 4 涓哄亣锛�FALSE锛�: 璁板綍灏嗚淇濆瓨鍒�#2鍒嗗尯涓�)

鎸夌収KEY杩涜鍒嗗尯绫讳技浜庢寜鐓�HASH鍒嗗尯锛岄櫎浜�HASH鍒嗗尯浣跨敤鐨勭敤鎴峰畾涔夌殑琛ㄨ揪寮忥紝鑰�KEY鍒嗗尯鐨� 鍝堝笇鍑芥暟鏄敱MySQL 鏈嶅姟鍣ㄦ彁渚涖€�MySQL 绨囷紙Cluster锛変娇鐢ㄥ嚱鏁�MD5()鏉ュ疄鐜�KEY鍒嗗尯锛涘浜庝娇鐢ㄥ叾浠栧瓨鍌ㄥ紩鎿庣殑琛紝鏈嶅姟鍣ㄤ娇鐢ㄥ叾鑷繁鍐呴儴鐨� 鍝堝笇鍑芥暟锛岃繖浜涘嚱鏁版槸鍩轰簬涓�PASSWORD()涓€鏍风殑杩愮畻娉曞垯銆�

鈥�CREATE TABLE ... PARTITION BY KEY鈥�鐨勮娉曡鍒欑被浼间簬鍒涘缓涓€涓€氳繃HASH鍒嗗尯鐨勮〃鐨勮鍒欍€傚畠浠敮涓€鐨勫尯鍒湪浜庝娇鐢ㄧ殑鍏抽敭瀛楁槸KEY鑰屼笉鏄�HASH锛屽苟涓�KEY鍒嗗尯鍙噰鐢ㄤ竴涓垨澶氫釜鍒楀悕鐨勪竴涓垪琛ㄣ€�

閫氳繃绾挎€�KEY鍒嗗壊涓€涓〃涔熸槸鍙兘鐨勩€備笅闈㈡槸涓€涓畝鍗曠殑渚嬪瓙锛�

CREATE TABLE tk (

    col1 INT NOT NULL,

    col2 CHAR(5),

    col3 DATE

) 

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

鍦�KEY鍒嗗尯涓娇鐢ㄥ叧閿瓧LINEAR鍜屽湪HASH鍒嗗尯涓娇鐢ㄥ叿鏈夊悓鏍风殑浣滅敤锛屽垎鍖虹殑缂栧彿鏄€氳繃2鐨勫箓锛�powers-of-two锛夌畻娉曞緱鍒帮紝鑰屼笉鏄€氳繃妯℃暟绠楁硶銆傚叧浜庤绠楁硶鍙婂叾钑存兜寮忕殑鎻忚堪璇峰弬鑰� 18.2.3.1鑺傦紝鈥淟INEAR HASH鍒嗗尯鈥� 銆�

瀛愬垎鍖烘槸鍒嗗尯琛ㄤ腑姣忎釜鍒嗗尯鐨勫啀娆″垎鍓层€備緥濡傦紝鑰冭檻涓嬮潰鐨�CREATE TABLE 璇彞锛�

CREATE TABLE ts (id INT, purchased DATE)

    PARTITION BY RANGE(YEAR(purchased))

    SUBPARTITION BY HASH(TO_DAYS(purchased))

    SUBPARTITIONS 2

    (

        PARTITION p0 VALUES LESS THAN (1990),

        PARTITION p1 VALUES LESS THAN (2000),

        PARTITION p2 VALUES LESS THAN MAXVALUE

    )锛�

琛�ts 鏈�3涓�RANGE鍒嗗尯銆傝繖3涓垎鍖轰腑鐨勬瘡涓€涓垎鍖�鈥斺€�p0, p1, 鍜� p2 鈥斺€斿張琚繘涓€姝ュ垎鎴愪簡2涓瓙鍒嗗尯銆傚疄闄呬笂锛屾暣涓〃琚垎鎴愪簡3 * 2 = 6涓垎鍖恒€備絾鏄紝鐢变簬PARTITION BY RANGE瀛愬彞鐨勪綔鐢紝杩欎簺鍒嗗尯鐨勫ご2涓彧淇濆瓨鈥減urchased鈥�鍒椾腑鍊煎皬浜�1990鐨勯偅浜涜褰曘€�

鍦�MySQL 5.1涓紝瀵逛簬宸茬粡閫氳繃RANGE鎴�LIST鍒嗗尯浜嗙殑琛ㄥ啀杩涜瀛愬垎鍖烘槸鍙兘鐨勩€傚瓙鍒嗗尯鏃㈠彲浠ヤ娇鐢�HASH甯屽垎鍖猴紝涔熷彲浠ヤ娇鐢�KEY鍒嗗尯銆傝繖涔熻绉颁负澶嶅悎鍒嗗尯锛�composite partitioning锛夈€�

涓轰簡瀵逛釜鍒殑瀛愬垎鍖烘寚瀹氶€夐」锛屼娇鐢�SUBPARTITION 瀛愬彞鏉ユ槑纭畾涔夊瓙鍒嗗尯涔熸槸鍙兘鐨勩€備緥濡傦紝鍒涘缓鍦ㄥ墠闈緥瀛愪腑缁欏嚭鐨勫悓涓€涓〃鐨勩€佷竴涓洿鍔犺缁嗙殑鏂瑰紡濡備笅锛�

CREATE TABLE ts (id INT, purchased DATE)

    PARTITION BY RANGE(YEAR(purchased))

    SUBPARTITION BY HASH(TO_DAYS(purchased))

    (

        PARTITION p0 VALUES LESS THAN (1990)

        (

            SUBPARTITION s0,

            SUBPARTITION s1

        ),

        PARTITION p1 VALUES LESS THAN (2000)

        (

            SUBPARTITION s2,

            SUBPARTITION s3

        ),

        PARTITION p2 VALUES LESS THAN MAXVALUE

        (

            SUBPARTITION s4,

            SUBPARTITION s5

        )

    );

鍑犵偣瑕佹敞鎰忕殑璇硶椤癸細

路         姣忎釜鍒嗗尯蹇呴』鏈夌浉鍚屾暟閲忕殑瀛愬垎鍖恒€�

路         濡傛灉鍦ㄤ竴涓垎鍖鸿〃涓婄殑浠讳綍鍒嗗尯涓婁娇鐢�SUBPARTITION 鏉ユ槑纭畾涔変换浣曞瓙鍒嗗尯锛岄偅涔堝氨蹇呴』瀹氫箟鎵€鏈夌殑瀛愬垎鍖恒€傛崲鍙ヨ瘽璇达紝涓嬮潰鐨勮鍙ュ皢鎵ц澶辫触锛�

路                CREATE TABLE ts (id INT, purchased DATE)

路                    PARTITION BY RANGE(YEAR(purchased))

路                    SUBPARTITION BY HASH(TO_DAYS(purchased))

路                    (

路                        PARTITION p0 VALUES LESS THAN (1990)

路                        (

路                            SUBPARTITION s0,

路                            SUBPARTITION s1

路                        ),

路                        PARTITION p1 VALUES LESS THAN (2000),

路                        PARTITION p2 VALUES LESS THAN MAXVALUE

路                        (

路                            SUBPARTITION s2,

路                            SUBPARTITION s3

路                        )

路                    )锛�

鍗充究杩欎釜璇彞鍖呭惈浜嗕竴涓�SUBPARTITIONS 2瀛愬彞锛屼絾鏄畠浠嶇劧浼氭墽琛屽け璐ャ€�

路         姣忎釜SUBPARTITION 瀛愬彞蹇呴』鍖呮嫭 (鑷冲皯)瀛愬垎鍖虹殑涓€涓悕瀛椼€傚惁鍒欙紝浣犲彲鑳借瀵硅瀛愬垎鍖鸿缃换浣曚綘鎵€闇€瑕佺殑閫夐」锛屾垨鑰呭厑璁歌瀛愬垎鍖哄閭ｄ簺閫夐」閲囩敤鍏堕粯璁ょ殑璁剧疆銆�

路         鍦ㄦ瘡涓垎鍖哄唴锛屽瓙鍒嗗尯鐨勫悕瀛楀繀椤绘槸鍞竴鐨勶紝浣嗘槸鍦ㄦ暣涓〃涓紝娌℃湁蹇呰淇濇寔鍞竴銆備緥濡傦紝涓嬮潰鐨�CREATE TABLE 璇彞鏄湁鏁堢殑锛�

路                CREATE TABLE ts (id INT, purchased DATE)

路                    PARTITION BY RANGE(YEAR(purchased))

路                    SUBPARTITION BY HASH(TO_DAYS(purchased))

路                    (

路                        PARTITION p0 VALUES LESS THAN (1990)

路                        (

路                            SUBPARTITION s0,

路                            SUBPARTITION s1

路                        ),

路                        PARTITION p1 VALUES LESS THAN (2000)

路                        (

路                            SUBPARTITION s0,

路                            SUBPARTITION s1

路                        ),

路                        PARTITION p2 VALUES LESS THAN MAXVALUE

路                        (

路                            SUBPARTITION s0,

路                            SUBPARTITION s1

路                        )

路                    )锛�

瀛愬垎鍖哄彲浠ョ敤浜庣壒鍒ぇ鐨勮〃锛屽湪澶氫釜纾佺洏闂村垎閰嶆暟鎹拰绱㈠紩銆傚亣璁炬湁6涓鐩橈紝鍒嗗埆涓�/disk0锛� /disk1锛� /disk2绛夈€傜幇鍦ㄨ€冭檻涓嬮潰鐨勪緥瀛愶細

CREATE TABLE ts (id INT, purchased DATE)

    PARTITION BY RANGE(YEAR(purchased))

    SUBPARTITION BY HASH(TO_DAYS(purchased))

    (

        PARTITION p0 VALUES LESS THAN (1990)

        (

            SUBPARTITION s0 

                DATA DIRECTORY = '/disk0/data' 

                INDEX DIRECTORY = '/disk0/idx',

            SUBPARTITION s1 

                DATA DIRECTORY = '/disk1/data' 

                INDEX DIRECTORY = '/disk1/idx'

        ),

        PARTITION p1 VALUES LESS THAN (2000)

        (

            SUBPARTITION s0 

                DATA DIRECTORY = '/disk2/data' 

                INDEX DIRECTORY = '/disk2/idx',

            SUBPARTITION s1 

                DATA DIRECTORY = '/disk3/data' 

                INDEX DIRECTORY = '/disk3/idx'

        ),

        PARTITION p2 VALUES LESS THAN MAXVALUE

        (

            SUBPARTITION s0 

                DATA DIRECTORY = '/disk4/data' 

                INDEX DIRECTORY = '/disk4/idx',

            SUBPARTITION s1 

                DATA DIRECTORY = '/disk5/data' 

                INDEX DIRECTORY = '/disk5/idx'

        )

    )锛�

鍦ㄨ繖涓緥瀛愪腑锛屾瘡涓�RANGE鍒嗗尯鐨勬暟鎹拰绱㈠紩閮戒娇鐢ㄤ竴涓崟鐙殑纾佺洏銆傝繕鍙兘鏈夎澶氬叾浠栫殑鍙樺寲锛涗笅闈㈡槸鍙﹀涓€涓彲鑳界殑渚嬪瓙锛�

CREATE TABLE ts (id INT, purchased DATE)

    PARTITION BY RANGE(YEAR(purchased))

    SUBPARTITION BY HASH(TO_DAYS(purchased))

    (

        PARTITION p0 VALUES LESS THAN (1990)

        (

            SUBPARTITION s0a 

                DATA DIRECTORY = '/disk0' 

                INDEX DIRECTORY = '/disk1',

            SUBPARTITION s0b 

                DATA DIRECTORY = '/disk2' 

                INDEX DIRECTORY = '/disk3'

        ),

        PARTITION p1 VALUES LESS THAN (2000)

        (

            SUBPARTITION s1a 

                DATA DIRECTORY = '/disk4/data' 

                INDEX DIRECTORY = '/disk4/idx',

            SUBPARTITION s1b 

                DATA DIRECTORY = '/disk5/data' 

                INDEX DIRECTORY = '/disk5/idx'

        ),

        PARTITION p2 VALUES LESS THAN MAXVALUE

        (

            SUBPARTITION s2a,

            SUBPARTITION s2b

        )

    )锛�

鍦ㄨ繖涓緥瀛愪腑锛屽瓨鍌ㄧ殑鍒嗛厤濡備笅锛�

路         璐拱鏃ユ湡鍦�1990骞村墠鐨勮褰曞崰浜嗗ぇ閲忕殑瀛樺偍绌洪棿锛屾墍浠ユ妸瀹冨垎涓轰簡鍥涗釜閮ㄥ垎杩涜瀛樺偍锛岀粍鎴�p0鍒嗗尯鐨�涓や釜瀛愬垎鍖猴紙s0a 鍜�s0b锛夌殑鏁版嵁鍜岀储寮曢兘鍒嗗埆鐢ㄤ竴涓崟鐙殑纾佺洏杩涜瀛樺偍銆傛崲鍙ヨ瘽璇达細

o        瀛愬垎鍖�s0a 鐨勬暟鎹繚瀛樺湪纾佺洏/disk0涓€�

o        瀛愬垎鍖�s0a 鐨勭储寮曚繚瀛樺湪纾佺洏/disk1涓€�

o        瀛愬垎鍖�s0b 鐨勬暟鎹繚瀛樺湪纾佺洏/disk2涓€�

o        瀛愬垎鍖�s0b 鐨勭储寮曚繚瀛樺湪纾佺洏/disk3涓€�

路         淇濆瓨璐拱鏃ユ湡浠�1990骞村埌1999骞撮棿鐨勮褰曪紙鍒嗗尯p1锛変笉闇€瑕佷繚瀛樿喘涔版棩鏈熷湪1990骞翠箣鍓嶇殑璁板綍閭ｄ箞澶х殑瀛樺偍绌洪棿銆傝繖浜涜褰曞垎鍦�2涓鐩橈紙/disk4鍜�/disk5锛変笂淇濆瓨锛岃€屼笉鏄�4涓鐩橈細

o        灞炰簬鍒嗗尯p1鐨勭涓€涓瓙鍒嗗尯锛�s1a锛夌殑鏁版嵁鍜岀储寮曚繚瀛樺湪纾佺洏/disk4涓� 鈥� 鍏朵腑鏁版嵁淇濆瓨鍦ㄨ矾寰�/disk4/data涓嬶紝绱㈠紩淇濆瓨鍦�/disk4/idx涓嬨€�

o        灞炰簬鍒嗗尯p1鐨勭浜屼釜瀛愬垎鍖猴紙s1b锛夌殑鏁版嵁鍜岀储寮曚繚瀛樺湪纾佺洏/disk5涓� 鈥� 鍏朵腑鏁版嵁淇濆瓨鍦ㄨ矾寰�/disk5/data涓嬶紝绱㈠紩淇濆瓨鍦�/disk5/idx涓嬨€�

路         淇濆瓨璐拱鏃ユ湡浠�2000骞村埌鐜板湪鐨勮褰曪紙鍒嗗尯p2锛変笉闇€瑕佸墠闈袱涓�RANGE鍒嗗尯閭ｄ箞澶х殑绌洪棿銆傚綋鍓嶏紝鍦ㄩ粯璁ょ殑浣嶇疆鑳藉瓒冲淇濆瓨鎵€鏈夎繖浜涜褰曘€�

灏嗘潵锛屽鏋滀粠2000骞村紑濮嬪悗鍗佸勾璐拱鐨勬暟閲忓凡缁忚揪鍒颁簡榛樿鐨勪綅缃笉鑳藉鎻愪緵瓒冲鐨勪繚瀛樼┖闂存椂锛岀浉搴旂殑璁板綍锛堣锛夊彲浠ラ€氳繃浣跨敤鈥�ALTER TABLE ... REORGANIZE PARTITION鈥濊鍙ョЩ鍔ㄥ埌鍏朵粬鐨勪綅缃€傚叧浜庡浣曞疄鐜扮殑璇存槑锛岃鍙傝18.3鑺傦紝鈥滃垎鍖虹鐞嗏€� 銆�

MySQL 涓殑鍒嗗尯鍦ㄧ姝㈢┖鍊硷紙NULL锛変笂娌℃湁杩涜澶勭悊锛屾棤璁哄畠鏄竴涓垪鍊艰繕鏄竴涓敤鎴峰畾涔夎〃杈惧紡鐨勫€笺€備竴鑸€岃█锛屽湪杩欑鎯呭喌涓�MySQL 鎶�NULL瑙嗕负0銆傚鏋滀綘甯屾湜鍥為伩杩欑鍋氭硶锛屼綘搴旇鍦ㄨ璁¤〃鏃朵笉鍏佽绌哄€硷紱鏈€鍙兘鐨勬柟娉曟槸锛岄€氳繃澹版槑鍒椻€�NOT NULL鈥濇潵瀹炵幇杩欎竴鐐广€�

鍦ㄦ湰鑺備腑锛屾垜浠彁渚涗簡涓€浜涗緥瀛愶紝鏉ヨ鏄庡綋鍐冲畾涓€涓搴旇淇濆瓨鍒板摢涓垎鍖烘椂锛�MySQL 鏄浣曞鐞�NULL鍊肩殑銆�

濡傛灉鎻掑叆涓€琛屽埌鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃锛岃琛岀敤鏉ョ‘瀹氬垎鍖虹殑鍒楀€间负NULL锛屽垎鍖哄皢鎶婅NULL鍊艰涓�0銆備緥濡傦紝鑰冭檻涓嬮潰鐨勪袱涓〃锛岃〃鐨勫垱寤哄拰鎻掑叆璁板綍濡備笅锛�

mysql> CREATE TABLE tnlist (

    ->     id INT,

    ->     name VARCHAR(5)

    -> )

    -> PARTITION BY LIST(id) (

    ->     PARTITION p1 VALUES IN (0),

    ->     PARTITION p2 VALUES IN (1)

    -> );

Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE tnrange (

    ->     id INT,

    ->     name VARCHAR(5)

    -> )

    -> PARTITION BY RANGE(id) (

    ->     PARTITION p1 VALUES LESS THAN (1),

    ->     PARTITION p2 VALUES LESS THAN MAXVALUE

    -> );

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO tnlist VALUES (NULL, 'bob');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tnrange VALUES (NULL, 'jim');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnlist;

+------+------+

| id   | name |

+------+------+

| NULL | bob  |

+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM tnrange;

+------+------+

| id   | name |

+------+------+

| NULL | jim  |

+------+------+

1 row in set (0.00 sec)

鍦ㄤ袱涓〃涓紝id鍒楁病鏈夊０鏄庝负鈥�NOT NULL鈥濓紝杩欐剰鍛崇潃瀹冧滑鍏佽Null鍊笺€傚彲浠ラ€氳繃鍒犻櫎杩欎簺鍒嗗尯锛岀劧鍚庨噸鏂拌繍琛�SELECT 璇彞锛屾潵楠岃瘉杩欎簺琛岃淇濆瓨鍦ㄦ瘡涓〃鐨�p1鍒嗗尯涓細

mysql> ALTER TABLE tnlist DROP PARTITION p1;

Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE tnrange DROP PARTITION p1;

Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM tnlist;

Empty set (0.00 sec)

mysql> SELECT * FROM tnrange;

Empty set (0.00 sec)

鍦ㄦ寜HASH鍜�KEY鍒嗗尯鐨勬儏鍐典笅锛屼换浣曚骇鐢�NULL鍊肩殑琛ㄨ揪寮忛兘瑙嗗悓濂藉儚瀹冪殑杩斿洖鍊间负0銆傛垜浠彲浠ラ€氳繃鍏堝垱寤轰竴涓寜HASH鍒嗗尯鐨勮〃锛岀劧鍚庢彃鍏ヤ竴涓寘鍚湁閫傚綋鍊肩殑璁板綍锛屽啀妫€鏌ュ鏂囦欢绯荤粺鐨勪綔鐢紝鏉ラ獙璇佽繖涓€鐐广€傚亣瀹氭湁浣跨敤涓嬮潰鐨勮鍙ュ湪娴嬭瘯鏁版嵁搴撲腑鍒涘缓浜嗕竴涓〃tnhash锛�

CREATE TABLE tnhash (

    id INT,

    name VARCHAR(5)

)

PARTITION BY HASH(id)

PARTITIONS 2锛�

鍋囧Linux 涓婄殑MySQL 鐨勪竴涓�RPM瀹夎锛岃繖涓鍙ュ湪鐩綍/var/lib/mysql/test涓�鍒涘缓浜嗕袱涓�.MYD鏂囦欢锛岃繖涓や釜鏂囦欢鍙互鍦�bash shell涓煡鐪嬶紝缁撴灉濡備笅锛�

/var/lib/mysql/test> ls *.MYD -l

-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash_p0.MYD

-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD

娉ㄦ剰锛氭瘡涓枃浠剁殑澶у皬涓�0瀛楄妭銆傜幇鍦ㄥ湪琛�tnhash 涓彃鍏ヤ竴琛�id鍒楀€间负NULL鐨勮锛岀劧鍚庨獙璇佽琛屽凡缁忚鎻掑叆锛�

mysql> INSERT INTO tnhash VALUES (NULL, 'sam');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnhash;

+------+------+

| id   | name |

+------+------+

| NULL | sam  |

+------+------+

1 row in set (0.01 sec)

鍥炴兂涓€涓嬶紝瀵逛簬浠绘剰鐨勬暣鏁�N,NULL MOD N 鐨勫€兼€绘槸绛変簬NULL銆傝繖涓粨鏋滃湪纭畾姝ｇ‘鐨勫垎鍖烘柟闈㈣璁や负鏄�0銆傚洖鍒扮郴缁�shell(浠嶇劧鍋囧畾bash鐢ㄤ簬杩欎釜鐩殑) 锛岄€氳繃鍐嶆鍒楀嚭鏁版嵁鏂囦欢锛屽彲浠ョ湅鍑哄€艰鎴愬姛鍦版彃鍏ュ埌绗竴涓垎鍖猴紙榛樿鍚嶇О涓�p0锛変腑锛�

var/lib/mysql/test> ls *.MYD -l

-rw-rw----  1 mysql mysql 20 2005-11-04 18:44 tnhash_p0.MYD

-rw-rw----  1 mysql mysql  0 2005-11-04 18:41 tnhash_p1.MYD

鍙互鐪嬪嚭INSERT璇彞鍙慨鏀逛簡鏂囦欢tnhash_p0.MYD锛屽畠鍦ㄧ鐩樹笂鐨勫昂瀵稿鍔犱簡锛岃€屾病鏈夊奖鍝嶅叾浠栫殑鏂囦欢銆�

鍋囧畾鏈変笅闈㈢殑涓€涓〃锛�

CREATE TABLE tndate (

    id INT,

    dt DATE

)

PARTITION BY RANGE( YEAR(dt) ) (

    PARTITION p0 VALUES LESS THAN (1990),

    PARTITION p1 VALUES LESS THAN (2000),

    PARTITION p2 VALUES LESS THAN MAXVALUE

)锛�

鍍忓叾浠栫殑MySQL鍑芥暟涓€鏍凤紝YEAR(NULL)杩斿洖NULL鍊笺€備竴涓�dt鍒楀€间负NULL鐨勮锛屽叾鍒嗗尯琛ㄨ揪寮忕殑璁＄畻缁撴灉琚涓�0锛岃琛岃鎻掑叆鍒板垎鍖�p0涓€�

鍏充簬濡備綍娣诲姞鍜屽垹闄ゅ垎鍖虹殑澶勭悊锛�RANGE鍜�LIST鍒嗗尯闈炲父鐩镐技銆傚熀浜庤繖涓師鍥狅紝鎴戜滑鍦ㄦ湰鑺傝璁鸿繖涓ょ鍒嗗尯鐨勭鐞嗐€傚叧浜�HASH鍜�KEY鍒嗗尯绠＄悊鐨勪俊鎭紝璇峰弬瑙�18.3.2鑺傦紝鈥淗ASH鍜孠EY鍒嗗尯鐨勭鐞�鈥�銆傚垹闄や竴涓�RANGE鎴�LIST鍒嗗尯姣斿鍔犱竴涓垎鍖鸿鏇村姞绠€鍗曟槗鎳傦紝鎵€浠ユ垜浠厛璁ㄨ鍓嶈€呫€�

浠庝竴涓寜鐓�RANGE鎴�LIST鍒嗗尯鐨勮〃涓垹闄や竴涓垎鍖猴紝鍙互浣跨敤甯︿竴涓�DROP PARTITION瀛愬彞鐨�ALTER TABLE鍛戒护鏉ュ疄鐜般€傝繖閲屾湁涓€涓潪甯稿熀鏈殑渚嬪瓙锛屽亣璁惧凡缁忎娇鐢ㄤ笅闈㈢殑CREATE TABLE鍜�INSERT璇彞鍒涘缓浜嗕竴涓寜鐓�RANGE鍒嗗尯鐨勮〃锛屽苟涓斿凡缁忔彃鍏ヤ簡10鏉¤褰曪細

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)

    ->     PARTITION BY RANGE(YEAR(purchased))

    ->     (

    ->         PARTITION p0 VALUES LESS THAN (1990),

    ->         PARTITION p1 VALUES LESS THAN (1995),

    ->         PARTITION p2 VALUES LESS THAN (2000),

    ->         PARTITION p3 VALUES LESS THAN (2005)

    ->     );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES

    ->     (1, 'desk organiser', '2003-10-15'),

    ->     (2, 'CD player', '1993-11-05'),

    ->     (3, 'TV set', '1996-03-10'),

    ->     (4, 'bookcase', '1982-01-10'),

    ->     (5, 'exercise bike', '2004-05-09'),

    ->     (6, 'sofa', '1987-06-05'),

    ->     (7, 'popcorn maker', '2001-11-22'),

    ->     (8, 'aquarium', '1992-08-04'),

    ->     (9, 'study desk', '1984-09-16'),

    ->     (10, 'lava lamp', '1998-12-25');

Query OK, 10 rows affected (0.01 sec)                  

鍙互閫氳繃浣跨敤涓嬮潰鐨勫懡浠ゆ煡鐪嬮偅浜涜褰曞凡缁忔彃鍏ュ埌浜嗗垎鍖�p2涓細

mysql> SELECT * FROM tr

    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

+------+-----------+------------+

| id   | name      | purchased  |

+------+-----------+------------+

|    3 | TV set    | 1996-03-10 |

|   10 | lava lamp | 1998-12-25 |

+------+-----------+------------+

2 rows in set (0.00 sec)

瑕佸垹闄ゅ悕瀛椾负p2鐨勫垎鍖猴紝鎵ц涓嬮潰鐨勫懡浠わ細

mysql> ALTER TABLE tr DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

璁颁綇涓嬮潰涓€鐐归潪甯搁噸瑕侊細褰撳垹闄や簡涓€涓垎鍖猴紝涔熷悓鏃跺垹闄や簡璇ュ垎鍖轰腑鎵€鏈夌殑鏁版嵁銆�鍙互閫氳繃閲嶆柊杩愯鍓嶉潰鐨�SELECT鏌ヨ鏉ラ獙璇佽繖涓€鐐癸細

mysql> SELECT * FROM tr WHERE purchased 

    -> BETWEEN '1995-01-01' AND '1999-12-31';

Empty set (0.00 sec)

濡傛灉甯屾湜浠庢墍鏈夊垎鍖哄垹闄ゆ墍鏈夌殑鏁版嵁锛屼絾鏄張淇濈暀琛ㄧ殑瀹氫箟鍜岃〃鐨勫垎鍖烘ā寮忥紝浣跨敤TRUNCATE TABLE鍛戒护銆�锛�璇峰弬瑙�13.2.9鑺傦紝鈥淭RUNCATE璇硶鈥�锛�銆�

濡傛灉甯屾湜鏀瑰彉琛ㄧ殑鍒嗗尯鑰屽張涓嶄涪澶辨暟鎹紝浣跨敤鈥�ALTER TABLE ... REORGANIZE PARTITION鈥�璇彞銆傚弬瑙佷笅闈㈢殑鍐呭锛屾垨鑰呭湪13.1.2鑺傦紝鈥淎LTER TABLE璇硶鈥� 涓弬鑰冨叧浜�REORGANIZE PARTITION鐨勪俊鎭€�

濡傛灉鐜板湪鎵ц涓€涓�SHOW CREATE TABLE鍛戒护锛屽彲浠ヨ瀵熷埌琛ㄧ殑鍒嗗尯缁撴瀯鏄浣曡鏀瑰彉鐨勶細

mysql> SHOW CREATE TABLE tr\G

*************************** 1. row ***************************

       Table: tr

Create Table: CREATE TABLE `tr` (

  `id` int(11) default NULL,

  `name` varchar(50) default NULL,

  `purchased` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 

PARTITION BY RANGE (YEAR(purchased)) (

  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 

  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 

  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

)

1 row in set (0.01 sec)

濡傛灉鎻掑叆璐拱鏃ユ湡鍒楃殑鍊煎湪'1995-01-01'鍜� '2004-12-31'涔嬮棿锛堝惈锛夌殑鏂拌鍒板凡缁忎慨鏀瑰悗鐨勮〃涓椂锛岃繖浜涜灏嗚淇濆瓨鍦ㄥ垎鍖�p3涓€傚彲浠ラ€氳繃涓嬮潰鐨勬柟寮忔潵楠岃瘉杩欎竴鐐癸細

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased 

    -> BETWEEN '1995-01-01' AND '2004-12-31';

+------+----------------+------------+

| id   | name           | purchased  |

+------+----------------+------------+

|   11 | pencil holder  | 1995-07-12 |

|    1 | desk organiser | 2003-10-15 |

|    5 | exercise bike  | 2004-05-09 |

|    7 | popcorn maker  | 2001-11-22 |

+------+----------------+------------+

4 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased 

    -> BETWEEN '1995-01-01' AND '2004-12-31';

Empty set (0.00 sec)

娉ㄦ剰锛氱敱鈥�ALTER TABLE ... DROP PARTITION鈥�璇彞寮曡捣鐨勩€佷粠琛ㄤ腑鍒犻櫎鐨勮鏁板苟娌℃湁琚湇鍔″櫒鎶ュ憡鍑烘潵锛屽氨濂藉儚閫氳繃鍚岀瓑鐨�DELETE鏌ヨ鎿嶄綔涓€鏍枫€�

鍒犻櫎LIST鍒嗗尯浣跨敤鍜屽垹闄�RANGE鍒嗗尯瀹屽叏鐩稿悓鐨勨€�ALTER TABLE ... DROP PARTITION鈥�璇硶銆備絾鏄紝鍦ㄥ鍏跺悗浣跨敤杩欎釜琛ㄧ殑褰卞搷鏂归潰锛岃繕鏄湁閲嶅ぇ鐨勫尯鍒細鍦ㄨ繖涓〃涓紝鍐嶄篃涓嶈兘鎻掑叆杩欎箞涓€浜涜锛岃繖浜涜鐨勫垪鍊煎寘鍚湪瀹氫箟宸茬粡鍒犻櫎浜嗙殑鍒嗗尯鐨勫€煎垪琛ㄤ腑 (鏈夊叧绀轰緥锛岃鍙傝18.2.2鑺傦紝鈥淟IST鍒嗗尯鈥� 锛�銆�

瑕佸鍔犱竴涓柊鐨�RANGE鎴�LIST鍒嗗尯鍒颁竴涓墠闈㈠凡缁忓垎鍖轰簡鐨勮〃锛屼娇鐢ㄢ€�ALTER TABLE ... ADD PARTITION鈥�璇彞銆傚浜庝娇鐢�RANGE鍒嗗尯鐨勮〃锛屽彲浠ョ敤杩欎釜璇彞娣诲姞鏂扮殑鍖洪棿鍒板凡鏈夊垎鍖虹殑搴忓垪鐨勫墠闈㈡垨鍚庨潰銆備緥濡傦紝鍋囪鏈変竴涓寘鍚綘鎵€鍦ㄧ粍缁囩殑鍏ㄤ綋鎴愬憳鏁版嵁鐨勫垎鍖鸿〃锛岃琛ㄧ殑瀹氫箟濡備笅锛�

CREATE TABLE members (

    id INT, 

    fname VARCHAR(25),

    lname VARCHAR(25), 

    dob DATE

)

PARTITION BY RANGE(YEAR(dob)) (

    PARTITION p0 VALUES LESS THAN (1970),

    PARTITION p1 VALUES LESS THAN (1980),

    PARTITION p2 VALUES LESS THAN (1990)

);

杩涗竴姝ュ亣璁炬垚鍛樼殑鏈€灏忓勾绾槸16宀併€傞殢鐫€鏃ュ巻鎺ヨ繎2005骞村勾搴曪紝浣犱細璁よ瘑鍒颁笉涔呭皢瑕佹帴绾�1990骞达紙浠ュ強浠ュ悗骞翠唤锛夊嚭鐢熺殑鎴愬憳銆傚彲浠ユ寜鐓т笅闈㈢殑鏂瑰紡锛屼慨鏀规垚鍛樿〃鏉ュ绾冲嚭鐢熷湪1990锛�1999骞翠箣闂寸殑鎴愬憳锛�

ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

瑕佺偣锛�瀵逛簬閫氳繃RANGE鍒嗗尯鐨勮〃锛屽彧鍙互浣跨敤ADD PARTITION娣诲姞鏂扮殑鍒嗗尯鍒板垎鍖哄垪琛ㄧ殑楂樼銆傝娉曢€氳繃杩欑鏂瑰紡鍦ㄧ幇鏈夊垎鍖虹殑鍓嶉潰鎴栦箣闂村鍔犱竴涓柊鐨勫垎鍖猴紝灏嗕細瀵艰嚧涓嬮潰鐨勪竴涓敊璇細

mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));

閿欒1463 (HY000): 瀵规瘡涓垎鍖猴紝VALUES LESS THAN 鍊煎繀椤讳弗鏍煎闀�

閲囩敤涓€涓被浼肩殑鏂瑰紡锛屽彲浠ュ鍔犳柊鐨勫垎鍖哄埌宸茬粡閫氳繃LIST鍒嗗尯鐨勮〃銆備緥濡傦紝鍋囧畾鏈夊涓嬪畾涔夌殑涓€涓〃锛�

CREATE TABLE tt (

    id INT, 

    data INT

)

PARTITION BY LIST(data) (

    PARTITION p0 VALUES IN (5, 10, 15),

    PARTITION p1 VALUES IN (6, 12, 18)

)锛�

鍙互閫氳繃涓嬮潰鐨勬柟娉曟坊鍔犱竴涓柊鐨勫垎鍖猴紝鐢ㄦ潵淇濆瓨鎷ユ湁鏁版嵁鍒楀€�7锛�14鍜�21鐨勮锛�

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21))锛�

娉ㄦ剰锛氫笉鑳芥坊鍔犺繖鏍蜂竴涓柊鐨�LIST鍒嗗尯锛岃鍒嗗尯鍖呭惈鏈夊凡缁忓寘鍚湪鐜版湁鍒嗗尯鍊煎垪琛ㄤ腑鐨勪换鎰忓€笺€傚鏋滆瘯鍥捐繖鏍峰仛锛屽皢浼氬鑷撮敊璇細

mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));

閿欒1465 (HY000): 鍦�LIST鍒嗗尯涓紝鍚屼竴涓父鏁扮殑澶氭瀹氫箟

鍥犱负甯︽湁鏁版嵁鍒楀€�12鐨勪换浣曡閮藉凡缁忓垎閰嶇粰浜嗗垎鍖�p1锛屾墍浠ヤ笉鑳藉湪琛�tt涓婂啀鍒涘缓涓€涓叾鍊煎垪琛ㄥ寘鎷�12鐨勬柊鍒嗗尯銆備负浜嗗疄鐜拌繖涓€鐐癸紝鍙互鍏堝垹闄ゅ垎鍖�p1锛屾坊鍔犲垎鍖�np锛岀劧鍚庝娇鐢ㄤ慨姝ｅ悗鐨勫畾涔夋坊鍔犱竴涓柊鐨勫垎鍖�p1銆備絾鏄紝姝ｅ鎴戜滑鍓嶉潰璁ㄨ杩囩殑锛岃繖灏嗗鑷翠繚瀛樺湪鍒嗗尯p1涓殑鎵€鏈夋暟鎹涪澶扁€斺€旇€岃繖寰€寰€骞朵笉鏄綘鎵€鐪熸鎯宠鍋氱殑銆傚彟澶栦竴绉嶈В鍐虫柟娉曞彲鑳芥槸锛屽缓绔嬩竴涓甫鏈夋柊鍒嗗尯鐨勮〃鐨勫壇鏈紝鐒跺悗浣跨敤鈥�CREATE TABLE ... SELECT ...鈥濇妸鏁版嵁鎷疯礉鍒拌鏂拌〃涓紝鐒跺悗鍒犻櫎鏃ц〃锛岄噸鏂板懡鍚嶆柊琛紝浣嗘槸锛屽綋闇€瑕佸鐞嗗ぇ閲忕殑鏁版嵁鏃讹紝杩欏彲鑳芥槸闈炲父鑰楁椂鐨勩€傚湪闇€瑕侀珮鍙敤鎬х殑鍦哄悎锛岃繖涔熷彲鑳芥槸涓嶅彲琛岀殑銆�

骞歌繍鍦版槸锛�MySQL 鐨勫垎鍖哄疄鐜版彁渚涗簡鍦ㄤ笉涓㈠け鏁版嵁鐨勬潯浠朵笅閲嶆柊瀹氫箟鍒嗗尯鐨勬柟寮忋€傝鎴戜滑棣栧厛鐪嬩袱涓秹鍙婂埌RANGE鍒嗗尯鐨勭畝鍗曚緥瀛愩€傚洖鎯充竴涓嬬幇鍦ㄥ畾涔夊涓嬬殑鎴愬憳琛細

mysql> SHOW CREATE TABLE members\G

*************************** 1. row ***************************

       Table: members

Create Table: CREATE TABLE `members` (

  `id` int(11) default NULL,

  `fname` varchar(25) default NULL,

  `lname` varchar(25) default NULL,

  `dob` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 

PARTITION BY RANGE (YEAR(dob)) (

  PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM, 

  PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM, 

  PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.

  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM

)

鍋囧畾鎯宠鎶婅〃绀哄嚭鐢熷湪1960骞村墠鎴愬憳鐨勬墍鏈夎绉诲叆鍒颁竴涓垎寮€鐨勫垎鍖轰腑銆傛濡傛垜浠墠闈㈢湅鍒扮殑锛屼笉鑳介€氳繃浣跨敤鈥�ALTER TABLE ... ADD PARTITION鈥濇潵瀹炵幇杩欎竴鐐广€備絾鏄紝瑕佸疄鐜拌繖涓€鐐癸紝鍙互浣跨敤ALTER TABLE涓婄殑鍙﹀涓€涓笌鍒嗗尯鏈夊叧鐨勬墿灞曪紝鍏蜂綋瀹炵幇濡備笅锛�

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (1960),

    PARTITION s1 VALUES LESS THAN (1970)

)锛�

瀹為檯涓婏紝杩欎釜鍛戒护鎶婂垎鍖�p0鍒嗘垚浜嗕袱涓柊鐨勫垎鍖�s0鍜�s1銆傚悓鏃讹紝瀹冭繕鏍规嵁鍖呭惈鍦ㄤ袱涓€�PARTITION ... VALUES ...鈥濆瓙鍙ヤ腑鐨勮鍒欙紝鎶婁繚瀛樺湪鍒嗗尯p0涓殑鏁版嵁绉诲叆鍒颁袱涓柊鐨勫垎鍖轰腑锛屾墍浠ュ垎鍖�s0涓彧鍖呭惈YEAR(dob)灏忎簬1960鐨勯偅浜涜锛�s1涓寘鍚偅浜�YEAR(dob)澶т簬鎴栫瓑浜�1960浣嗘槸灏忎簬1970鐨勮銆�

涓€涓�REORGANIZE PARTITION璇彞涔熷彲浠ョ敤鏉ュ悎骞剁浉閭荤殑鍒嗗尯銆傚彲浠ヤ娇鐢ㄥ涓嬬殑璇彞鎭㈠鎴愬憳琛ㄥ埌瀹冧互鍓嶇殑鍒嗗尯锛�

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (

    PARTITION p0 VALUES LESS THAN (1970)

)锛�

浣跨敤鈥�REORGANIZE PARTITION鈥濇媶鍒嗘垨鍚堝苟鍒嗗尯锛屾病鏈夋暟鎹涪澶便€傚湪鎵ц涓婇潰鐨勮鍙ヤ腑锛�MySQL 鎶婁繚瀛樺湪鍒嗗尯s0鍜�s1涓殑鎵€鏈夋暟鎹兘绉诲埌鍒嗗尯p0涓€�

鈥淩EORGANIZE PARTITION鈥�鐨勫熀鏈娉曟槸锛�

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)锛�

鍏朵腑锛�tbl_name 鏄垎鍖鸿〃鐨勫悕绉帮紝partition_list 鏄€氳繃閫楀彿鍒嗗紑鐨勩€佷竴涓垨澶氫釜灏嗚琚敼鍙樼殑鐜版湁鍒嗗尯鐨勫垪琛ㄣ€�partition_definitions 鏄竴涓槸閫氳繃閫楀彿鍒嗗紑鐨勩€佹柊鍒嗗尯瀹氫箟鐨勫垪琛紝瀹冮伒寰笌鐢ㄥ湪鈥�CREATE TABLE鈥�涓殑partition_definitions 鐩稿悓鐨勮鍒� (璇峰弬瑙�13.1.5鑺傦紝鈥淐REATE TABLE璇硶鈥�)銆傚簲褰撴敞鎰忓埌锛屽湪鎶婂灏戜釜鍒嗗尯鍚堝苟鍒颁竴涓垎鍖烘垨鎶婁竴涓垎鍖烘媶鍒嗘垚澶氬皯涓垎鍖烘柟闈紝娌℃湁闄愬埗銆備緥濡傦紝鍙互閲嶆柊缁勭粐鎴愬憳琛ㄧ殑鍥涗釜鍒嗗尯鎴愪袱涓垎鍖猴紝鍏蜂綋瀹炵幇濡備笅锛�

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (

    PARTITION m0 VALUES LESS THAN (1980),

    PARTITION m1 VALUES LESS THAN (2000)

)锛�

鍚屾牱锛屽浜庢寜LIST鍒嗗尯鐨勮〃锛屼篃鍙互浣跨敤REORGANIZE PARTITION銆傝鎴戜滑鍥炲埌閭ｄ釜闂锛屽嵆澧炲姞涓€涓柊鐨勫垎鍖哄埌宸茬粡鎸夌収LIST鍒嗗尯鐨勮〃tt涓紝浣嗘槸鍥犱负璇ユ柊鍒嗗尯鏈変竴涓€煎凡缁忓瓨鍦ㄤ簬鐜版湁鍒嗗尯鐨勫€煎垪琛ㄤ腑锛屾坊鍔犳柊鐨勫垎鍖哄け璐ャ€傛垜浠彲浠ラ€氳繃鍏�娣诲姞鍙寘鍚潪鍐茬獊鍊肩殑鍒嗗尯锛岀劧鍚庨噸鏂扮粍缁囪鏂板垎鍖哄拰鐜版湁鐨勯偅涓垎鍖猴紝浠ヤ究淇濆瓨鍦ㄧ幇鏈夌殑閭ｄ釜鍒嗗尯涓殑鍊肩幇鍦ㄧЩ鍒颁簡鏂扮殑鍒嗗尯涓紝鏉ュ鐞嗚繖涓棶棰橈細

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));

ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (

    PARTITION p1 VALUES IN (6, 18),

    PARTITION np VALUES in (4, 8, 12)

)锛�

褰撲娇鐢ㄢ€�ALTER TABLE ... REORGANIZE PARTITION鈥濇潵瀵瑰凡缁忔寜鐓�RANGE鍜�LIST鍒嗗尯琛ㄨ繘琛岄噸鏂板垎鍖烘椂锛屼笅闈㈡槸涓€浜涜璁颁綇鐨勫叧閿偣锛�

路         鐢ㄦ潵纭畾鏂板垎鍖烘ā寮忕殑PARTITION瀛愬彞浣跨敤涓庣敤鍦�CREATE TABLE涓‘瀹氬垎鍖烘ā寮忕殑PARTITION瀛愬彞鐩稿悓鐨勮鍒欍€�

鏈€閲嶈鐨勬槸锛屽簲璇ヨ浣忥細鏂板垎鍖烘ā寮忎笉鑳芥湁浠讳綍閲嶅彔鐨勫尯闂达紙閫傜敤浜庢寜鐓�RANGE鍒嗗尯鐨勮〃锛夋垨鍊奸泦鍚堬紙閫傜敤浜庨噸鏂扮粍缁囨寜鐓�LIST鍒嗗尯鐨勮〃锛夈€�

路         partition_definitions 鍒楄〃涓垎鍖虹殑鍚堥泦搴旇涓庡湪partition_list 涓懡鍚嶅垎鍖虹殑鍚堥泦鍗犳湁鐩稿悓鐨勫尯闂存垨鍊奸泦鍚堛€�

渚嬪锛屽湪鏈妭涓敤浣滀緥瀛愮殑鎴愬憳琛ㄤ腑锛屽垎鍖�p1鍜�p2鎬诲叡瑕嗙洊浜�1980鍒�1999鐨勮繖浜涘勾銆傚洜姝わ紝瀵硅繖涓や釜鍒嗗尯鐨勯噸鏂扮粍缁囬兘搴旇瑕嗙洊鐩稿悓鑼冨洿鐨勫勾浠姐€�

路         瀵逛簬鎸夌収RANGE鍒嗗尯鐨勮〃锛屽彧鑳介噸鏂扮粍缁囩浉閭荤殑鍒嗗尯锛涗笉鑳借烦杩�RANGE鍒嗗尯銆�

渚嬪锛屼笉鑳戒娇鐢ㄤ互鈥�ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...鈥濆紑澶寸殑璇彞锛屾潵閲嶆柊缁勭粐鏈妭涓敤浣滀緥瀛愮殑鎴愬憳琛ㄣ€傚洜涓猴紝p0瑕嗙洊浜�1970骞翠互鍓嶇殑骞翠唤锛岃€�p2瑕嗙洊浜嗕粠1990鍒�1999锛堝寘鎷�1990鍜�1999锛変箣闂寸殑骞翠唤锛屽洜鑰岃繖涓や釜鍒嗗尯涓嶆槸鐩搁偦鐨勫垎鍖恒€�

路         涓嶈兘浣跨敤REORGANIZE PARTITION鏉ユ敼鍙樿〃鐨勫垎鍖虹被鍨嬶紱涔熷氨鏄锛屼緥濡傦紝涓嶈兘鎶�RANGE鍒嗗尯鍙樹负HASH鍒嗗尯锛屽弽涔嬩害鐒躲€備篃涓嶈兘浣跨敤璇ュ懡浠ゆ潵鏀瑰彉鍒嗗尯琛ㄨ揪寮忔垨鍒椼€傚鏋滄兂鍦ㄤ笉鍒犻櫎鍜岄噸寤鸿〃鐨勬潯浠朵笅瀹炵幇杩欎袱涓换鍔★紝鍙互浣跨敤鈥淎LTER TABLE ... PARTITION BY ....鈥濓紝渚嬪锛�

路                ALTER TABLE members 

路                    PARTITION BY HASH(YEAR(dob))

路                    PARTITIONS 8锛�

娉ㄩ噴锛�鍦�MySQL 5.1鍙戝竷鍓嶇殑鐗堟湰涓紝鈥�ALTER TABLE ... PARTITION BY ...鈥�杩樻病鏈夊疄鐜般€備綔涓烘浛浠ｏ紝瑕佷箞浣跨敤鍏堝垹闄よ〃锛岀劧鍚庝娇鐢ㄦ兂瑕佺殑鍒嗗尯閲嶅缓琛紝鎴栬€�鈥斺€�濡傛灉闇€瑕佷繚鐣欏凡缁忓瓨鍌ㄥ湪琛ㄤ腑鐨勬暟鎹�鈥斺€�鍙互浣跨敤鈥淐REATE TABLE ... SELECT ...鈥�鏉ュ垱寤烘柊鐨勮〃锛岀劧鍚庝粠鏃ц〃涓妸鏁版嵁鎷疯礉鍒版柊琛ㄤ腑锛屽啀鍒犻櫎鏃ц〃锛屽鏈夊繀瑕侊紝鏈€鍚庨噸鏂板懡鍚嶆柊琛ㄣ€�

鍦ㄦ敼鍙樺垎鍖鸿缃柟闈紝鎸夌収HASH鍒嗗尯鎴�KEY鍒嗗尯鐨勮〃褰兼闈炲父鐩镐技锛屼絾鏄畠浠張涓庢寜鐓�RANGE鎴�LIST鍒嗗尯鐨勮〃鍦ㄥ緢澶氭柟闈㈡湁宸埆銆傛墍浠ワ紝鏈妭鍙璁烘寜鐓�HASH鎴�KEY鍒嗗尯琛ㄧ殑淇敼銆傚叧浜庢坊鍔犲拰鍒犻櫎鎸夌収RANGE鎴�LIST杩涜鍒嗗尯鐨勮〃鐨勫垎鍖虹殑璁ㄨ锛屽弬瑙�18.3.1鑺傦紝鈥淩ANGE鍜孡IST鍒嗗尯鐨勭鐞嗏€�銆�

涓嶈兘浣跨敤涓庝粠鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃涓垹闄ゅ垎鍖虹浉鍚岀殑鏂瑰紡锛屾潵浠�HASH鎴�KEY鍒嗗尯鐨勮〃涓垹闄ゅ垎鍖恒€備絾鏄紝鍙互浣跨敤鈥�ALTER TABLE ... COALESCE PARTITION鈥濆懡浠ゆ潵鍚堝苟HASH鎴�KEY鍒嗗尯銆備緥濡傦紝鍋囧畾鏈変竴涓寘鍚【瀹俊鎭暟鎹殑琛紝瀹冭鍒嗘垚浜�12涓垎鍖恒€傝椤惧琛ㄧ殑瀹氫箟濡備笅锛�

CREATE TABLE clients (

    id INT,

    fname VARCHAR(30),

    lname VARCHAR(30),

    signed DATE

)

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12锛�

瑕佸噺灏戝垎鍖虹殑鏁伴噺浠�12鍒�6锛屾墽琛屼笅闈㈢殑ALTER TABLE鍛戒护锛�

mysql> ALTER TABLE clients COALESCE PARTITION 6锛�

Query OK, 0 rows affected (0.02 sec)

瀵逛簬鎸夌収HASH锛�KEY锛�LINEAR HASH锛屾垨LINEAR KEY鍒嗗尯鐨勮〃锛� COALESCE鑳借捣鍒板悓鏍风殑浣滅敤銆備笅闈㈡槸涓€涓被浼间簬鍓嶉潰渚嬪瓙鐨勫彟澶栦竴涓緥瀛愶紝瀹冧滑鐨勫尯鍒彧鏄湪浜庤〃鏄寜鐓�LINEAR KEY 杩涜鍒嗗尯锛�

mysql> CREATE TABLE clients_lk (

    ->     id INT,

    ->     fname VARCHAR(30),

    ->     lname VARCHAR(30),

    ->     signed DATE

    -> )

    -> PARTITION BY LINEAR KEY(signed)

    -> PARTITIONS 12锛�

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 6锛�

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

COALESCE涓嶈兘鐢ㄦ潵澧炲姞鍒嗗尯鐨勬暟閲忥紝濡傛灉浣犲皾璇曡繖涔堝仛锛岀粨鏋滀細鍑虹幇绫讳技浜庝笅闈㈢殑閿欒锛�

mysql> ALTER TABLE clients COALESCE PARTITION 18;

閿欒1478 (HY000): 涓嶈兘绉诲姩鎵€鏈夊垎鍖猴紝浣跨敤DROP TABLE浠ｆ浛

瑕佸鍔犻【瀹㈣〃鐨勫垎鍖烘暟閲忎粠12鍒�18锛屼娇鐢ㄢ€�ALTER TABLE ... ADD PARTITION鈥�,鍏蜂綋濡備笅锛�

ALTER TABLE clients ADD PARTITION PARTITIONS 18锛�

娉ㄩ噴锛�鈥�ALTER TABLE ... REORGANIZE PARTITION鈥�涓嶈兘鐢ㄤ簬鎸夌収HASH鎴�HASH鍒嗗尯鐨勮〃銆�

娉ㄩ噴锛�瀹為檯涓婏紝鏈妭璁ㄨ鐨勫懡浠よ繕娌℃湁鍦�MySQL 5.1涓疄鐜帮紝 鍦ㄨ繖閲屾彁鍑虹殑鐩殑锛屾槸涓轰簡鍦�5.1鐗堟姇浜у墠鐨勫紑鍙戝懆鏈熸湡闂达紝寮曞嚭鏉ヨ嚜鐢ㄦ埛娴嬭瘯璇ヨ蒋浠剁殑鍙嶉鎰忚銆�(鎹㈠彞璇濊锛屽氨鏄€滆涓嶈鍙嶉杩欐牱鐨勭己闄凤紝璇磋繖浜涘懡浠や笉璧蜂綔鐢ㄢ€濓級銆�闅忕潃MySQL5.1鐗堝紑鍙戠殑缁х画锛岃繖浜涗俊鎭緢鏈夊彲鑳藉彂鐢熷彉鍖栥€傞殢鐫€鍒嗗尯鍔熻兘鐨勫疄鐜板拰鎻愰珮锛屾垜浠皢鏇存柊鏈妭鐨勫唴瀹广€�

MySQL 5.1涓彲浠ユ墽琛岃澶氬垎鍖虹淮鎶ょ殑浠诲姟銆傚浜庡垎鍖鸿〃锛�MySQL涓嶆敮鎸佸懡浠�CHECK TABLE锛�OPTIMIZE TABLE锛�ANALYZE TABLE锛屾垨REPAIR TABLE銆備綔涓烘浛浠ｏ紝鍙互浣跨敤ALTER TABLE 鐨勮澶氭墿灞曟潵鍦ㄤ竴涓垨澶氫釜鍒嗗尯涓婄洿鎺ュ湴鎵ц杩欎簺鎿嶄綔锛屽涓嬮潰鍒楀嚭鐨勯偅鏍凤細

路         閲嶅缓鍒嗗尯: 杩欏拰鍏堝垹闄や繚瀛樺湪鍒嗗尯涓殑鎵€鏈夎褰曪紝鐒跺悗閲嶆柊鎻掑叆瀹冧滑锛屽叿鏈夊悓鏍风殑鏁堟灉銆傚畠鍙敤浜庢暣鐞嗗垎鍖虹鐗囥€�

绀轰緥锛�

ALTER TABLE t1 REBUILD PARTITION (p0, p1)锛�

路         浼樺寲鍒嗗尯锛�濡傛灉浠庡垎鍖轰腑鍒犻櫎浜嗗ぇ閲忕殑琛岋紝鎴栬€呭涓€涓甫鏈夊彲鍙橀暱搴︾殑琛岋紙涔熷氨鏄锛屾湁VARCHAR锛�BLOB锛屾垨TEXT绫诲瀷鐨勫垪锛変綔浜嗚澶氫慨鏀癸紝鍙互浣跨敤鈥�ALTER TABLE ... OPTIMIZE PARTITION鈥�鏉ユ敹鍥炴病鏈変娇鐢ㄧ殑绌洪棿锛屽苟鏁寸悊鍒嗗尯鏁版嵁鏂囦欢鐨�纰庣墖銆�

绀轰緥锛�

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)锛�

鍦ㄤ竴涓粰瀹氱殑鍒嗗尯琛ㄤ笂浣跨敤鈥�OPTIMIZE PARTITION鈥�绛夊悓浜庡湪閭ｄ釜鍒嗗尯涓婅繍琛�CHECK PARTITION锛�ANALYZE PARTITION锛屽拰REPAIR PARTITION銆�

路         鍒嗘瀽鍒嗗尯锛�璇诲彇骞朵繚瀛樺垎鍖虹殑閿垎甯冦€�

绀轰緥锛�

ALTER TABLE t1 ANALYZE PARTITION (p3)锛�

路         淇ˉ鍒嗗尯锛� 淇ˉ琚牬鍧忕殑鍒嗗尯銆�

绀轰緥锛�

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

路         妫€鏌ュ垎鍖猴細 鍙互浣跨敤鍑犱箮涓庡闈炲垎鍖鸿〃浣跨敤CHECK TABLE 鐩稿悓鐨勬柟寮忔鏌ュ垎鍖恒€�

绀轰緥锛�

ALTER TABLE trb3 CHECK PARTITION (p1)锛�

杩欎釜鍛戒护鍙互鍛婅瘔浣犺〃t1鐨勫垎鍖�p1涓殑鏁版嵁鎴栫储寮曟槸鍚﹀凡缁忚鐮村潖銆傚鏋滃彂鐢熶簡杩欑鎯呭喌锛屼娇鐢ㄢ€�ALTER TABLE ... REPAIR PARTITION鈥�鏉ヤ慨琛ヨ鍒嗗尯銆�

杩樺彲浠ヤ娇鐢�mysqlcheck鎴�myisamchk 搴旂敤绋嬪簭锛屽湪瀵硅〃杩涜鍒嗗尯鏃舵墍浜х敓鐨勩€佸崟鐙殑MYI鏂囦欢涓�杩涜鎿嶄綔锛屾潵瀹屾垚杩欎簺浠诲姟銆傝鍙傝8.7鑺傦紝鈥渕ysqlcheck锛氳〃缁存姢鍜岀淮淇▼搴忊€�銆傦紙鍦�pre-alpha缂栫爜涓紝杩欎釜鍔熻兘宸茬粡鍙互浣跨敤锛夈€�

鏈妭璁ㄨ鑾峰彇鍏充簬鐜版湁鍒嗗尯鐨勪俊鎭€傝繖涓姛鑳戒粛鐒跺浜庤鍒掗樁娈碉紝鎵€浠ョ幇闃舵鍦ㄨ繖閲屾弿杩扮殑锛屽疄闄呬笂鏄垜浠兂瑕佸湪MySQL 5.1涓疄鐜扮殑涓€涓瑙傘€�

濡傚湪鏈珷涓埆澶勮璁虹殑涓€鏍凤紝鍦�SHOW CREATE TABLE鐨勮緭鍑轰腑鍖呭惈浜嗙敤浜庡垱寤哄垎鍖鸿〃鐨�PARTITION BY瀛愬彞銆備緥濡傦細

mysql> SHOW CREATE TABLE trb3\G

*************************** 1. row ***************************

       Table: trb3

Create Table: CREATE TABLE `trb3` (

  `id` int(11) default NULL,

  `name` varchar(50) default NULL,

  `purchased` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 

PARTITION BY RANGE (YEAR(purchased)) (

  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 

  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 

  PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, 

  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

)

1 row in set (0.00 sec)

娉ㄩ噴锛�褰撳墠锛屽浜庢寜HASH鎴�KEY鍒嗗尯鐨勮〃锛�PARTITIONS瀛愬彞骞朵笉鏄剧ず銆� (Bug #14327)

SHOW TABLE STATUS鐢ㄤ簬鍒嗗尯琛紝瀹冪殑杈撳嚭涓庣敤浜庨潪鍒嗗尯琛ㄧ殑杈撳嚭鐩稿悓锛岄櫎浜嗗紩鎿庯紙Engine锛夊垪鎬绘槸鍖呭惈'PARTITION'鍊笺€�(鍏充簬杩欎釜鍛戒护鐨勬洿澶氫俊鎭紝鍙傝13.5.4.18鑺傦紝鈥淪HOW TABLE STATUS璇硶鈥�锛�銆�瑕佽幏鍙栧崟涓垎鍖虹殑鐘舵€佷俊鎭紝鎴戜滑璁″垝瀹炵幇涓€涓�SHOW PARTITION STATUS鍛戒护锛�璇峰弬瑙佷笅闈�)銆�

璁″垝鐢ㄤ簬鍒嗗尯琛ㄧ殑銆佷袱涓檮鍔犵殑SHOW鍛戒护鏄細

路         SHOW PARTITIONS

杩欎釜鍛戒护棰勬湡鍏跺姛鑳界被浼间簬SHOW TABLES鍜�SHOW DATABASES锛岄櫎浜嗚鍛戒护灏嗗垪鍑虹殑鏄垎鍖鸿€屼笉鏄〃鎴栨暟鎹簱銆傝繖涓懡浠ょ殑杈撳嚭鍙兘鍖呭惈鍗曚釜绉颁负Partitions_in_tbl_name 鐨勫垪锛屽叾涓�tbl_name 鏄垎鍖鸿〃鐨勫悕瀛椼€傚浜�SHOW TABLES鍛戒护鑰岃█锛屽鏋滀竴鏃﹂€夋嫨浜嗕竴涓暟鎹簱锛岄殢鍚庤鏁版嵁搴撳皢浣滀负SHOW TABLES鍛戒护鐨勯粯璁ゆ暟鎹簱銆備絾鏄敱浜�SHOW PARTITIONS鍛戒护涓嶅彲鑳界敤杩欐牱鐨勬柟寮忔潵鈥�閫夋嫨鈥�涓€涓〃锛屽畠寰堝彲鑳介渶瑕佷娇鐢�FROM瀛愬彞锛屼互渚�MySQL鐭ラ亾瑕佹樉绀虹殑鏄摢涓〃鐨勫垎鍖轰俊鎭€�

路         SHOW PARTITION STATUS

杩欎釜鍛戒护灏嗘彁渚涘叧浜庝竴涓垨澶氫釜鍒嗗尯鐨勮缁嗙姸鎬佷俊鎭€傚畠鐨勮緭鍑哄緢鍙兘鍖呭惈鏈変笌SHOW TABLE STATUS 鐨勮緭鍑虹浉鍚屾垨绫讳技鐨勫垪锛屾澶栵紝杩樺寘鎷樉绀虹敤浜庡垎鍖虹殑鏁版嵁鍜岀储寮曡矾寰勭殑闄勫姞鍒椼€傝繖涓懡浠ゅ彲鑳芥敮鎸�LIKE鍜�FROM瀛愬彞锛岃繖鏍蜂娇寰楅€氳繃鍚嶅瓧鑾峰緱鍏充簬涓€涓粰瀹氬垎鍖虹殑淇℃伅锛屾垨鑰呰幏寰楀叧浜庡睘浜庢寚瀹氳〃鎴栨暟鎹簱鐨勫垎鍖虹殑淇℃伅锛屾垚涓哄彲鑳姐€�

鎵╁睍INFORMATION_SCHEMA 鏁版嵁搴撶殑璁″垝涔熷湪杩涜涓紝浠ヤ究鎻愪緵鍏充簬鍒嗗尯琛ㄥ拰鍒嗗尯鐨勪俊鎭€傝繖涓鍒掑綋鍓嶈繕澶勪竴涓湪闈炲父鏃╃殑闃舵锛涢殢鐫€琛ュ厖鐨勪俊鎭彉寰楀彲鐢紝浠ュ強浠讳綍鏂扮殑銆佷笌鍒嗗尯鏈夊叧鐨�INFORMATION_SCHEMA鎵╁睍寰椾互瀹炵幇锛屾垜浠皢鏇存柊鎵嬪唽鐩稿叧閮ㄥ垎鐨勫唴瀹广€�