MariaDB表按paid LIST分区后按branch子分区报错求助

MariaDB的复合分区规则明确：LIST类型的主分区仅支持HASH或KEY类型的子分区，不允许使用RANGE/LIST作为子分区，这就是你执行语句时报错的核心原因。

根据你的需求（先按paid分，再按current_branch范围细分），提供三种落地方案：

方案1：LIST-LIST分区（将分支范围转为枚举分组） #

通过生成列把current_branch的连续范围映射为离散分组值，再基于分组值做LIST子分区，适配MariaDB的规则。

步骤1：添加生成列（无需修改业务代码） #

ALTER TABLE o_loans ADD COLUMN branch_group INT AS (
  CASE
    WHEN current_branch < 2 THEN 1
    WHEN current_branch < 4 THEN 2
    WHEN current_branch < 6 THEN 3
    WHEN current_branch < 8 THEN 4
    WHEN current_branch < 10 THEN 5
    WHEN current_branch < 12 THEN 6
    WHEN current_branch < 14 THEN 7
    WHEN current_branch < 16 THEN 8
    WHEN current_branch < 18 THEN 9
    WHEN current_branch < 20 THEN 10
    ELSE 11
  END
) STORED;

步骤2：执行LIST-LIST分区 #

ALTER TABLE o_loans 
PARTITION BY LIST (paid) 
SUBPARTITION BY LIST (branch_group) (
  PARTITION p0 VALUES IN (0) (
    SUBPARTITION p0_1 VALUES IN (1),
    SUBPARTITION p0_2 VALUES IN (2),
    SUBPARTITION p0_3 VALUES IN (3),
    SUBPARTITION p0_4 VALUES IN (4),
    SUBPARTITION p0_5 VALUES IN (5),
    SUBPARTITION p0_6 VALUES IN (6),
    SUBPARTITION p0_7 VALUES IN (7),
    SUBPARTITION p0_8 VALUES IN (8),
    SUBPARTITION p0_9 VALUES IN (9),
    SUBPARTITION p0_10 VALUES IN (10),
    SUBPARTITION p0_overflow VALUES IN (11)
  ),
  PARTITION p1 VALUES IN (1) (
    SUBPARTITION p1_1 VALUES IN (1),
    SUBPARTITION p1_2 VALUES IN (2),
    SUBPARTITION p1_3 VALUES IN (3),
    SUBPARTITION p1_4 VALUES IN (4),
    SUBPARTITION p1_5 VALUES IN (5),
    SUBPARTITION p1_6 VALUES IN (6),
    SUBPARTITION p1_7 VALUES IN (7),
    SUBPARTITION p1_8 VALUES IN (8),
    SUBPARTITION p1_9 VALUES IN (9),
    SUBPARTITION p1_10 VALUES IN (10),
    SUBPARTITION p1_overflow VALUES IN (11)
  )
);

方案2：RANGE分区模拟复合逻辑 #

直接用表达式将paid和current_branch组合成RANGE分区键，无需额外字段，直接实现分组逻辑。

ALTER TABLE o_loans 
PARTITION BY RANGE (paid * 100 + current_branch) (
  -- paid=0，branch <2
  PARTITION p0_1 VALUES LESS THAN (200),
  -- paid=0，branch <4
  PARTITION p0_2 VALUES LESS THAN (400),
  PARTITION p0_3 VALUES LESS THAN (600),
  PARTITION p0_4 VALUES LESS THAN (800),
  PARTITION p0_5 VALUES LESS THAN (1000),
  PARTITION p0_6 VALUES LESS THAN (1200),
  PARTITION p0_7 VALUES LESS THAN (1400),
  PARTITION p0_8 VALUES LESS THAN (1600),
  PARTITION p0_9 VALUES LESS THAN (1800),
  PARTITION p0_10 VALUES LESS THAN (2000),
  -- paid=0，branch ≥20
  PARTITION p0_overflow VALUES LESS THAN (10000),
  -- paid=1，branch <2
  PARTITION p1_1 VALUES LESS THAN (1200),
  PARTITION p1_2 VALUES LESS THAN (1400),
  PARTITION p1_3 VALUES LESS THAN (1600),
  PARTITION p1_4 VALUES LESS THAN (1800),
  PARTITION p1_5 VALUES LESS THAN (2000),
  PARTITION p1_6 VALUES LESS THAN (2200),
  PARTITION p1_7 VALUES LESS THAN (2400),
  PARTITION p1_8 VALUES LESS THAN (2600),
  PARTITION p1_9 VALUES LESS THAN (2800),
  PARTITION p1_10 VALUES LESS THAN (3000),
  -- paid=1，branch ≥20
  PARTITION p1_overflow VALUES LESS THAN (11000),
  -- 兜底分区
  PARTITION p_overflow VALUES LESS THAN MAXVALUE
);

