基于指定分区取值规则关联主表创建业务表的技术咨询

一、建表语句 #

1. 分区阶段主表（存储所有枚举分区值） #

-- 分区阶段主表
CREATE TABLE partition_stage (
    id NUMBER PRIMARY KEY,
    task VARCHAR2(20) NOT NULL,
    -- 约束task取值为指定枚举值
    CONSTRAINT chk_task_val CHECK (task IN (
        'start','Build','Test','Reg Test','A','B','C','D','E','F','G','H','I','J','K','L'
    ))
);
-- 提前插入枚举值
INSERT INTO partition_stage(id, task) VALUES(0,'start');
INSERT INTO partition_stage(id, task) VALUES(1,'Build');
INSERT INTO partition_stage(id, task) VALUES(2,'Test');
INSERT INTO partition_stage(id, task) VALUES(3,'Reg Test');
INSERT INTO partition_stage(id, task) VALUES(4,'A');
INSERT INTO partition_stage(id, task) VALUES(5,'B');
INSERT INTO partition_stage(id, task) VALUES(6,'C');
INSERT INTO partition_stage(id, task) VALUES(7,'D');
INSERT INTO partition_stage(id, task) VALUES(8,'E');
INSERT INTO partition_stage(id, task) VALUES(9,'F');
INSERT INTO partition_stage(id, task) VALUES(10,'G');
INSERT INTO partition_stage(id, task) VALUES(11,'H');
INSERT INTO partition_stage(id, task) VALUES(12,'I');
INSERT INTO partition_stage(id, task) VALUES(13,'J');
INSERT INTO partition_stage(id, task) VALUES(14,'K');
INSERT INTO partition_stage(id, task) VALUES(15,'L');
COMMIT;

2. 业务数据表 #

修正原需求字段笔误：varchar 2调整为VARCHAR2(200)（可根据实际存储长度调整），varcha2修正为VARCHAR2：

CREATE TABLE business_data (
    id NUMBER PRIMARY KEY,
    description VARCHAR2(200),
    partition_0_to_5 VARCHAR2(200),
    partition_6_to_10 VARCHAR2(200),
    partition_11_to_15 VARCHAR2(200),
    status VARCHAR2(50)
);

如果需要校验三个分区字段的取值合法性，可添加正则校验约束（默认使用英文逗号分隔多值）：

ALTER TABLE business_data ADD CONSTRAINT chk_part0_5 CHECK (
    REGEXP_LIKE(partition_0_to_5, '^(start|Build|Test|Reg Test|A|B)(,(start|Build|Test|Reg Test|A|B))*$') 
    OR partition_0_to_5 IS NULL
);
ALTER TABLE business_data ADD CONSTRAINT chk_part6_10 CHECK (
    REGEXP_LIKE(partition_6_to_10, '^(C|D|E|F|G)(,(C|D|E|F|G))*$') 
    OR partition_6_to_10 IS NULL
);
ALTER TABLE business_data ADD CONSTRAINT chk_part11_15 CHECK (
    REGEXP_LIKE(partition_11_to_15, '^(H|I|J|K|L)(,(H|I|J|K|L))*$') 
    OR partition_11_to_15 IS NULL
);

二、数据映射规则 #

分区值归属区间对应关系 #

• partition_0_to_5：仅存储分区主表id为0~5的取值：start、Build、Test、Reg Test、A、B
• partition_6_to_10：仅存储分区主表id为6~10的取值：C、D、E、F、G
• partition_11_to_15：仅存储分区主表id为11~15的取值：H、I、J、K、L

多值存储规则 #

同一区间存在多个归属分区的，使用英文逗号分隔多个取值存入对应字段，无对应归属的字段留空即可。

三、使用示例 #

按照需求示例：id为1的记录同时属于1.Build和6.C两个分区，插入语句如下：

INSERT INTO business_data(id, description, partition_0_to_5, partition_6_to_10, status) 
VALUES(1, '测试记录', 'Build', 'C', '生效');

多值场景示例：某记录同时归属start、Reg Test、D、J四个分区，插入语句如下：

INSERT INTO business_data(id, description, partition_0_to_5, partition_6_to_10, partition_11_to_15, status) 
VALUES(2, '多分区测试记录', 'start,Reg Test', 'D', 'J', '生效');

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