一旦插入变量，MySQL 过程就会表现得很奇怪

Question

我在 MySQL 和我正在编写的过程中遇到了令人沮丧的时间，想看看其他人是否有任何关于正在发生的事情的线索。

下面的过程将动态地将表中的行转置为列。除了最后一个 WHERE 之外，它工作正常。声明。

如果我有以下任一 WHERE声明它工作正常。

WHERE t.company_id = v_company_id AND t.class = 'asset_property'

WHERE t.company_id IN (v_company_id) AND t.class = 'asset_property'

但是，如果我还想获取 company_id 可能为 1 或传入的任何值的记录，它会拒绝运行。对查询所做的唯一更改是“WHERE”语句。这些版本都不起作用。

WHERE (t.company_id = v_company_id OR t.company_id = 1) AND t.class = 'asset_property'

WHERE t.company_id IN (v_company_id, 1) AND t.class = 'asset_property'

WHERE t.company_id IN (161, 1) AND t.class = 'asset_property'

感谢更精通 MySQL 的人可以提供的任何帮助。我已尽我所能尝试调试，我似乎发现的唯一错误是当我尝试获取 t.company_id 时作为两个值之一，会出现一个通用错误: Invalid table name 。

完整的过程如下。

(参见更新 1)

最后，如果我生成上面的工作语句，则手动添加 t.company_id = 1 的行记录，它工作完美并创建以下查询。

(参见更新 1)

<小时/>

更新 1

为了让其他人可以模拟我所看到的内容，我构建了一个包含测试数据的测试数据库，以便您可以自己尝试。还删除了上面的一些代码以节省空间...

尝试在此处添加此内容超出了我的字符限制。我已将其上传到我的个人网站。 (如果这样做不安全，请告诉我!) Test DB

创建此数据库后，您可以运行以下一些过程和 SQL 语句来测试它并模拟我正在做的事情。

程序 fill_tableau_ap测试数据库中的程序将运行并创建一个包含数据的表(但缺少公司 1 记录)。

该过程生成以下 SQL:

CREATE TABLE dw_161.tableau_ap AS 
    SELECT a.id as "Asset ID (ap)", 
        max(case when ap.property_id = 2473 then ap.text_value else null end) as "Business Class" ,
        max(case when ap.property_id = 2294 then ap.date_value else null end) as "COI Date" ,
        max(case when ap.property_id = 2293 then ap.date_value else null end) as "Compliance Date" ,
        max(case when ap.property_id = 2291 then ap.text_value else null end) as "Fuel Type" ,
        max(case when ap.property_id = 2292 then ap.text_value else null end) as "Operator" ,
        max(case when ap.property_id = 2296 then ap.numeric_value else null end) as "PAX Capacity" ,
        max(case when ap.property_id = 2295 then ap.date_value else null end) as "Registration Expiry" ,
        max(case when ap.property_id = 2290 then ap.text_value else null end) as "Taxi Plate" ,
        max(case when ap.property_id = 2323 then ap.text_value else null end) as "VIN" 
    FROM dw_161.equipment AS a 
    JOIN dw_161.asset_properties ap ON ap.asset_id = a.id 
    JOIN dw_161.sites AS s on a.site_id = s.id 
    WHERE dw_161.s.company_id = 161 
    GROUP BY a.id;

如果我手动将公司 1 的单个项目添加到上述语句中，如下所示，我可以在数据库上运行它，它也可以工作。如果您还想在测试数据库上运行它，则如下所示。

CREATE TABLE dw_161.tableau_ap AS 
    SELECT a.id as "Asset ID (ap)", 
        max(case when ap.property_id = 2473 then ap.text_value else null end) as "Business Class" ,
        max(case when ap.property_id = 2294 then ap.date_value else null end) as "COI Date" ,
        max(case when ap.property_id = 2293 then ap.date_value else null end) as "Compliance Date" ,
        max(case when ap.property_id = 2291 then ap.text_value else null end) as "Fuel Type" ,
