phpMyAdmin中MySQL查询Count(*)列导出CSV/Excel异常求助

在phpMyAdmin中执行以下MySQL查询时，界面内最后一列# of Claims显示的数据完全正确，但将结果导出为CSV/Excel后，该列所有行都显示同一个固定数值（例如42345）。

查询语句：

SELECT 
     PATIENT_INSURANCE_ID
        ,PATIENT_ID
        ,(SELECT FIRST_NAME FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'First Name'
        ,(SELECT LAST_NAME FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'Last Name'
        ,(SELECT ORG_ID FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'ORG ID'
        ,INSURANCE_ID
        ,GROUP_ID
        ,INSURANCE_TYPE
        ,INSURANCE_MASTER_ID
        ,(SELECT INSURANCE_NAME FROM PATIENT_INSURANCE_MASTER_LIST WHERE PATIENT_INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID) AS 'Insurane Name'
        ,CURRENT
        ,(SELECT COUNT(*) FROM CLAIM WHERE INSURANCE_ID IN (SELECT PATIENT_INSURANCE_ID FROM PATIENT_INSURANCE WHERE INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID)) AS '# of Claims'
FROM PATIENT_INSURANCE AS PI
WHERE INSURANCE_MASTER_ID IN (SELECT PATIENT_INSURANCE_MASTER_ID FROM PATIENT_INSURANCE_MASTER_LIST WHERE ORG_ID ='10000797' AND INSURANCE_NAME LIKE '%delete%')

核心问题是phpMyAdmin导出模块的别名解析Bug：列别名# of Claims包含特殊字符（#和空格），导出时系统无法正确识别该别名对应的列数据，转而重复填充了缓存的聚合值。同时原查询大量使用关联子查询，导致结果集元数据处理复杂，进一步加剧了导出异常。

1. 修改列别名，规避特殊字符 #

将# of Claims改为无特殊符号的别名（如num_claims），导出后再手动改回原名称：

SELECT 
     PATIENT_INSURANCE_ID
        ,PATIENT_ID
        ,(SELECT FIRST_NAME FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'First Name'
        ,(SELECT LAST_NAME FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'Last Name'
        ,(SELECT ORG_ID FROM PATIENT WHERE PATIENT_ID = PI.PATIENT_ID) AS 'ORG ID'
        ,INSURANCE_ID
        ,GROUP_ID
        ,INSURANCE_TYPE
        ,INSURANCE_MASTER_ID
        ,(SELECT INSURANCE_NAME FROM PATIENT_INSURANCE_MASTER_LIST WHERE PATIENT_INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID) AS 'Insurance Name'
        ,CURRENT
        ,(SELECT COUNT(*) FROM CLAIM WHERE INSURANCE_ID IN (SELECT PATIENT_INSURANCE_ID FROM PATIENT_INSURANCE WHERE INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID)) AS num_claims
FROM PATIENT_INSURANCE AS PI
WHERE INSURANCE_MASTER_ID IN (SELECT PATIENT_INSURANCE_MASTER_ID FROM PATIENT_INSURANCE_MASTER_LIST WHERE ORG_ID ='10000797' AND INSURANCE_NAME LIKE '%delete%')

2. 重构查询，用JOIN替代关联子查询 #

原查询的关联子查询可替换为JOIN，既提升性能，也让结果集结构更清晰，避免导出时的元数据解析错误：

SELECT 
    PI.PATIENT_INSURANCE_ID,
    PI.PATIENT_ID,
    P.FIRST_NAME AS 'First Name',
    P.LAST_NAME AS 'Last Name',
    P.ORG_ID AS 'ORG ID',
    PI.INSURANCE_ID,
    PI.GROUP_ID,
    PI.INSURANCE_TYPE,
    PI.INSURANCE_MASTER_ID,
    PIML.INSURANCE_NAME AS 'Insurance Name',
    PI.CURRENT,
    C.num_claims AS '# of Claims'
FROM PATIENT_INSURANCE AS PI
JOIN PATIENT P ON P.PATIENT_ID = PI.PATIENT_ID
JOIN PATIENT_INSURANCE_MASTER_LIST PIML ON PIML.PATIENT_INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID
LEFT JOIN (
    SELECT 
        PI_CLAIM.INSURANCE_MASTER_ID,
        COUNT(*) AS num_claims
    FROM CLAIM
    JOIN PATIENT_INSURANCE PI_CLAIM ON CLAIM.INSURANCE_ID = PI_CLAIM.PATIENT_INSURANCE_ID
    GROUP BY PI_CLAIM.INSURANCE_MASTER_ID
) C ON C.INSURANCE_MASTER_ID = PI.INSURANCE_MASTER_ID
WHERE PI.INSURANCE_MASTER_ID IN (
    SELECT PATIENT_INSURANCE_MASTER_ID 
    FROM PATIENT_INSURANCE_MASTER_LIST 
    WHERE ORG_ID ='10000797' AND INSURANCE_NAME LIKE '%delete%'
)

3. 临时导出处理（无需修改查询） #

• 直接将phpMyAdmin界面中的查询结果复制粘贴到本地Excel表格，再保存为CSV格式
• 选择phpMyAdmin的「自定义导出」选项，确认勾选「包含列名」，并检查别名是否被正确识别

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