MySQL 分區表操作實戰：創建、新增、刪除、重組分區完整指南

MySQL Partition 分區表 DDL 操作全攻略

對現有非分區表直接 ALTER TABLE ... ADD PARTITION 會報錯：ERROR 1505: Partition management on a not partitioned table is not possible。正確路徑：創建新分區表 → 數據遷移 → 重命名切換。

1. 創建範圍分區表範例

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)
);

2. 測試數據插入與分區查詢

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');

-- 查詢 P2 分區數據（1995-2004）
SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';

3. 分區刪除：數據同步消失

ALTER TABLE tr DROP PARTITION p2;
-- p2 及其數據永久刪除，後續插入 1995-1999 年數據會路由到 p3

4. 分區新增：僅支持尾部追加

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)
);

-- 尾部新增分區
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

5. 分區重組：拆分與合併（數據不丟失）

-- 將 p0 拆分為 m0(1960前) 和 m1(1960-1970)
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION m0 VALUES LESS THAN (1960),
    PARTITION m1 VALUES LESS THAN (1970)
);

-- 合併多個分區
ALTER TABLE sale_data REORGANIZE PARTITION p201001,p201002,p201003,
    p201004,p201005,p201006,p201007,p201008,p201009 INTO (
    PARTITION p2010Q1 VALUES LESS THAN (201004),
    PARTITION p2010Q2 VALUES LESS THAN (201007),
    PARTITION p2010Q3 VALUES LESS THAN (201010)
);

6. 關鍵限制與最佳實踐

• 分區鍵必須包含在主鍵/唯一鍵中
• 分區字段不能為 NULL，建議 NOT NULL
• REORGANIZE 可拆分/合併，ADD PARTITION 僅能尾部追加
• 大表變更分區方案：建新表 → INSERT SELECT → RENAME TABLE 原子切換
• 定期 ANALYZE TABLE 更新分區統計信息