如何在MySQL中根据动态字符串的列渲染对应值

问题说明 #

数据表的SKU列存储着以-分隔的动态字符串，每个片段格式为[表别名].[列名]，需要将这些片段对应的关联表字段实际值提取出来，拼接成指定格式的SKU内容。

现有查询语句 #

SELECT d.device_type_name,a.serial, d.sku
FROM anticipated_device a 
JOIN inventory_device_attribute i on a.serial = i.serial
LEFT JOIN sku_by_device d on a.device_type_id = d.id
LEFT JOIN color c on i.color_id = c.id
LEFT JOIN grade g on i.verified_condition_id = g.id
LEFT JOIN phone_model pm on i.phone_model = pm.model
LEFT JOIN carrier_lock cl on i.carrier_lock_id = cl.id
LEFT JOIN computer_model cm on i.computer_model = cm.model
LEFT JOIN computer_year cy on i.computer_year = cy.year
LEFT JOIN screen_size ss on i.screen_size = ss.size
LEFT JOIN ghz gh on i.ghz = gh.ghz 
LEFT JOIN processor pr on i.processor = pr.processor
LEFT JOIN ram ra on i.ram = ra.ram 
LEFT JOIN storage st on i.storage_size = st.storage and i.storage_type=st.storage_type
LEFT JOIN tablet_model tm on i.tablet_gen = tm.model
LEFT JOIN tablet_connectivity tc on i.tablet_connectivity = tc.connectivity
LEFT JOIN band_color bc on i.band_color = bc.color
LEFT JOIN manufacturer mn on a.manufacturer_id = mn.id
LEFT JOIN watch_series ws on i.watch_series = ws.series
LEFT JOIN watch_size wsi on i.watch_size = wsi.size
LEFT JOIN model m on i.non_device_model_id = m.id;

示例数据 #

期望输出 #

phone   - 546412 - Iphone-XS-512GB-Gry-UNL
Laptop  - 545646 - McbA-E15-13-1.6-i5-8-128S-Slv

解决方案 #

方案1：应用层处理（推荐） #

因为SKU的字段组合是动态的，应用层处理更灵活可控：

• 执行现有SQL查询，获取包含device_type_name、serial和原始sku字段的结果集。
• 将每条记录的所有关联表字段值存入键值对结构（如字典），键为[表别名].[列名]格式。
• 对每条记录的原始sku按-拆分，遍历片段从键值对中取出对应值，用-拼接成完整SKU值。
• 最后组合成设备类型 - 序列号 - 拼接后的SKU值的格式输出。

方案2：数据库存储过程/函数（以MySQL为例） #

如果必须在数据库层面处理，可编写存储过程通过动态SQL实现：

DELIMITER //
CREATE PROCEDURE GenerateFormattedSKU()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dev_type VARCHAR(50);
    DECLARE serial VARCHAR(50);
    DECLARE raw_sku VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT d.device_type_name,a.serial, d.sku FROM anticipated_device a JOIN inventory_device_attribute i on a.serial = i.serial LEFT JOIN sku_by_device d on a.device_type_id = d.id LEFT JOIN color c on i.color_id = c.id LEFT JOIN grade g on i.verified_condition_id = g.id LEFT JOIN phone_model pm on i.phone_model = pm.model LEFT JOIN carrier_lock cl on i.carrier_lock_id = cl.id LEFT JOIN computer_model cm on i.computer_model = cm.model LEFT JOIN computer_year cy on i.computer_year = cy.year LEFT JOIN screen_size ss on i.screen_size = ss.size LEFT JOIN ghz gh on i.ghz = gh.ghz LEFT JOIN processor pr on i.processor = pr.processor LEFT JOIN ram ra on i.ram = ra.ram LEFT JOIN storage st on i.storage_size = st.storage and i.storage_type=st.storage_type LEFT JOIN tablet_model tm on i.tablet_gen = tm.model LEFT JOIN tablet_connectivity tc on i.tablet_connectivity = tc.connectivity LEFT JOIN band_color bc on i.band_color = bc.color LEFT JOIN manufacturer mn on a.manufacturer_id = mn.id LEFT JOIN watch_series ws on i.watch_series = ws.series LEFT JOIN watch_size wsi on i.watch_size = wsi.size LEFT JOIN model m on i.non_device_model_id = m.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO dev_type, serial, raw_sku;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 拆分原始SKU为字段列表
        SET @field_list = REPLACE(raw_sku, '-', ',');
        -- 构造动态查询获取拼接后的SKU值
        SET @sql = CONCAT('SELECT CONCAT_WS("-", ', @field_list, ') INTO @formatted_sku FROM anticipated_device a JOIN inventory_device_attribute i on a.serial = "', serial, '" LEFT JOIN color c on i.color_id = c.id LEFT JOIN grade g on i.verified_condition_id = g.id LEFT JOIN phone_model pm on i.phone_model = pm.model LEFT JOIN carrier_lock cl on i.carrier_lock_id = cl.id LEFT JOIN computer_model cm on i.computer_model = cm.model LEFT JOIN computer_year cy on i.computer_year = cy.year LEFT JOIN screen_size ss on i.screen_size = ss.size LEFT JOIN ghz gh on i.ghz = gh.ghz LEFT JOIN processor pr on i.processor = pr.processor LEFT JOIN ram ra on i.ram = ra.ram LEFT JOIN storage st on i.storage_size = st.storage and i.storage_type=st.storage_type LEFT JOIN tablet_model tm on i.tablet_gen = tm.model LEFT JOIN tablet_connectivity tc on i.tablet_connectivity = tc.connectivity LEFT JOIN band_color bc on i.band_color = bc.color LEFT JOIN manufacturer mn on a.manufacturer_id = mn.id LEFT JOIN watch_series ws on i.watch_series = ws.series LEFT JOIN watch_size wsi on i.watch_size = wsi.size LEFT JOIN model m on i.non_device_model_id = m.id WHERE a.serial = "', serial, '"');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 输出指定格式结果
        SELECT CONCAT(dev_type, ' - ', serial, ' - ', @formatted_sku) AS formatted_result;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

调用存储过程：

CALL GenerateFormattedSKU();

注意：存储过程需根据实际数据库语法调整，生产环境要对serial等参数做转义处理，避免SQL注入风险。

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