如何将含大量PERSON_X列的文件导入MySQL并统计值频次

方法1：预处理文本文件后导入 #

先通过脚本把每行的PERSON_X列统计出YES、NO、N/A的数量，生成结构简化的新文件，再导入MySQL，这是最省心的方案。

示例Python脚本 #

import csv

# 替换为你的实际文件路径
input_file = "source_data.txt"
output_file = "processed_data.txt"

with open(input_file, 'r', newline='', encoding='utf-8') as infile, \
     open(output_file, 'w', newline='', encoding='utf-8') as outfile:
    reader = csv.reader(infile, delimiter='\t')
    writer = csv.writer(outfile, delimiter='\t')
    
    # 处理表头
    header = next(reader)
    # 定位PERSON_X列的起始位置（info列之后）
    person_col_start = header.index('info') + 1
    # 生成新表头
    new_header = header[:person_col_start] + ['PERSON_YES', 'PERSON_NO', 'PERSON_N/A']
    writer.writerow(new_header)
    
    # 逐行处理数据
    for row in reader:
        # 提取基础字段
        base_fields = row[:person_col_start]
        # 统计PERSON_X列的各值数量
        person_values = row[person_col_start:]
        yes_count = person_values.count('YES')
        no_count = person_values.count('NO')
        na_count = person_values.count('N/A')
        # 写入新行
        writer.writerow(base_fields + [yes_count, no_count, na_count])

处理完成后，直接导入简化后的文件：

-- 创建目标表
CREATE TABLE target_table (
    item VARCHAR(255),
    id INT,
    info TEXT,
    PERSON_YES INT,
    PERSON_NO INT,
    PERSON_NA INT
);

-- 导入预处理后的文件
LOAD DATA INFILE '/path/to/processed_data.txt'
INTO TABLE target_table
FIELDS TERMINATED BY '\t'
IGNORE 1 LINES;

方法2：MySQL临时表中转 #

如果不想用外部脚本，可先将原始文件导入临时表，再通过动态SQL统计生成目标表。

步骤1：创建并导入临时表 #

先通过awk提取文件表头，自动生成临时表的建表语句：

# 提取表头并生成列定义
head -n 1 source_data.txt | awk -F '\t' '{
    print "CREATE TEMPORARY TABLE temp_table (";
    print "  item VARCHAR(255),";
    print "  id INT,";
    print "  info TEXT,";
    for(i=4;i<=NF;i++){
        print "  " $i " VARCHAR(10),"
    }
    print ");"
}' > create_temp_table.sql

执行生成的SQL文件创建临时表，再导入原始数据：

LOAD DATA INFILE '/path/to/source_data.txt'
INTO TABLE temp_table
FIELDS TERMINATED BY '\t'
IGNORE 1 LINES;

步骤2：动态统计生成目标表 #

-- 创建目标表
CREATE TABLE target_table (
    item VARCHAR(255),
    id INT,
    info TEXT,
    PERSON_YES INT,
    PERSON_NO INT,
    PERSON_NA INT
);

-- 生成动态统计SQL
SET @sql = (
    SELECT CONCAT(
        'INSERT INTO target_table SELECT item, id, info, ',
        GROUP_CONCAT(CONCAT('SUM(CASE WHEN ', column_name, ' = ''YES'' THEN 1 ELSE 0 END)') SEPARATOR ' + '),
        ' AS PERSON_YES, ',
        GROUP_CONCAT(CONCAT('SUM(CASE WHEN ', column_name, ' = ''NO'' THEN 1 ELSE 0 END)') SEPARATOR ' + '),
        ' AS PERSON_NO, ',
        GROUP_CONCAT(CONCAT('SUM(CASE WHEN ', column_name, ' = ''N/A'' THEN 1 ELSE 0 END)') SEPARATOR ' + '),
        ' AS PERSON_NA FROM temp_table GROUP BY item, id, info'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'temp_table'
      AND column_name LIKE 'PERSON_%'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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