如何高效存储重复出现的可变长度数据库行数据？

核心思路：拆分表结构（范式化设计） #

不要把所有数据塞进单一行，而是拆分成主表+关联表，完美适配变长的关联值需求。

1. 主表（存储参考值） #

创建主表存储每组数据的核心参考值，例如命名为reference_records：

CREATE TABLE reference_records (
    ref_id VARCHAR(20) PRIMARY KEY, -- 对应示例中的第一个参考值3500312
    ref_type VARCHAR(10) NOT NULL    -- 对应示例中的第二个参考值09
);

将示例里的<strong>3500312;09</strong>拆分为两个字段，作为主表的唯一标识与属性。

2. 关联表（存储变长的关联数据） #

创建关联表存储每个参考值对应的所有关联项，例如命名为related_records：

CREATE TABLE related_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(20) NOT NULL, -- 关联主表的参考值ID
    related_value VARCHAR(20) NOT NULL, -- 对应示例中的4217558、3780576这类关联值
    operator_1 VARCHAR(10) NOT NULL, -- 对应示例中的01、03这类第一个比较运算符
    operator_2 VARCHAR(10) NOT NULL, -- 对应示例中的1这类附加信息
    FOREIGN KEY (ref_id) REFERENCES reference_records(ref_id)
);

每一组关联数据（比如4217558;01;1）对应关联表中的一行，无论关联项数量多少，只需新增行即可，完全适配长度不固定的问题。

3. 数据插入示例 #

针对你提供的示例数据：

3500312;09;4217558;01;1;3780576;01;1;3909811;03;1;3909952;03;1;3911386;05;1;3909343;06;1;3909604;07;1;3908651;08;1

• 先插入主表数据：

INSERT INTO reference_records (ref_id, ref_type) VALUES ('3500312', '09');

• 再插入关联表数据（每3个值为一组）：

INSERT INTO related_records (ref_id, related_value, operator_1, operator_2)
VALUES 
('3500312', '4217558', '01', '1'),
('3500312', '3780576', '01', '1'),
('3500312', '3909811', '03', '1'),
('3500312', '3909952', '03', '1'),
('3500312', '3911386', '05', '1'),
('3500312', '3909343', '06', '1'),
('3500312', '3909604', '07', '1'),
('3500312', '3908651', '08', '1');

优势说明 #

• 完全适配变长数据：无论每个参考值对应多少关联项，只需在关联表新增行，无需修改表结构。
• 查询效率高：通过主表ref_id可快速关联查询所有对应关联数据，支持索引优化。
• 数据维护方便：新增、删除关联项仅操作单行数据，不会影响其他记录。

备选方案：JSON类型字段（非推荐） #

如果你的数据库支持JSON类型（如MySQL 5.7+、PostgreSQL），也可将关联数据存为JSON数组放在主表的一个字段中：

CREATE TABLE reference_records (
    ref_id VARCHAR(20) PRIMARY KEY,
    ref_type VARCHAR(10) NOT NULL,
    related_data JSON NOT NULL
);

插入示例：

INSERT INTO reference_records VALUES ('3500312', '09', '[
    {"related_value":"4217558","operator_1":"01","operator_2":"1"},
    {"related_value":"3780576","operator_1":"01","operator_2":"1"}
]');

但该方案查询、过滤关联数据时效率较低，且难以做数据校验，仅适合关联数据无需频繁查询的场景。

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