注意：表达式paid * 100 + current_branch确保paid=0的分支值落在0-999区间，paid=1落在100-1999区间，需根据实际分支范围调整边界值。

方案3：调整分区顺序（RANGE主分区+LIST子分区） #

如果可以接受先按current_branch范围做RANGE主分区，再按paid做LIST子分区，这种组合符合MariaDB的复合分区规则（RANGE-LIST）：

ALTER TABLE o_loans 
PARTITION BY RANGE (current_branch) 
SUBPARTITION BY LIST (paid) (
  PARTITION p_branch1 VALUES LESS THAN (2) (
    SUBPARTITION p_branch1_unpaid VALUES IN (0),
    SUBPARTITION p_branch1_paid VALUES IN (1)
  ),
  PARTITION p_branch2 VALUES LESS THAN (4) (
    SUBPARTITION p_branch2_unpaid VALUES IN (0),
    SUBPARTITION p_branch2_paid VALUES IN (1)
  ),
  PARTITION p_branch3 VALUES LESS THAN (6) (
    SUBPARTITION p_branch3_unpaid VALUES IN (0),
    SUBPARTITION p_branch3_paid VALUES IN (1)
  ),
  PARTITION p_branch4 VALUES LESS THAN (8) (
    SUBPARTITION p_branch4_unpaid VALUES IN (0),
    SUBPARTITION p_branch4_paid VALUES IN (1)
  ),
  PARTITION p_branch5 VALUES LESS THAN (10) (
    SUBPARTITION p_branch5_unpaid VALUES IN (0),
    SUBPARTITION p_branch5_paid VALUES IN (1)
  ),
  PARTITION p_branch6 VALUES LESS THAN (12) (
    SUBPARTITION p_branch6_unpaid VALUES IN (0),
    SUBPARTITION p_branch6_paid VALUES IN (1)
  ),
  PARTITION p_branch7 VALUES LESS THAN (14) (
    SUBPARTITION p_branch7_unpaid VALUES IN (0),
    SUBPARTITION p_branch7_paid VALUES IN (1)
  ),
  PARTITION p_branch8 VALUES LESS THAN (16) (
    SUBPARTITION p_branch8_unpaid VALUES IN (0),
    SUBPARTITION p_branch8_paid VALUES IN (1)
  ),
  PARTITION p_branch9 VALUES LESS THAN (18) (
    SUBPARTITION p_branch9_unpaid VALUES IN (0),
    SUBPARTITION p_branch9_paid VALUES IN (1)
  ),
  PARTITION p_branch10 VALUES LESS THAN (20) (
    SUBPARTITION p_branch10_unpaid VALUES IN (0),
    SUBPARTITION p_branch10_paid VALUES IN (1)
  ),
  PARTITION p_branch_overflow VALUES LESS THAN MAXVALUE (
    SUBPARTITION p_overflow_unpaid VALUES IN (0),
    SUBPARTITION p_overflow_paid VALUES IN (1)
  )
);

1. 执行分区操作前务必全量备份数据，避免操作失败导致数据丢失
2. 分区键需与业务高频查询条件匹配，才能发挥分区的性能优化作用
3. 生成列需要MariaDB 10.2及以上版本支持
4. 确保分区字段是主键/唯一键的一部分（你的表主键已包含paid，满足要求）

内容的提问来源于stack exchange，提问作者Sam