現有非分區表需改為分區表,不能直接 ALTER TABLE ADD PARTITION(報錯 ERROR 1505: Partition management on a not partitioned table is not possible)。本文提供標準遷移路徑:新建分區表 → 資料遷移 → 原子切換,並演示分區增刪改查、合併拆分全流程操作。
INSERT INTO partitioned_tbl SELECT * FROM original_tblRENAME TABLE original_tbl TO original_tbl_bak, partitioned_tbl TO original_tbl;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)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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');
-- 僅掃描 p1、p2、p3 三個分區
EXPLAIN PARTITIONS SELECT * FROM tr
WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';
ALTER TABLE tr DROP PARTITION p2;
SHOW CREATE TABLE tr\G
-- p2 消失,p0、p1、p3 保留
INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
-- 原屬 p2 的資料現路由至 p3(VALUES LESS THAN 2005)
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));
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION m0 VALUES LESS THAN (1960),
PARTITION m1 VALUES LESS THAN (1970)
);
SHOW CREATE TABLE members\G
-- 結果包含 m0, m1, p1, p2, p3 五個分區,資料完整無損
| 操作 | 語法 | 資料影響 | 鎖等級 | 適用場景 |
|---|---|---|---|---|
| 新增尾部分區 | ADD PARTITION | 無資料移動 | MDL 共享鎖 | 時間序列定期擴展 |
| 刪除分區 | DROP PARTITION | 資料永久刪除 | MDL 獨佔鎖 | 歷史資料歸檔清理 |
| 合併/拆分現有分區 | REORGANIZE PARTITION | 資料重組,不丟失 | MDL 獨佔鎖 | 調整分區粒度、合併冷分區 |
| 修改分區表達式 | PARTITION BY ... | 全表重建 | MDL 獨佔鎖 | 極少用,需停機 |
-- 1. 建立分區表(結構同步,加上分區定義)
CREATE TABLE orders_partitioned LIKE orders;
ALTER TABLE orders_partitioned
PARTITION BY RANGE COLUMNS (order_date) (
PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 2. 分批遷移資料(避免長事務、鎖衝突)
-- 假設 order_date 範圍 2023-01-01 至 2023-12-31
INSERT INTO orders_partitioned SELECT * FROM orders
WHERE order_date < '2023-02-01';
INSERT INTO orders_partitioned SELECT * FROM orders
WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01';
INSERT INTO orders_partitioned SELECT * FROM orders
WHERE order_date >= '2023-03-01';
-- 3. 一致性校驗
SELECT COUNT(*), SUM(order_amount) FROM orders;
SELECT COUNT(*), SUM(order_amount) FROM orders_partitioned;
-- 4. 原子切換(需短暫封寫,秒級)
RENAME TABLE orders TO orders_old, orders_partitioned TO orders;
-- 5. 觀察 1-7 天無異常後
DROP TABLE orders_old;
分區表新增/管理操作核心在於:非分區表遷移必須重建表、REORGANIZE PARTITION 安全合併拆分不丟數據、DROP PARTITION 等同於資料刪除需謹慎。生產環境變更請遵循:預演環境驗證 → 分批遷移 → 原子切換 → 觀察期 → 清理 標準流程。