表单数据明细表设计

TDuck 表单数据明细表 (`fm_user_form_data_detail_x`) 技术设计文档

1. 背景与设计初衷

在 TDuck 低代码表单/问卷/考试系统中，用户提交的动态表单数据存储在 fm_user_form_data 主表中的 original_data 字段（JSON 格式）。

1.1 纯 JSON 存储的痛点

复杂过滤性能差：若直接对 JSON 字段进行检索（如查找“年龄 > 25”且“城市 = 杭州”的提交），需要使用数据库的 JSON 抽取函数（如 MySQL JSON_EXTRACT），无法有效利用 B+ 树索引，引发全表扫描。
聚合统计成本高：在进行分类占比、平均分计算、数值求和、时间趋势分析等报表统计时，反序列化大量大体积 JSON 数据会消耗巨大系统 CPU 和内存带宽。
AI 智能查询（Text-to-SQL）困难：大模型对动态 Schema 的 JSON 结构的生成准确率较低，很难生成高效且标准的跨层级 JSON 查询语句。

1.2 明细表 (`fm_user_form_data_detail_x`) 的设计目标

为了解决上述痛点，TDuck 引入了EAV 扩展模型（Entity-Attribute-Value）与强类型列相结合的明细表结构：

解构拉平（Data Flattening）：将 JSON 中的每一个组件/题目选项拆解为明细行。
强类型化存储：按文本、数值、日期、得分等强类型存储在独立列中，提供极高效率的索引扫描。
物理分表隔离：按表单 formKey 进行 Hash 散列分表（_1 到 _6），避免单表行数爆炸。

2. 表结构设计 (Schema Design)

明细表包含 6 张同构物理分表：fm_user_form_data_detail_1 ~ fm_user_form_data_detail_6。

2.1 建表语句 (MySQL 示例)

