MySQL多列转多行IN查询：求简洁高效的实现方案

我查阅了资料，但未找到优质且简洁的解决方案。方案2、3、4均可正常运行，但SQL代码冗长繁琐，仅方案4执行速度快。一定存在更简洁的写法，我需要一段代码简洁（即简短）且执行高效的查询语句。

最初的错误尝试 #

我原本想写出如下简洁的查询，但该语句无法运行，因为子查询返回多列而非多行：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
    WHERE pd.ean IN (SELECT ean, ean2, ean3, ean4, ean5 FROM product_data WHERE ean = '3607343811835')

各方案测试情况 #

方案1（超时） #

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        
    WHERE pd.ean IN (
        
        SELECT ean
        FROM product_data 
        WHERE ean = '3607343811835' AND ean != ''
        
        UNION
        
        SELECT ean2
        FROM product_data 
        WHERE ean = '3607343811835' AND ean2 != ''
        
        UNION
        
        SELECT ean3
        FROM product_data 
        WHERE ean = '3607343811835' AND ean3 != ''
        
        UNION
        
        SELECT ean4
        FROM product_data 
        WHERE ean = '3607343811835' AND ean4 != ''
        
        UNION
        
        SELECT ean5
        FROM product_data 
        WHERE ean = '3607343811835' AND ean5 != ''
        )

• 子查询仅返回3行，耗时毫秒级，但整个查询超时（90秒）
• 对比：直接使用具体值的查询耗时仅毫秒级：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
        FROM price_data pd
            
        WHERE pd.ean IN (3607343811835, 0088300107407, 0088300607402)

规范化表结构测试（高效） #

按照建议新增alternative_eans表（仅含3行），该查询执行速度很快，但我仍想知道在现有表结构下是否存在更简洁的写法：

SELECT pd.ean, pd.stocklist, pd.stock_is_current
        FROM price_data pd
        WHERE pd.ean IN (SELECT alternative_eans FROM eans_to_more_eans WHERE main_ean = 3607343811835)

方案2（耗时约22秒） #

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        
    WHERE (pd.ean = (
        
        SELECT ean
        FROM product_data 
        WHERE ean = '3607343811835' AND ean != ''
        
) OR pd.ean = (
        
        SELECT ean2
        FROM product_data 
        WHERE ean = '3607343811835' AND ean2 != ''
        
) OR pd.ean = (
        
        SELECT ean3
        FROM product_data 
        WHERE ean = '3607343811835' AND ean3 != ''
        
) OR pd.ean = (
        
        SELECT ean4
        FROM product_data 
        WHERE ean = '3607343811835' AND ean4 != ''
        
) OR pd.ean = (
        
        SELECT ean5
        FROM product_data 
        WHERE ean = '3607343811835' AND ean5 != ''
    
))

方案3（耗时约22秒） #

SELECT pd.ean, pd.stocklist, pd.stock_is_current
    FROM price_data pd
        INNER JOIN product_data prod
            ON (pd.ean = prod.ean
            or pd.ean = prod.ean2
            or pd.ean = prod.ean3
            or pd.ean = prod.ean4
            or pd.ean = prod.ean5)
            AND pd.ean != ''
            
            
    WHERE (prod.ean = '3607343811835' or
                     prod.ean2 = '3607343811835' or
                   prod.ean3 = '3607343811835' or
                   prod.ean4 = '3607343811835' or
                   prod.ean5 = '3607343811835' )

方案4（高效但冗长，耗时<100ms） #

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean
FROM product_data 
WHERE ean = '3607343811835' AND ean != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean2
FROM product_data 
WHERE ean = '3607343811835' AND ean2 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean3
FROM product_data 
WHERE ean = '3607343811835' AND ean3 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean4
FROM product_data 
WHERE ean = '3607343811835' AND ean4 != ''
)

UNION ALL

SELECT pd.ean, pd.stocklist, pd.stock_is_current
FROM price_data pd

WHERE pd.ean = (

SELECT ean5
FROM product_data 
WHERE ean = '3607343811835' AND ean5 != ''
)

所有方案返回的结果集一致（仅顺序不同，无影响）。

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