MySQL 分區表新增分區操作實戰：ALTER TABLE ADD PARTITION 與 REORGANIZE PARTITION 避坑指南

已建普通表無法直接 ALTER TABLE ... ADD PARTITION（報錯 1505），正確路徑是創建同結構分區表 → INSERT ... SELECT 遷移數據。本文實戰演示：RANGE 分區表創建、測試數據插入、分區查詢、DROP PARTITION 歸檔、ADD PARTITION 擴展、REORGANIZE PARTITION 合併拆分，驗證數據零丟失。

一、創建測試分區表

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

三、DROP PARTITION 歸檔（數據同步刪除）

ALTER TABLE tr DROP PARTITION p2;  -- 1995-1999 數據刪除
SHOW CREATE TABLE tr;  -- 僅剩 p0, p1, p3
-- 再次插入 1995 年數據，自動路由至 p3
INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');

四、ADD PARTITION 尾部擴展（僅支援尾部）

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));  -- 尾部新增成功

五、REORGANIZE PARTITION 合併與拆分（數據零丟失）

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

SHOW CREATE TABLE members;
-- 結果：m0, m1, p1, p2, p3 共 5 分區，數據完整保留

注意：REORGANIZE 可合併相鄰分區、拆分單分區，數據不丟失，但會重寫涉及分區數據，大表操作需低峰期執行。

六、關鍵避坑總結

1. 普通表不可直接 ADD PARTITION：報錯 1505，必須重建分區表遷移數據。
2. ADD PARTITION 僅支援尾部：中間插入需用 REORGANIZE。
3. DROP PARTITION 刪除數據不可逆：生產環境務必先備份或確認歸檔策略。
4. REORGANIZE 重寫數據：大分區操作時間長、產生臨時文件、鎖表（MDL），建議 5.6+ ALGORITHM=INPLACE, LOCK=NONE（部分操作支援）。
5. 分區鍵選擇：查詢模式決定分區鍵，確保分區裁剪生效（EXPLAIN PARTITIONS 驗證）。