将患者就诊电子表格数据导入MySQL并动态构建ICD10关联链接

一、核心数据库表设计 #

基于需求，需要三个核心表实现患者、ICD10编码的关联，同时保证诊断数据的去重存储：

1. 患者表（patients） #

存储患者基础标识信息，确保病历号（mrn）唯一：

CREATE TABLE patients (
    mrn VARCHAR(50) PRIMARY KEY COMMENT '患者唯一病历号',
    -- 可按需添加姓名、性别等其他患者字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. ICD10编码表（icd10_codes） #

集中存储全量ICD10编码，避免重复存储编码信息：

CREATE TABLE icd10_codes (
    icd10_code VARCHAR(10) PRIMARY KEY COMMENT 'ICD10诊断编码',
    description TEXT COMMENT '诊断描述（可选，可后续补充完整）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 患者-诊断关联表（patient_icd10_relations） #

建立患者与诊断的关联，通过唯一索引保证同一患者的同一诊断仅存储一次：

CREATE TABLE patient_icd10_relations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mrn VARCHAR(50) NOT NULL,
    icd10_code VARCHAR(10) NOT NULL,
    FOREIGN KEY (mrn) REFERENCES patients(mrn),
    FOREIGN KEY (icd10_code) REFERENCES icd10_codes(icd10_code),
    UNIQUE KEY idx_mrn_icd10 (mrn, icd10_code) -- 核心约束：防止同一患者重复关联同一诊断
);

二、历史CSV数据导入流程 #

1. 创建临时导入表 #

先创建与CSV结构匹配的临时表，暂存原始就诊数据：

CREATE TABLE temp_encounters (
    mrn VARCHAR(50),
    dos DATE COMMENT '服务日期',
    diag1 VARCHAR(10),
    diag2 VARCHAR(10),
    diag3 VARCHAR(10),
    diag4 VARCHAR(10),
    diag5 VARCHAR(10),
    diag6 VARCHAR(10),
    diag7 VARCHAR(10),
    diag8 VARCHAR(10),
    diag9 VARCHAR(10),
    diag10 VARCHAR(10),
    diag11 VARCHAR(10),
    diag12 VARCHAR(10)
);

2. 导入CSV到临时表 #

用MySQL自带的LOAD DATA INFILE高效导入数十万行数据（比PHP逐行读取性能更高）：

LOAD DATA INFILE '/path/to/your/encounters.csv'
INTO TABLE temp_encounters
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 忽略CSV表头行

PHP环境中可通过PDO或mysqli调用该SQL语句执行导入。

3. 填充ICD10编码表 #

从临时表的所有诊断列提取不重复编码，插入到icd10_codes（自动跳过已存在的编码）：

INSERT INTO icd10_codes (icd10_code)
SELECT DISTINCT diag_code
FROM (
    SELECT diag1 AS diag_code FROM temp_encounters WHERE diag1 IS NOT NULL AND diag1 != ''
    UNION ALL
    SELECT diag2 AS diag_code FROM temp_encounters WHERE diag2 IS NOT NULL AND diag2 != ''
    UNION ALL
    SELECT diag3 AS diag_code FROM temp_encounters WHERE diag3 IS NOT NULL AND diag3 != ''
    UNION ALL
    SELECT diag4 AS diag_code FROM temp_encounters WHERE diag4 IS NOT NULL AND diag4 != ''
    UNION ALL
    SELECT diag5 AS diag_code FROM temp_encounters WHERE diag5 IS NOT NULL AND diag5 != ''
    UNION ALL
    SELECT diag6 AS diag_code FROM temp_encounters WHERE diag6 IS NOT NULL AND diag6 != ''
    UNION ALL
    SELECT diag7 AS diag_code FROM temp_encounters WHERE diag7 IS NOT NULL AND diag7 != ''
    UNION ALL
    SELECT diag8 AS diag_code FROM temp_encounters WHERE diag8 IS NOT NULL AND diag8 != ''
    UNION ALL
    SELECT diag9 AS diag_code FROM temp_encounters WHERE diag9 IS NOT NULL AND diag9 != ''
    UNION ALL
    SELECT diag10 AS diag_code FROM temp_encounters WHERE diag10 IS NOT NULL AND diag10 != ''
    UNION ALL
    SELECT diag11 AS diag_code FROM temp_encounters WHERE diag11 IS NOT NULL AND diag11 != ''
    UNION ALL
    SELECT diag12 AS diag_code FROM temp_encounters WHERE diag12 IS NOT NULL AND diag12 != ''
) AS all_diags
ON DUPLICATE KEY UPDATE icd10_code = icd10_code; -- 已存在的编码不做更新

4. 同步患者表 #

提取临时表中的唯一病历号，插入到patients表：

INSERT INTO patients (mrn)
SELECT DISTINCT mrn FROM temp_encounters
ON DUPLICATE KEY UPDATE mrn = mrn;

5. 建立患者与诊断的去重关联 #

将临时表中的诊断数据展开并去重，插入到关联表，利用唯一索引自动跳过重复关联：

INSERT INTO patient_icd10_relations (mrn, icd10_code)
SELECT DISTINCT te.mrn, ad.diag_code
FROM temp_encounters te
JOIN (
    SELECT diag1 AS diag_code FROM temp_encounters WHERE diag1 IS NOT NULL AND diag1 != ''
    UNION ALL
    SELECT diag2 AS diag_code FROM temp_encounters WHERE diag2 IS NOT NULL AND diag2 != ''
    UNION ALL
    SELECT diag3 AS diag_code FROM temp_encounters WHERE diag3 IS NOT NULL AND diag3 != ''
    UNION ALL
    SELECT diag4 AS diag_code FROM temp_encounters WHERE diag4 IS NOT NULL AND diag4 != ''
    UNION ALL
    SELECT diag5 AS diag_code FROM temp_encounters WHERE diag5 IS NOT NULL AND diag5 != ''
    UNION ALL
    SELECT diag6 AS diag_code FROM temp_encounters WHERE diag6 IS NOT NULL AND diag6 != ''
    UNION ALL
    SELECT diag7 AS diag_code FROM temp_encounters WHERE diag7 IS NOT NULL AND diag7 != ''
    UNION ALL
    SELECT diag8 AS diag_code FROM temp_encounters WHERE diag8 IS NOT NULL AND diag8 != ''
    UNION ALL
    SELECT diag9 AS diag_code FROM temp_encounters WHERE diag9 IS NOT NULL AND diag9 != ''
    UNION ALL
    SELECT diag10 AS diag_code FROM temp_encounters WHERE diag10 IS NOT NULL AND diag10 != ''
    UNION ALL
    SELECT diag11 AS diag_code FROM temp_encounters WHERE diag11 IS NOT NULL AND diag11 != ''
    UNION ALL
    SELECT diag12 AS diag_code FROM temp_encounters WHERE diag12 IS NOT NULL AND diag12 != ''
) AS ad ON 
    (te.diag1 = ad.diag_code) OR
    (te.diag2 = ad.diag_code) OR
    (te.diag3 = ad.diag_code) OR
    (te.diag4 = ad.diag_code) OR
    (te.diag5 = ad.diag_code) OR
    (te.diag6 = ad.diag_code) OR
    (te.diag7 = ad.diag_code) OR
    (te.diag8 = ad.diag_code) OR
    (te.diag9 = ad.diag_code) OR
    (te.diag10 = ad.diag_code) OR
    (te.diag11 = ad.diag_code) OR
    (te.diag12 = ad.diag_code)
ON DUPLICATE KEY UPDATE id = id; -- 已存在的关联不做更新

三、查询患者去重诊断列表 #

以查询病历号123的患者为例，执行以下SQL即可得到去重后的诊断码：

SELECT ic.icd10_code, ic.description
FROM patient_icd10_relations pir
JOIN icd10_codes ic ON pir.icd10_code = ic.icd10_code
WHERE pir.mrn = '123'
ORDER BY ic.icd10_code;

返回结果会自动去重，符合需求中的输出格式。

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