CREATE TABLE `fm_user_form_data_detail_1` (
  `id`             bigint                                                        NOT NULL AUTO_INCREMENT COMMENT '主键',
  `data_id`        bigint                                                        NOT NULL COMMENT '表单数据ID（关联 fm_user_form_data.id）',
  `form_key`       varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '表单Key',
  `field_key`      varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字段/题目组件Key',
  `field_type`     tinyint                                                       NOT NULL COMMENT '字段组件类型编码(FormItemTypeCodeEnum)',
  `field_value`    varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '字段原始编码/选项值',
  `field_text`     varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字段显示文本(短文本，支持前缀/等值索引)',
  `field_text_full` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci        DEFAULT NULL COMMENT '字段完整显示文本(超长文本/多行文本)',
  `field_number`   bigint                                                        DEFAULT NULL COMMENT '数值类型字段值(用于数字、金额、评分等)',
  `field_datetime` datetime                                                      DEFAULT NULL COMMENT '日期时间类型字段值',
  `field_score`    decimal(10, 2)                                                DEFAULT NULL COMMENT '考试/打分表单中该项得分',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_data_field` (`data_id`, `field_key`) USING BTREE,
  KEY `idx_form_data_field` (`form_key`, `data_id`, `field_key`) USING BTREE,
  KEY `idx_form_field_cover` (`form_key`, `field_key`, `data_id`, `field_text`, `field_number`, `field_datetime`) USING BTREE,
  KEY `idx_form_field_number` (`form_key`, `field_key`, `field_number`) USING BTREE,
  KEY `idx_form_field_datetime` (`form_key`, `field_key`, `field_datetime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='表单字段明细表';

2.2 字段说明与存储策略

字段名	类型	是否必填	说明与存储策略
`id`	`bigint`	是	主键ID (支持数据库自增或全局雪花ID)
`data_id`	`bigint`	是	提交的主数据记录ID，关联 `fm_user_form_data.id`
`form_key`	`varchar(100)`	是	表单唯一标识，作为分片路由键与查询过滤基准
`field_key`	`varchar(100)`	是	题目的组件ID (例如 `field_1690000000000`)
`field_type`	`tinyint`	是	题目组件类型映射编码 (参见 `FormItemTypeCodeEnum`，如 1:Input, 3:Number, 10:Radio, 12:Date 等)
`field_value`	`varchar(255)`	否	字段原始选项 Key，如单选/多选/下拉框的 option key
`field_text`	`varchar(500)`	是	格式化后的短显示文本。若超过 100~500 字符会被截断，专门用于索引与匹配查询
`field_text_full`	`text`	否	完整原始文本，用于富文本、多行文本或极长回答的展示
`field_number`	`bigint`	否	转换为整数的数值列（如金额*100、评分、数字框）。专门支持区间检索 `<, >, BETWEEN` 与 `SUM/AVG`
`field_datetime`	`datetime`	否	日期/时间标准格式化值，专门支持按年/月/日区间筛选与时间趋势统计
`field_score`	`decimal(10,2)`	否	考试场景或打分表单中，该题目的实际得分

3. 物理分表与路由策略 (Sharding & Routing Logic)

系统固定配置 TABLE_COUNT = 6 张分表。

3.1 分表计算公式

在后端服务代码 (UserFormDataDetailServiceImpl) 中实现表名计算算法：

$$ \text{TableIndex} = |\text{hashCode}(\text{formKey})| \pmod 6 + 1 $$

实际物理表名拼接公式为：fm_user_form_data_detail_ + TableIndex。

3.2 策略优势

单表单内聚：同一个 formKey 的所有数据及答题明细必定写入同物理分表中。
无需跨表 JOIN：表单内的所有统计分析、多条件交集/并集筛选均可在单物理表中以索引高效完成。
抗单表膨胀：即使系统总答题数据达到千万/亿级，明细数据也会均匀分散在 6 张表中，有效维持索引 B+ 树的高度。

4. 索引设计与查询优化 (Index Strategy)

针对明细表高频出现的查询模式，精心设计了复合索引结构：

4.1 索引设计矩阵

idx_data_field (data_id, field_key)
- 适用场景：根据数据 ID 查看某条具体记录的所有明细，或更新/删除某条数据的明细记录。
idx_form_data_field (form_key, data_id, field_key)
- 适用场景：按表单维度过滤特定数据集合中的字段明细。
idx_form_field_cover (form_key, field_key, data_id, field_text, field_number, field_datetime)
- 适用场景：覆盖索引 (Covering Index)。包含核心查询与返回列，使得常用条件的 SELECT 查询无需二次“回表”读取主键索引，显著提升 IO 性能。
idx_form_field_number (form_key, field_key, field_number)
- 适用场景：数值类型组件的范围过滤（如 field_number >= 80）与 SUM(field_number)、AVG(field_number) 等聚合分析。
idx_form_field_datetime (form_key, field_key, field_datetime)
- 适用场景：时间组件的区间检索（如按日、周、月统计提交趋势）。

5. 数据抽取与同步机制 (Data Processing Pipeline)

明细数据并不是由前端直接写入，而是通过后端的解构提取器处理后落库。

+---------------------------------------------------------+
|                    用户提交表单数据                     |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|               保存主表 fm_user_form_data                 |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|            触发 FormDataExtractorManager                |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|             按组件类型分流至各子 Extractor               |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|           解析提取文本 / 数值 / 日期 / 得分明细          |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|          组装 UserFormDataDetailEntity 列表             |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|            根据 formKey Hash 计算目标物理分表            |
+----------------------------+----------------------------+
                             |
                             v
+---------------------------------------------------------+
|           批量 Batch Insert (batchSize=500)             |
+---------------------------------------------------------+

5.1 核心同步逻辑

新增数据：主表写完后，调用 extractAndSaveDetails 方法抽取 JSON 数据并批量写入对应分表。
修改数据：调用 updateDetails 方法，采取先删后插 (Delete-then-Insert) 的幂等策略，确保复杂嵌套结构变更不会产生残留孤立数据。
删除数据：删除表单提交记录时，依据 form_key 路由计算对应物理分表，精准清除对应的明细行（DELETE FROM fm_user_form_data_detail_x WHERE data_id = ?）。
批量写性能优化：saveBatchDetails 采用每 500 条分批插入，防止 SQL 语句超出 max_allowed_packet 并降低事务锁冲突。

6. 典型应用场景与查询示例

6.1 场景一：多条件交叉筛选 (例如：搜索 "满意度 >= 4分" 且 "部门 = 技术部")

SELECT data_id 
FROM fm_user_form_data_detail_2
WHERE form_key = 'fk_123456'
  AND (
    (field_key = 'satisfaction_field' AND field_number >= 4)
    OR
    (field_key = 'dept_field' AND field_text = '技术部')
  )
GROUP BY data_id
HAVING COUNT(DISTINCT field_key) = 2;

6.2 场景二：AI Text-to-SQL 自然语言统计 (智能分析)

AiQueryService 可直接基于标准 SQL 对 fm_user_form_data_detail_x 进行图表聚合分析：

-- 统计各选项的占比分布
SELECT field_text AS option_name, COUNT(*) AS count
FROM fm_user_form_data_detail_2
WHERE form_key = 'fk_123456' AND field_key = 'choice_field'
GROUP BY field_text
ORDER BY count DESC;

7. 多数据库兼容性设计

为了保证 TDuck 在标准企业级私有部署（MySQL、KingbaseES 人大金仓、Dameng 达梦、PostgreSQL 等）下的兼容性：

纯 ANSI SQL 语法：不使用特定的 JSON 运算符或特定厂商独有函数。
数据类型映射适配：
- MySQL: bigint, varchar, datetime, decimal
- KingbaseES / Dameng: 大小写双引号转义兼容及 TIMESTAMP / NUMERIC 类型自动映射。

8. 总结与演进方向

fm_user_form_data_detail_x 明细表设计平衡了低代码表单 Schemaless 的灵活扩展性与关系型数据库强类型索引的高效查询性能。

优势：查询性能大幅提升、天然支持复杂的交叉过滤、无缝对接大模型 AI 分析与高并发复杂报表统计。
后期演进：当表单总数量激增时，可扩展 TABLE_COUNT（如扩容至 12/24 张），或对超期历史数据引入冷热数据归档机制。

官方文档·

约 17 分钟阅读 (6636 字)

最后更新于 2026-08-13 12:55:23

这篇文章对您有帮助吗？

您的反馈将帮助我们不断改进平台技术文档质量

表单数据同步到Mongdb

项目概览