mysql分割槽表小結2

更改分割槽型別

如果想更改分割槽型別，只需呼叫alter table partition by即可，比oracle要簡便；

--將range分割槽改為key分割槽

CREATE TABLE trb3 (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)

);

 

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS

 

Reorganize partition

對於range/list分割槽表，如想分割/合併相鄰分割槽，除了split/coalesce命令外，還可以使用reorganize partition

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

)

--分割第一個分割槽

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (1960),

    PARTITION s1 VALUES LESS THAN (1970)

);

--合併前兩個分割槽

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (

    PARTITION p0 VALUES LESS THAN (1970)

);

  

分割槽修剪

對於key/hash分割槽，分割槽修剪只使用於整數列；

range/list分割槽限制沒有那麼多，5.5開始to_days()/to_seconds()/year()也支援分割槽修剪

CREATE TABLE t2 (

    fname VARCHAR(50) NOT NULL,

    lname VARCHAR(50) NOT NULL,

    region_code TINYINT UNSIGNED NOT NULL,

    dob DATE NOT NULL

)

PARTITION BY RANGE( YEAR(dob) ) (

    PARTITION d0 VALUES LESS THAN (1970),

    PARTITION d1 VALUES LESS THAN (1975),

    PARTITION d2 VALUES LESS THAN (1980),

    PARTITION d3 VALUES LESS THAN (1985),

    PARTITION d4 VALUES LESS THAN (1990),

    PARTITION d5 VALUES LESS THAN (2000),

    PARTITION d6 VALUES LESS THAN (2005),

    PARTITION d7 VALUES LESS THAN MAXVALUE

);

--該sql照樣能使用分割槽修剪

SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

 

Tip

hash/linear hash分割槽表，不能使用drop partition命令，可用coalesce partition替代

分割槽表建立後，不要修改sql_mode，否則有可能導致資料損壞，且必須保持master/slave使用同一種sql_mode

一個表最多可包含1024個分割槽/子分割槽(NDB儲存引擎除外)，若表沒有達到最大分割槽數限制卻仍遭遇Got error ... from storage engine: Out of resources when opening file,，則需檢查open_files_limit引數設定；

5.5新增操作命令  alter table … truncate partition

分割槽表不支援外來鍵

臨時表和日誌表都不可分割槽

資料庫升級時，使用非NDBCLUSTER的key分割槽表必須重建

子分割槽只能使用key/hash型別

如果表指定了主鍵或者unique列，則必須指定這些列或其部分列做分割槽鍵

mysql> CREATE TABLE np_pk (

    ->     id INT NOT NULL AUTO_INCREMENT,

    ->     name VARCHAR(50),

    ->     added DATE,

    ->     PRIMARY KEY (id)

    -> );

Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE np_pk

    ->     PARTITION BY HASH( TO_DAYS(added) )

    ->     PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function