MySQL存储数学公式并原生SQL动态计算的表结构设计咨询

该方案完全基于MySQL原生能力实现公式的结构化存储与动态计算，无需额外引入外部语言解析公式文本。

核心表结构设计 #

1. 常量配置表 formula_constants #

用于存储公式中用到的固定常量，避免硬编码：

CREATE TABLE formula_constants (
    constant_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '常量唯一ID',
    constant_name VARCHAR(64) NOT NULL UNIQUE COMMENT '常量名称',
    constant_value DECIMAL(18,6) NOT NULL COMMENT '常量数值',
    description VARCHAR(255) COMMENT '常量说明'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 公式元数据表 formula_metadata #

存储每个公式的基础信息：

CREATE TABLE formula_metadata (
    formula_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '公式唯一ID',
    formula_name VARCHAR(64) NOT NULL UNIQUE COMMENT '公式名称',
    result_column_name VARCHAR(64) NOT NULL COMMENT '计算结果输出字段名',
    is_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    description VARCHAR(255) COMMENT '公式用途说明'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 公式运算节点表 formula_operation_nodes #

核心存储表，将公式拆解为按优先级排列的运算节点，完全结构化存储无需保留公式文本：

CREATE TABLE formula_operation_nodes (
    node_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '节点唯一ID',
    formula_id INT UNSIGNED NOT NULL COMMENT '所属公式ID，关联formula_metadata.formula_id',
    execution_order INT UNSIGNED NOT NULL COMMENT '运算执行顺序，数值越小越先执行',
    left_operand_type ENUM('timing_column','constant','node_result') NOT NULL COMMENT '左操作数类型：时序表字段/常量/前置节点计算结果',
    left_operand_value VARCHAR(64) NOT NULL COMMENT '左操作数值：时序字段名/常量ID/前置节点ID',
    operator ENUM('+','-','*','/') NOT NULL COMMENT '运算符，可按需扩展',
    right_operand_type ENUM('timing_column','constant','node_result') NOT NULL COMMENT '右操作数类型',
    right_operand_value VARCHAR(64) NOT NULL COMMENT '右操作数值',
    FOREIGN KEY (formula_id) REFERENCES formula_metadata(formula_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

以你给出的示例公式result = (id_1 * some_stored_constant + id_2 * another_stored_constant) * id_5为例，可拆解为4个运算节点：

1. 执行顺序1：左操作数为时序字段id_1，运算符*，右操作数为常量some_stored_constant，结果存为节点1结果
2. 执行顺序2：左操作数为时序字段id_2，运算符*，右操作数为常量another_stored_constant，结果存为节点2结果
3. 执行顺序3：左操作数为节点1结果，运算符+，右操作数为节点2结果，结果存为节点3结果
4. 执行顺序4：左操作数为节点3结果，运算符*，右操作数为时序字段id_5，结果为公式最终输出

原生SQL动态计算实现 #

通过MySQL内置的预处理语句能力实现动态计算，示例存储过程如下：

DELIMITER //
CREATE PROCEDURE calc_formula_result(
    IN p_formula_id INT UNSIGNED,
    IN p_timing_table_name VARCHAR(64),
    IN p_time_filter VARCHAR(255) -- 时间过滤条件，如'record_time >= "2024-01-01"'
)
BEGIN
    DECLARE v_calc_expr TEXT DEFAULT '';
    DECLARE v_result_col VARCHAR(64);
    -- 获取结果字段名
    SELECT result_column_name INTO v_result_col FROM formula_metadata WHERE formula_id = p_formula_id AND is_enabled = 1;
    -- 按执行顺序拼接计算表达式
    SELECT GROUP_CONCAT(
        CONCAT(
            CASE left_operand_type
                WHEN 'timing_column' THEN left_operand_value
                WHEN 'constant' THEN (SELECT constant_value FROM formula_constants WHERE constant_id = left_operand_value)
                WHEN 'node_result' THEN CONCAT('@node_',left_operand_value)
            END,
            operator,
            CASE right_operand_type
                WHEN 'timing_column' THEN right_operand_value
                WHEN 'constant' THEN (SELECT constant_value FROM formula_constants WHERE constant_id = right_operand_value)
                WHEN 'node_result' THEN CONCAT('@node_',right_operand_value)
            END,
            ' INTO @node_', node_id, ';'
        ) ORDER BY execution_order SEPARATOR ' '
    ) INTO v_calc_expr FROM formula_operation_nodes WHERE formula_id = p_formula_id;
    -- 拼接最终查询SQL
    SET @final_sql = CONCAT(
        'SELECT *, @node_', (SELECT MAX(node_id) FROM formula_operation_nodes WHERE formula_id = p_formula_id), ' AS ', v_result_col,
        ' FROM ', p_timing_table_name,
        ' WHERE ', p_time_filter, ';'
    );
    -- 执行计算
    PREPARE stmt FROM CONCAT('SET ', v_calc_expr, ' ', @final_sql);
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

方案优势 #

• 完全SQL原生实现，不需要调用外部语言解析公式文本，性能损耗低
• 公式迭代不需要修改业务代码，仅需调整formula_operation_nodes中的节点配置即可生效
• 灵活支持时序表任意时间范围的计算，直接传入过滤条件即可批量生成结果
• 运算优先级完全由execution_order字段控制，不会出现四则运算顺序错误

注意事项 #

• 传入的p_time_filter和p_timing_table_name参数建议加白名单校验，避免SQL注入风险
• 如果需要支持更复杂的运算（比如幂、取余），可以直接扩展formula_operation_nodes的operator枚举值，同步修改存储过程的拼接逻辑即可

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