MySQL 分區表新增分區操作實戰：避坑指南與最佳實踐

MySQL 分區表新增分區：正確姿勢與常見錯誤

對非分區表執行 ALTER TABLE ... ADD PARTITION 會報錯：ERROR 1505: Partition management on a not partitioned table is not possible。本文通過完整案例演示標準化操作流程。

1. 錯誤場景重現

CREATE TABLE t_no_part (id INT, dt DATE);
ALTER TABLE t_no_part ADD PARTITION (PARTITION p1 VALUES LESS THAN (2020));
-- ERROR 1505 (HY000)

2. 正確流程：建新表 → 遷移數據 → 切換

-- 步驟1：創建同結構分區表
CREATE TABLE t_part (
    id INT, dt DATE
) PARTITION BY RANGE (YEAR(dt)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020)
);

-- 步驟2：數據遷移
INSERT INTO t_part SELECT * FROM t_no_part;

-- 步驟3：原子切換（需短暫鎖表）
RENAME TABLE t_no_part TO t_no_part_old, t_part TO t_no_part;

3. 範圍分區表完整生命周期演示

創建測試表

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

插入測試數據

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

分區查詢驗證

SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';
-- 命中 p2, p3

刪除分區（數據同步刪除）

ALTER TABLE tr DROP PARTITION p2;
-- p2 及 1995-1999 年數據永久消失

尾部新增分區

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

分區重組：拆分與合併（數據安全）

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

4. 關鍵注意事項

• ADD PARTITION 僅支持尾部追加，中間插入需 REORGANIZE
• DROP PARTITION 物理刪除數據，不可恢復，謹慎操作
• REORGANIZE PARTITION 過程會重建涉及分區，大表耗時長，建議低峰期執行
• 分區鍵必須包含在主鍵/唯一鍵中，字段非 NULL
• 生產環境大表變更：影子表雙寫驗證 → 原子重命名切換