如何将MySQL慢查询结果中多行的query_text合并为单行？

问题 #

我有MySQL慢查询输出结果，包含query_id和query_text两列，列之间用|分隔，但query_text字段内容是多行格式（示例如下）。需要将query_text的多行内容合并为单行，尝试了以下awk命令但未成功，求解决办法。

原始输出示例 #

| query_id | query_text| 
| Select_table1_59711_9a3cf39f0a4a0fae | SELECT COUNT(*),COUNT(`stock_exchange`),APPROX_COUNT_DISTINCT(`stock_exchange`),COUNT(`display_domain`),APPROX_COUNT_DISTINCT(`display_domain`),COUNT(`products_and_services_lower`),APPROX_COUNT_DISTINCT(`products_and_services_lower`),COUNT(`nc_hash`),APPROX_COUNT_DISTINCT(`nc_hash`),COUNT(`naics`),APPROX_COUNT_DISTINCT(`naics`),COUNT(`slintel_sector`),APPROX_COUNT_DISTINCT(`slintel_sector`),COUNT(`employee_range`),APPROX_COUNT_DISTINCT(`employee_range`),COUNT(`cb_link`),APPROX_COUNT_DISTINCT(`cb_link`),COUNT(`fgic_quality_score`),APPROX_COUNT_DISTINCT(`fgic_quality_score`),COUNT(`annual_revenue`),APPROX_COUNT_DISTINCT(`annual_revenue`),COUNT(`revenue_range`),APPROX_COUNT_DISTINCT(`revenue_range`),COUNT(`facebook`),APPROX_COUNT_DISTINCT(`facebook`),COUNT(`youtube`),APPROX_COUNT_DISTINCT(`youtube`),COUNT(`industry_deprecated`),APPROX_COUNT_DISTINCT(`industry_deprecated`),COUNT(`mcd_name_exclusion_removed_date`),APPROX_COUNT_DISTINCT(`mcd_name_exclusion_removed_date`),COUNT(`industry_v2_ranked`),APPROX_COUNT_DISTINCT(`industry_v2_ranked`),COUNT(`franchisee_parent_id`),APPROX_COUNT_DISTINCT(`franchisee_parent_id`),COUNT(`dnc_hash`),APPROX_COUNT_DISTINCT(`dnc_hash`),COUNT(`is_display_quality`),APPROX_COUNT_DISTINCT(`is_display_quality`),COUNT(`usefulness_score`),APPROX_COUNT_DISTINCT(`usefulness_score`),COUNT(`industry_v2`),APPROX_COUNT_DISTINCT(`industry_v2`),COUNT(`is_mutable`),APPROX_COUNT_DISTINCT(`is_mutable`),COUNT(`fortune_1000`),APPROX_COUNT_DISTINCT(`fortune_1000`),COUNT(`employee_count`),APPROX_COUNT_DISTINCT(`employee_count`),COUNT(`num_country_records`),APPROX_COUNT_DISTINCT(`num_country_records`),COUNT(`preferred_id`),APPROX_COUNT_DISTINCT(`preferred_id`) FROM `table1_59711` OPTION (INTERPRETER_MODE=LLVM) /*stats_collection_query*/ |
| Select_table3_60867__et_al_d7a90531f56de418 | select
 id,
 name
 from
 table3
 where
 id in (^) |
| Select_country_39040 | SELECT
 table2.mid AS mid,
 table2.logo AS company_logo_url,
 table2.name AS name,
 table2.display_domain AS website,
 table2.employee_range AS employee_range,
 table2.industry_v2_ranked AS industry_v2_ranked,
 table2.city AS city,
 table2.state AS state,
 table2.country AS country,
 table2.phone AS phone_number,
 table2.products_and_services AS products_and_services,
 table2.naics AS naics,
 table2.sic AS sic,
 table2.slintel_company_type AS type,
 table2.year_founded AS year_founded,
 table2.total_funding_raised_range AS total_funding_raised_range,
 table2.revenue_range AS revenue_range,
 table2.linkedin AS linkedin,
 table2.facebook AS facebook,
 table2.twitter AS twitter,
 table2.usefulness_score AS usefulness_score
FROM table2 AS table2
WHERE
 table2.mid IN (
 SELECT
 s2_optimizer_hint_wrap.mid AS mid) | 

期望输出示例 #

