简介这份资源面向医疗信息化开发者、数据工程师及SNOMED CT术语集使用者提供在关系数据库中表示SNOMED CT的完整脚本方案解决术语版本导入、建库与查询的落地问题。包内共115个文件以64个SQL脚本为核心配合11个Python脚本、8个Markdown说明及Perl、Shell、批处理等辅助文件覆盖MySQL、PostgreSQL、MSSQL与Neo4j多种数据库的建表、填充与加载流程压缩包约434KB。已有1100人学习下载适合需要将RF2分发格式术语集迁移至本地数据库的中高级技术人员。读者可借助脚本快速完成术语数据建模、批量导入与图数据库更新校验并参考配置模板与排错脚本理解不同数据库的加载差异目录按数据库类型分设子目录便于按需取用与二次扩展。1. 当医学术语表遇上关系数据库SNOMED CT 落库到底难在哪如果你在医疗信息化项目里做过术语映射大概率经历过这种场面临床数据要对接对方甩过来一份 SNOMED CT 的国际版全量包几十万条概念、上百万条关系让你“先导进数据库再说”。你打开压缩包一看RF2 格式的 txt 文件铺满目录Concept、Description、Relationship、Language Refset 各管一摊字段之间靠 conceptId 串起来脑子里第一反应是——这玩意儿直接塞进 MySQL 能跑吗SNOMED CT 是临床医学概念的系统化命名体系本质是一张巨大的有向图概念之间靠 IS-A、Finding site、Associated morphology 这类关系连成网。它天生不是为关系数据库设计的但绝大多数业务系统只认 SQL。所以“在关系数据库中表示 SNOMED CT”这件事核心矛盾就一句话怎么把图结构塞进表结构还能让查询不崩。这份 SNOMED-CT-Database 资源要解决的正是这个落库问题适合做医疗数据平台、电子病历、临床决策支持的工程师也适合需要把术语服务接进现有 SQL 架构的团队。2. RF2 原始文件拆解先看懂再动手建表2.1 五个核心文件各管什么拿到 SNOMED CT 国际版发布包解压后通常是一堆以sct2_开头的 txt 文件。别急着写建表语句先把这几个文件的分工搞清楚否则后面字段对不上会反复返工。文件类型典型文件名片段核心作用关键字段Conceptsct2_Concept_Full概念主表每个 conceptId 一行id, effectiveTime, active, moduleId, definitionStatusIdDescriptionsct2_Description_Full概念的描述/同义词id, conceptId, languageCode, typeId, term, caseSignificanceIdRelationshipsct2_Relationship_Full概念间关系含 IS-Aid, sourceId, destinationId, typeId, characteristicTypeIdLanguage Refsetder2_cRefset_Language语言偏好标记哪个描述是首选referencedComponentId, acceptabilityIdStated Relationshipsct2_StatedRelationship推理用的声明关系同 Relationship但语义不同Concept 表是骨架Description 是给人看的标签Relationship 是给机器推理的边。三者通过 conceptId 和 id 关联。这里有个容易忽略的点Description 表里的id是描述 ID不是概念 IDconceptId才是指向 Concept 的外键。很多新手第一次写 JOIN 时把这两个搞混查出来的术语全是乱的。2.2 建表时字段类型怎么选SNOMED CT 的 ID 是 64 位整数但实际有效位数在 6 到 18 位之间。用BIGINT是稳妥选择别用INT否则遇到大 ID 会溢出。effectiveTime存成DATE或VARCHAR(8)都行看你的增量更新策略——如果要做时间旅行查询建议单独建一张历史表。-- 概念主表SNOMED CT 的骨架 CREATE TABLE snomed_concept ( concept_id BIGINT NOT NULL, effective_time DATE NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, module_id BIGINT NOT NULL, definition_status BIGINT NOT NULL, PRIMARY KEY (concept_id), INDEX idx_active (active), INDEX idx_module (module_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 描述表术语的文本表达 CREATE TABLE snomed_description ( description_id BIGINT NOT NULL, effective_time DATE NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, concept_id BIGINT NOT NULL, language_code VARCHAR(10) NOT NULL, type_id BIGINT NOT NULL, term VARCHAR(1024) NOT NULL, case_significance BIGINT NOT NULL, PRIMARY KEY (description_id), INDEX idx_concept (concept_id), INDEX idx_term (term(255)), INDEX idx_lang_type (language_code, type_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;term字段给到 1024 是留余量实际术语很少超过 255 字符但索引前缀用 255 足够。active字段一定要建索引因为绝大多数查询都会带WHERE active 1。module_id和definition_status在单版本场景下区分度不高但做多模块合并时是过滤关键。2.3 关系表的设计取舍Relationship 表是整张图的核心也是最容易出性能问题的地方。每条关系有 sourceId、destinationId、typeId 三个关键维度查询“某个概念的所有上位词”就是WHERE sourceId ? AND typeId 116680003116680003 是 IS-A 的类型 ID。CREATE TABLE snomed_relationship ( relationship_id BIGINT NOT NULL, effective_time DATE NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, source_id BIGINT NOT NULL, destination_id BIGINT NOT NULL, relationship_type BIGINT NOT NULL, characteristic BIGINT NOT NULL, modifier_id BIGINT NOT NULL, PRIMARY KEY (relationship_id), INDEX idx_source_type (source_id, relationship_type), INDEX idx_dest_type (destination_id, relationship_type), INDEX idx_type (relationship_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里我一般会建两个复合索引(source_id, relationship_type)和(destination_id, relationship_type)。前者用于“向上查父类”后者用于“向下查子类”。单独在relationship_type上建索引意义不大因为 IS-A 关系占了全表很大比例区分度低优化器可能直接走全表扫描。真正影响性能的是 source 和 destination 的过滤。注意Stated Relationship 和 Inferred Relationship 不要混在同一张表里。前者是人工声明的后者是推理机算出来的语义不同。混存会导致推理结果污染。3. 数据导入实战从 RF2 文本到可查询的表3.1 导入前的清洗与格式对齐RF2 文件是 Tab 分隔的但有几个坑第一文件编码通常是 UTF-8但某些版本带 BOM 头直接LOAD DATA会把第一个字段名读成带 BOM 的乱码第二空字段用空字符串表示不是 NULL第三日期格式是yyyyMMdd不是标准 SQL 日期。我一般先用 Python 做一轮预处理把 BOM 去掉、日期转成yyyy-MM-dd、空字段统一成 NULL。这一步看起来多余但能省掉后面大量STR_TO_DATE和NULLIF的麻烦。import csv import sys def clean_rf2(input_path, output_path): 清洗 RF2 文件去 BOM、转日期、空串转 NULL with open(input_path, r, encodingutf-8-sig) as fin, \ open(output_path, w, encodingutf-8, newline) as fout: reader csv.reader(fin, delimiter\t) writer csv.writer(fout, delimiter\t, quotingcsv.QUOTE_NONE) header next(reader) writer.writerow(header) for row in reader: # effectiveTime 从 yyyyMMdd 转 yyyy-MM-dd if len(row) 1 and len(row[1]) 8: row[1] f{row[1][:4]}-{row[1][4:6]}-{row[1][6:]} # 空字符串转 NULL用 \N 表示 row [r if r ! else \\N for r in row] writer.writerow(row) if __name__ __main__: clean_rf2(sys.argv[1], sys.argv[2])encodingutf-8-sig是关键它会自动吃掉 BOM。输出时用QUOTE_NONE避免 CSV 引号干扰空值用\N是因为 MySQL 的LOAD DATA默认把\N识别为 NULL。3.2 用 LOAD DATA 批量灌入清洗完的文件用 MySQL 的LOAD DATA LOCAL INFILE导入比逐条 INSERT 快两个数量级。百万级关系表大概几十秒能搞定。LOAD DATA LOCAL INFILE /data/clean/concept.txt INTO TABLE snomed_concept FIELDS TERMINATED BY \t LINES TERMINATED BY \n IGNORE 1 LINES (concept_id, effective_time, active, module_id, definition_status);IGNORE 1 LINES跳过表头。字段顺序必须和文件列顺序一致RF2 的列顺序在不同版本间可能有微调导入前用head -1确认一下。如果遇到active字段是1或0MySQL 会自动转成 TINYINT不用额外处理。导入 Description 表时注意term字段可能包含 Tab 或换行虽然 RF2 规范不允许但实际数据里偶尔出现。如果导入报错先用grep -P \t检查异常行。3.3 导入后的完整性校验导完不算完得验证数据有没有缺胳膊少腿。我一般跑三条校验 SQL-- 1. 检查孤立描述描述指向的概念不存在 SELECT COUNT(*) FROM snomed_description d LEFT JOIN snomed_concept c ON d.concept_id c.concept_id WHERE c.concept_id IS NULL; -- 2. 检查孤立关系关系的 source 或 destination 不存在 SELECT COUNT(*) FROM snomed_relationship r LEFT JOIN snomed_concept c1 ON r.source_id c1.concept_id LEFT JOIN snomed_concept c2 ON r.destination_id c2.concept_id WHERE c1.concept_id IS NULL OR c2.concept_id IS NULL; -- 3. 统计 IS-A 关系数量和官方文档对比 SELECT COUNT(*) FROM snomed_relationship WHERE relationship_type 116680003 AND active 1;第一条和第二条返回 0 才算干净。第三条的数量如果和发布说明里的数字对不上说明导入过程中有行被截断或跳过得回去查日志。IS-A 关系数量是 SNOMED CT 版本的一个标志性指标国际版通常在 40 万到 50 万条之间。4. 查询与推理让 SQL 跑出图数据库的效果4.1 递归 CTE 查上下位词SNOMED CT 最常用的查询是“找出某个概念的所有祖先或后代”。MySQL 8.0 支持递归 CTE可以直接在 SQL 里做图遍历不用把数据拉到应用层。-- 查某个概念的所有祖先向上遍历 IS-A WITH RECURSIVE ancestors AS ( -- 锚点起始概念的直接父类 SELECT r.destination_id AS concept_id, 1 AS depth FROM snomed_relationship r WHERE r.source_id 404684003 -- 示例Clinical finding AND r.relationship_type 116680003 AND r.active 1 UNION ALL -- 递归继续向上找 SELECT r.destination_id, a.depth 1 FROM snomed_relationship r INNER JOIN ancestors a ON r.source_id a.concept_id WHERE r.relationship_type 116680003 AND r.active 1 AND a.depth 20 -- 防止环导致无限递归 ) SELECT DISTINCT c.concept_id, d.term, a.depth FROM ancestors a JOIN snomed_concept c ON a.concept_id c.concept_id JOIN snomed_description d ON c.concept_id d.concept_id WHERE d.type_id 900000000000013009 -- Fully Specified Name AND d.active 1 ORDER BY a.depth;depth 20是保险丝。SNOMED CT 理论上是有向无环图但实际数据里偶尔存在环通常是数据错误没有深度限制会导致查询跑飞。type_id 900000000000013009是 Fully Specified Name 的类型 ID用它过滤能拿到每个概念的正式全名而不是同义词。4.2 物化路径与闭包表递归 CTE 写起来优雅但数据量大了之后性能会断崖式下跌。几十万概念、上百万关系每次查询都递归遍历响应时间轻松上秒。生产环境我一般会预计算一张闭包表或者物化路径表。闭包表就是提前把所有祖先-后代对算好存下来CREATE TABLE snomed_closure ( ancestor_id BIGINT NOT NULL, descendant_id BIGINT NOT NULL, depth INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id), INDEX idx_descendant (descendant_id) ) ENGINEInnoDB;填充闭包表可以用存储过程或者应用层代码迭代。代价是占空间——IS-A 关系的闭包大概几百万行但换来的是 O(1) 的查询WHERE ancestor_id ? AND descendant_id ?。空间换时间在术语服务这种读多写少的场景里非常划算。提示闭包表不需要每次查询都实时更新。SNOMED CT 国际版一年发布两次本地扩展模块更新频率也不高完全可以在版本更新时批量重建。4.3 全文检索术语临床医生输入“心梗”系统得能匹配到“Myocardial infarction”。SNOMED CT 的 Description 表里有同义词但直接LIKE %心梗%效率太低。MySQL 的全文索引对中文支持一般常见做法是外挂 Elasticsearch 或者用ngram分词器。如果不想引入额外组件至少给term字段建一个前缀索引然后配合应用层的同义词表做二次匹配。我见过有团队直接用SOUNDEX做模糊匹配对英文术语勉强能用中文就别想了。5. 避坑与排查那些让我加班到凌晨的坑5.1 坑一IS-A 关系查反了方向现象查“肺炎”的上位词结果返回一堆下位词越查越具体。原因SNOMED CT 的 Relationship 表里sourceId是子概念destinationId是父概念。IS-A 的语义是“source is a destination”。很多人直觉上觉得 source 是“来源/父级”正好搞反。解决记住一句话——source 是更具体的destination 是更泛化的。查祖先用source_id ?找destination_id查后代反过来。写查询前先用一个已知概念验证比如 404684003Clinical finding的父类应该是 138875005SNOMED CT Concept。5.2 坑二多语言 Refset 没关联导致术语显示英文现象系统里明明导入了中文描述界面上还是显示英文术语。原因Language Refset 文件没导入或者导入了但没和 Description 表关联。SNOMED CT 的描述有多个语言版本但哪个是“首选”由 Refset 里的acceptabilityId决定。没有 Refset查询时无法区分首选词和同义词。解决把der2_cRefset_Language文件也导入建一张snomed_language_refset表查询时 JOIN 上去用acceptabilityId 900000000000548007Preferred过滤。5.3 坑三effectiveTime 时区问题导致增量更新丢数据现象做增量更新时某些概念明明有新版本但查询effectiveTime 上次更新时间却查不到。原因RF2 的effectiveTime是发布日期不是精确到秒的时间戳。同一天发布的所有变更共享同一个日期。如果上次更新记录的是2024-01-15 10:30:00而新版本的effectiveTime是2024-01-15比较时会被判定为“不晚于”直接漏掉。解决增量更新不要用时间戳比较用版本号或发布批次。每次导入前记录当前最大effectiveTime下次导入时用而不是然后靠主键去重。5.4 坑四递归查询没加深度限制导致数据库连接打满现象某个术语查询突然变慢数据库连接数飙升其他业务查询全部排队。原因递归 CTE 遇到环或者超深路径时无限循环每个查询占用一个连接不释放连接池很快耗尽。解决递归 CTE 里强制加depth限制同时在应用层设置查询超时。更稳妥的做法是用闭包表替代实时递归从根上避免这个问题。5.5 坑五字符集不统一导致中文术语乱码现象导入的中文描述在数据库里显示正常但通过 API 返回给前端变成问号。原因数据库连接字符集、表字符集、应用层编码三者不一致。常见的是表用了utf8mb4但 JDBC 连接串没指定characterEncodingutf8。解决建表时统一utf8mb4连接串加useUnicodetruecharacterEncodingutf8应用层确认没有做额外的编码转换。导入前用file -i确认源文件编码。6. 版本更新与多模块合并一个能省半天时间的技巧SNOMED CT 国际版一年发两次本地扩展模块可能更频繁。每次更新都全量重建数据库不现实增量更新又容易踩坑。我现在的做法是保留全量历史用视图做版本切片。具体来说Concept、Description、Relationship 三张表都存全量数据主键是(id, effectiveTime)联合主键而不是单列主键。查询时通过一个snomed_version参数控制effectiveTime ?就能拿到任意时间点的快照。-- 联合主键示例 ALTER TABLE snomed_concept DROP PRIMARY KEY; ALTER TABLE snomed_concept ADD PRIMARY KEY (concept_id, effective_time); -- 查询某个版本的有效概念 SELECT * FROM snomed_concept WHERE effective_time 2024-07-01 AND active 1 AND concept_id NOT IN ( -- 排除在该版本之前已被标记为 inactive 的概念 SELECT concept_id FROM snomed_concept WHERE effective_time 2024-07-01 AND active 0 );这个模式的好处是更新时只需要INSERT新版本的行不用UPDATE旧行避免了锁表和更新冲突。代价是表体积会随时间增长但 SNOMED CT 的更新量不大一年两次全量发布增量部分通常只有几万行存储成本可以接受。多模块合并是另一个高频需求。国际版 某国扩展 某院本地术语三套数据要合并成一张表。合并策略是以 conceptId 为基准国际版优先扩展模块补充国际版没有的概念本地术语再补充。冲突时用moduleId判断优先级而不是简单覆盖。def merge_modules(base_rows, ext_rows, local_rows): 按优先级合并三个模块的概念返回去重后的列表 merged {} # 优先级从低到高国际版 扩展 本地 for row in base_rows: merged[row[concept_id]] row for row in ext_rows: cid row[concept_id] if cid not in merged or row[effective_time] merged[cid][effective_time]: merged[cid] row for row in local_rows: cid row[concept_id] if cid not in merged or row[effective_time] merged[cid][effective_time]: merged[cid] row return list(merged.values())合并后一定要跑一遍完整性校验尤其是关系表——扩展模块的关系可能指向国际版的概念合并后要确保这些引用仍然有效。我一般会在合并后重新执行第 3 章那三条校验 SQL确认没有孤立节点。从那以后我每次导入新版本前都会先在一个临时库上跑一遍完整流程确认校验 SQL 全部通过再动生产库。这个习惯帮我挡掉了至少三次因为文件损坏或版本不匹配导致的数据事故。希望帮到你。本文还有配套的精品资源点击获取