## Next field added manually from company 1 into the query
        max(case when ap.property_id = 213 then ap.numeric_value else null end) as "Odometer" ,
        max(case when ap.property_id = 2292 then ap.text_value else null end) as "Operator" ,
        max(case when ap.property_id = 2296 then ap.numeric_value else null end) as "PAX Capacity" ,
        max(case when ap.property_id = 2295 then ap.date_value else null end) as "Registration Expiry" ,
        max(case when ap.property_id = 2290 then ap.text_value else null end) as "Taxi Plate" ,
        max(case when ap.property_id = 2323 then ap.text_value else null end) as "VIN" 
    FROM dw_161.equipment AS a 
    JOIN dw_161.asset_properties ap ON ap.asset_id = a.id 
    JOIN dw_161.sites AS s on a.site_id = s.id 
    WHERE dw_161.s.company_id = 161 
    GROUP BY a.id;

因此我们可以得出结论，如果我能够正确生成语句，它将运行并工作。所以肯定是语句生成的问题。

将过程分为几个部分，后半部分将 Assets 属性列表输入前半部分。我认为这就是事情变得困惑的地方。具体来说，在程序的这一部分中:

    SELECT i.id as ap_id, item as ap_name, asset_property_data_type as ap_data_type
    FROM dw_161.config_items as i
    JOIN dw_161.config_tables as t on t.id = i.table_id
    WHERE t.company_id IN (v_company_id) AND t.class = 'asset_property'    
    ORDER BY ap_name

如果我取出变量 v_company_id 会发生什么？并手动输入我最终要查找的内容并运行它，如下所示？当然，它可以工作并提供所有 Assets 属性的列表。 (这里没有重复项。)

SELECT i.id as ap_id, item as ap_name, asset_property_data_type as ap_data_type
FROM dw_161.config_items as i
JOIN dw_161.config_tables as t on t.id = i.table_id
WHERE t.company_id IN (161,1) AND t.class = 'asset_property'    
ORDER BY ap_name

<小时/>

更新2

啊啊!!!在经历所有这些并确保其他人查看它时完全可重复时，我注意到其中一个字段被注册为 decimal当我们将数字和小数数据存储在名为 numeric_value 的单个字段中时，在数据库中在表中。这最终是我的问题，我似乎有 Rubber Ducked我。更改过程以将数据库中存储的任何字段转换为实际的列名称解决了我的问题!

这是已完成的程序，以防有人感兴趣。如果其他人需要的话，这是一段有趣的代码，用于将行转换为数据库中的列。

BEGIN

    SET  @sql = concat('DROP TABLE IF EXISTS dw_', v_company_id, '.tableau_ap;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET SESSION group_concat_max_len = 1000000;
    SET @sql = (select concat('CREATE TABLE dw_', v_company_id, '.tableau_ap AS SELECT a.id as "Asset ID (ap)", ',
            group_concat(
                concat('max(case when ap.property_id = ',ap_id, ' then ap.', ap_data_type, '_value else null end) as "', ap_name,'" ')
            ),
            'FROM dw_', v_company_id, '.equipment AS a ',
            'JOIN dw_', v_company_id, '.asset_properties ap ON ap.asset_id = a.id ',
            'JOIN dw_', v_company_id, '.sites AS s on a.site_id = s.id ',
            'WHERE dw_', v_company_id, '.s.company_id = ', v_company_id,' ',
            'GROUP BY a.id;'
            )
    from 
    (
        SELECT i.id as ap_id, item as ap_name, 
            (CASE asset_property_data_type
                WHEN 'numeric' THEN 'numeric'
                WHEN 'decimal' THEN 'numeric'
                WHEN 'calculated' THEN 'numeric'
                WHEN 'text' THEN 'text'
                WHEN 'date' THEN 'date'
                WHEN 'asset' THEN 'id'
                WHEN 'employee' THEN 'id'
                WHEN 'bar-code' THEN 'text'
                WHEN 'currency' THEN 'numeric'
                WHEN 'time' THEN 'date'
                WHEN 'duration' THEN 'date'
                WHEN 'serial' THEN 'text'
             END) AS ap_data_type
        FROM dw_161.config_items as i
        JOIN dw_161.config_tables as t on t.id = i.table_id
        WHERE t.company_id IN (v_company_id,1) AND t.class = 'asset_property'    
        ORDER BY ap_name) a
    )
    ;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END

Answer 1

请参阅原帖上面的更新 2。