| query_id | query_text| 
| Select_table1_59711_9a3cf39f0a4a0fae | SELECT COUNT(*),COUNT(`stock_exchange`),APPROX_COUNT_DISTINCT(`stock_exchange`),COUNT(`display_domain`),APPROX_COUNT_DISTINCT(`display_domain`),COUNT(`products_and_services_lower`),APPROX_COUNT_DISTINCT(`products_and_services_lower`),COUNT(`nc_hash`),APPROX_COUNT_DISTINCT(`nc_hash`),COUNT(`naics`),APPROX_COUNT_DISTINCT(`naics`),COUNT(`slintel_sector`),APPROX_COUNT_DISTINCT(`slintel_sector`),COUNT(`employee_range`),APPROX_COUNT_DISTINCT(`employee_range`),COUNT(`cb_link`),APPROX_COUNT_DISTINCT(`cb_link`),COUNT(`fgic_quality_score`),APPROX_COUNT_DISTINCT(`fgic_quality_score`),COUNT(`annual_revenue`),APPROX_COUNT_DISTINCT(`annual_revenue`),COUNT(`revenue_range`),APPROX_COUNT_DISTINCT(`revenue_range`),COUNT(`facebook`),APPROX_COUNT_DISTINCT(`facebook`),COUNT(`youtube`),APPROX_COUNT_DISTINCT(`youtube`),COUNT(`industry_deprecated`),APPROX_COUNT_DISTINCT(`industry_deprecated`),COUNT(`mcd_name_exclusion_removed_date`),APPROX_COUNT_DISTINCT(`mcd_name_exclusion_removed_date`),COUNT(`industry_v2_ranked`),APPROX_COUNT_DISTINCT(`industry_v2_ranked`),COUNT(`franchisee_parent_id`),APPROX_COUNT_DISTINCT(`franchisee_parent_id`),COUNT(`dnc_hash`),APPROX_COUNT_DISTINCT(`dnc_hash`),COUNT(`is_display_quality`),APPROX_COUNT_DISTINCT(`is_display_quality`),COUNT(`usefulness_score`),APPROX_COUNT_DISTINCT(`usefulness_score`),COUNT(`industry_v2`),APPROX_COUNT_DISTINCT(`industry_v2`),COUNT(`is_mutable`),APPROX_COUNT_DISTINCT(`is_mutable`),COUNT(`fortune_1000`),APPROX_COUNT_DISTINCT(`fortune_1000`),COUNT(`employee_count`),APPROX_COUNT_DISTINCT(`employee_count`),COUNT(`num_country_records`),APPROX_COUNT_DISTINCT(`num_country_records`),COUNT(`preferred_id`),APPROX_COUNT_DISTINCT(`preferred_id`) FROM `table1_59711` OPTION (INTERPRETER_MODE=LLVM) /*stats_collection_query*/ |
| Select_table3_60867__et_al_d7a90531f56de418 | select id, name from table3 where id in (^) |
| Select_country_39040 | SELECT table2.mid AS mid, table2.logo AS company_logo_url, table2.name AS name, table2.display_domain AS website, table2.employee_range AS employee_range, table2.industry_v2_ranked AS industry_v2_ranked, table2.city AS city, table2.state AS state, table2.country AS country, table2.phone AS phone_number, table2.products_and_services AS products_and_services, table2.naics AS naics, table2.sic AS sic, table2.slintel_company_type AS type, table2.year_founded AS year_founded, table2.total_funding_raised_range AS total_funding_raised_range, table2.revenue_range AS revenue_range, table2.linkedin AS linkedin, table2.facebook AS facebook, table2.twitter AS twitter, table2.usefulness_score AS usefulness_score FROM table2 AS table2 WHERE table2.mid IN ( SELECT mid AS mid) |

尝试的awk命令 #

BEGIN{RS="^|"; FS = "|"}NF>1{print gensub(/\n/," ","g")}

解决方案 #

你之前的命令问题在于行分隔符（RS）设置错误，且未正确识别记录的起始与结束标记。可以使用以下awk命令实现需求：

awk '
    BEGIN {
        FS = "|"
        in_record = 0
    }
    /^| / {
        if (in_record) {
            gsub(/\n+/, " ", query_text)
            gsub(/ +/, " ", query_text)
            printf("| %s | %s |\n", query_id, query_text)
        }
        query_id = $2
        gsub(/^ +| +$/, "", query_id)
        query_text = substr($0, index($0, $3))
        gsub(/^ +| +$/, "", query_text)
        in_record = 1
    }
    !/^| / {
        line = $0
        gsub(/^ +| +$/, "", line)
        query_text = query_text " " line
    }
    END {
        if (in_record) {
            gsub(/\n+/, " ", query_text)
            gsub(/ +/, " ", query_text)
            printf("| %s | %s |\n", query_id, query_text)
        }
    }
' input_file

命令说明 #

• BEGIN块：设置字段分隔符为|，初始化in_record标记用于判断是否正在处理一条记录。
• /^| / 匹配块：识别新记录的起始行（以| 开头）：
  • 若已在处理记录，先将收集的query_text中的换行替换为空格，合并多余空格后输出完整记录。
  • 提取当前行的query_id并去除前后空格，提取初始query_text部分并清理空格。
• !/^| / 匹配块：处理多行的query_text内容，将每行内容去除前后空格后追加到query_text。
• END块：处理最后一条未输出的记录，确保所有内容都被处理。

该命令会正确识别每条记录的起始，将多行query_text合并为单行，同时清理多余空格，输出符合要求的格式。

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