可以針對分區表的每個分區指定各自的存儲路徑，對于innodb存儲引擎的表只能指定數據路徑，因為數據和索引是存儲在一個文件當中，對于MYISAM存儲引擎可以分別指定數據文件和索引文件，一般也只有RANGE、LIST分區、sub子分區才有可能需要單獨指定各個分區的路徑，HASH和KEY分區的所有分區的路徑都是一樣。RANGE分區指定路徑和LIST分區是一樣的，這里就拿LIST分區來做講解。
一、MYISAM存儲引擎
CREATE?TABLE?th?(id?INT,?adate?DATE) engine='MyISAM'PARTITION?BY?LIST(YEAR(adate)) ( ??PARTITION?p1999?VALUES?IN?(1995,?1999,?2003) ????DATA?Directory?=?'/data/data' ????INDEX?DIRECTORY?=?'/data/idx', ??PARTITION?p2000?VALUES?IN?(1996,?2000,?2004) ????DATA?DIRECTORY?=?'/data/data' ????INDEX?DIRECTORY?=?'/data/idx', ??PARTITION?p2001?VALUES?IN?(1997,?2001,?2005) ????DATA?DIRECTORY?=?'/data/data' ????INDEX?DIRECTORY?=?'/data/idx', ??PARTITION?p2002?VALUES?IN?(1998,?2002,?2006) ????DATA?DIRECTORY?=?'/data/data' ????INDEX?DIRECTORY?=?'/data/idx');
注意：myisam存儲引擎的數據文件和索引文件是分庫存儲所以可以為數據文件和索引文件定義各自的路徑，innodb存儲引擎只能定義數據路徑。
二、INNODB存儲引擎
CREATE?TABLE?thex?(id?INT,?adate?DATE) engine='InnoDB'PARTITION?BY?LIST(YEAR(adate)) ( ??PARTITION?p1999?VALUES?IN?(1995,?1999,?2003) ????DATA?DIRECTORY?=?'/data/data', ???? ??PARTITION?p2000?VALUES?IN?(1996,?2000,?2004) ????DATA?DIRECTORY?=?'/data/data', ??? ??PARTITION?p2001?VALUES?IN?(1997,?2001,?2005) ????DATA?DIRECTORY?=?'/data/data', ???? ??PARTITION?p2002?VALUES?IN?(1998,?2002,?2006) ????DATA?DIRECTORY?=?'/data/data' ??);
指定路徑之后在原來的路徑中innodb生成了4個指向數據存儲的路徑文件，myisam生成了一個th.par文件指明該表是分區表，同時數據文件和索引文件指向了實際的存儲路徑。
三、子分區?
1.子分區
CREATE?TABLE?tb_sub_dir?(id?INT,?purchased?DATE) ENGINE='MYISAM' ????PARTITION?BY?RANGE(?YEAR(purchased)?) ????SUBPARTITION?BY?HASH(?TO_DAYS(purchased)?)?( ????????PARTITION?p0?VALUES?LESS?THAN?(1990)? ????????( ????????????SUBPARTITION?s0 ????????????????DATA?DIRECTORY?=?'/data/data_sub1' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub1', ????????????SUBPARTITION?s1 ????????????????DATA?DIRECTORY?=?'/data/data_sub1' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub1' ????????), ????????PARTITION?p1?VALUES?LESS?THAN?(2000)? ????????( ????????????SUBPARTITION?s2 ????????????????DATA?DIRECTORY?=?'/data/data_sub2' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub2', ????????????SUBPARTITION?s3 ????????????????DATA?DIRECTORY?=?'/data/data_sub2' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub2' ????????), ????????PARTITION?p2?VALUES?LESS?THAN?MAXVALUE? ????????( ????????????SUBPARTITION?s4 ????????????????DATA?DIRECTORY?=?'/data/data_sub3' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub3', ????????????SUBPARTITION?s5 ????????????????DATA?DIRECTORY?=?'/data/data_sub3' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub3' ????????) ????);
2.子分區再分
CREATE?TABLE?tb_sub_dirnew?(id?INT,?purchased?DATE) ENGINE='MYISAM' ????PARTITION?BY?RANGE(?YEAR(purchased)?) ????SUBPARTITION?BY?HASH(?TO_DAYS(purchased)?)?( ????????PARTITION?p0?VALUES?LESS?THAN?(1990)? ????????DATA?DIRECTORY?=?'/data/data' ????????INDEX?DIRECTORY?=?'/data/idx' ????????( ????????????SUBPARTITION?s0 ????????????????DATA?DIRECTORY?=?'/data/data_sub1' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub1', ????????????SUBPARTITION?s1 ????????????????DATA?DIRECTORY?=?'/data/data_sub1' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub1' ????????), ????????PARTITION?p1?VALUES?LESS?THAN?(2000) ????????DATA?DIRECTORY?=?'/data/data' ????????INDEX?DIRECTORY?=?'/data/idx' ????????( ????????????SUBPARTITION?s2 ????????????????DATA?DIRECTORY?=?'/data/data_sub2' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub2', ????????????SUBPARTITION?s3 ????????????????DATA?DIRECTORY?=?'/data/data_sub2' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub2' ????????), ????????PARTITION?p2?VALUES?LESS?THAN?MAXVALUE ????????DATA?DIRECTORY?=?'/data/data' ????????INDEX?DIRECTORY?=?'/data/idx' ????????( ????????????SUBPARTITION?s4 ????????????????DATA?DIRECTORY?=?'/data/data_sub3' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub3', ????????????SUBPARTITION?s5 ????????????????DATA?DIRECTORY?=?'/data/data_sub3' ????????????????INDEX?DIRECTORY?=?'/data/idx_sub3' ????????) ????);
也可以給個分區指定路徑后再給子分區指定路徑，但是這樣沒有意義，因為數據的存在都是由子分區決定的。
注意：
1.指定的路徑必須存在，否則分區無法創建成功
2.MYISAM存儲引擎的數據文件和索引文件是分庫存儲所以可以為數據文件和索引文件定義各自的路徑，INNODB存儲引擎只能定義數據路徑
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
