★ FEATURED ARTICLE 简介这份资源针对医疗信息化开发与医学术语标准化场景面向需要将SNOMED CT术语库导入不同数据库系统的工程师与研究人员。脚本以RF2分发格式为基础可创建并填充MySQL、PostgreSQL、MSSQL或Neo4j数据库支持从空库到术语数据的完整装载流程并且不同数据库的子目录划分清晰便于按需选用。压缩包内含115个文件其中64个SQL脚本是核心负责建表、索引与数据写入另有Python辅助脚本、批处理、配置文件和Cypher查询等用于自动化加载、环境检测与图数据库适配包体仅434KB非常紧凑。目前已有1103人学习适合医学信息学入门者或需要快速搭建SNOMED CT本地环境的团队参考。通过阅读目录结构、配置参数和装载脚本能较快理解RF2规则、多数据库适配思路及装载期间的排错技巧并可在此基础上复用或二次开发。1. 为什么要在关系数据库里装一套 SNOMED CTSNOMED CT 是目前医疗健康领域最庞大的临床术语表日常做医疗信息化集成的人几乎绕不开它病历结构化、检验检查术语映射、CDSS 规则引擎、科研数据抽取底层数据模型大多要落到这套术语体系上。但 SNOMED CT 的官方发布格式是 RF2一整套 TSV 文本文件散落在十几个目录里文件名后缀从 .txt 到 .delta 都有直接拿来做业务查询非常别扭。这个名为 SNOMED-CT-Database 的资源干的事情就是把 RF2 文本导入 MySQL / PostgreSQL建成一套可以直接跑 SQL 的关系表。适合正在做医学术语服务、临床数据平台或者被 RF2 解析折腾过的开发者。导入之后概念的父子关系、描述的语言偏好、历史包的追溯都能用标准 SQL 查出来不用再维护一堆自定义解析脚本。2. RF2 格式与关系模型的映射为什么不能直接拿文本当数据库SNOMED CT 的 RF2 发布包不是随手就能用的格式。先弄清楚它的结构才知道这个项目里那些建表脚本到底在做什么。2.1 RF2 的核心文件构成Terminology 与 Reference Set 的分工一套完整 RF2 发布包按内容分成几大块Terminology 目录放核心术语文件Concept、Description、Relationship、IdentifierDocumentation 目录放图表和说明Refset 目录放各种参考集映射、语言偏好、历史包。实际导入时最常用的是 Terminology 下的四个主文件sct2_Concept_Snapshot_INT_*.txt概念快照每行一个概念核心字段是概念ID、生效状态、模块ID。sct2_Description_Snapshot_INT_*.txt描述快照概念的全部文字描述包括 Fully Specified Name 和 Synonym。sct2_Relationship_Snapshot_INT_*.txt关系快照概念之间的父子关系、属性关系。sct2_Identifier_Snapshot_INT_*.txt标识符快照处理历史标识符和替代标识符的映射。这些文件用 Tab 分隔UTF-8 编码表头是固定的 8 个字段左右比如 id、effectiveTime、active、moduleId、definitionStatusId。注意是 .txt 扩展名但内容完全是结构化 TSV所以解析逻辑本质上就是「按行切分 按列映射」。这个项目做的第一件事就是把这些文件的行结构翻译成关系表的行列结构。以 Concept 文件为例官方规范的每一列都对应到后续建表语句里 Concept 表的一个字段。表格关系如下RF2 列名含义建表后的字段类型id概念唯一 IDBIGINT 主键effectiveTime生效时间DATEactive是否生效BIT/BOOLEANmoduleId所属模块BIGINT 外键到 ConceptdefinitionStatusId定义状态BIGINT我一般会在导入前先统计一下行数便于导入后核对。比如 Concept 快照文件通常是几十万行Description 文件是百万级行Relationship 文件也是百万级。行数对不上多半是文件没下全或者增量包混进去了。2.2 关系模型为什么要单独建表快照、增量与状态字段RF2 发布包里有 Snapshot 和 Delta 两种形态。Snapshot 是当前全量状态Delta 是某个时间段的增量变化。前者适合做初始导入后者适合做日常同步。这个资源里的建表脚本是按 Snapshot 建模的所以核心表都对应快照文件的列结构同时保留了 active、effectiveTime 这类状态字段。active 这个字段非常关键它标识某一行在对应生效时间点是否有效。临床查询时默认要过滤 active 1否则会把历史废弃的概念也查出来。effectiveTime 则用于时间点回溯比如「截至 2024 年 6 月这个概念的描述是什么」。两个字段一起用就能在建好的关系表上实现「按有效时间过滤」的查询。建表之外还有一层重要设计是对 Relationship 表里的关系类型做约束。SNOMED CT 的关系分为「父子关系」和「属性关系」前者用 116680003 (Is a) 标识后者包括 Finding site、Causative agent 等。关系表里 relationshipType 字段存的就是这些关系类型的 Concept ID。筛选时用「WHERE typeId 116680003」就能把父子关系单独捞出来。2.3 为什么用关系数据库而不是图数据库SNOMED CT 本质是个多父级分类树有人第一反应是上 Neo4j 之类图数据库。但对于大多数医疗集成项目图库太重运维成本高且团队不一定熟悉 Cypher 语法。关系数据库的优势在于医疗业务系统基本都用 MySQL / PostgreSQL术语服务作为模块嵌进去不需要额外部署中间件。临床数据抽取时术语表的关联查询可以和业务表直接 JOIN不用跨库。SQL 的技能门槛低团队成员都能维护。代价是递归查询稍微绕一点。MySQL 8 之后支持 WITH RECURSIVEPostgreSQL 天然支持所以「查某个概念连同所有下级子概念」这种操作也能用 SQL 实现。这个项目把基础表建好之后剩下的递归逻辑完全可以用一条 WITH 语句完成——我在第 4 章会给出可以直接抄的写法。3. 导入实操从 RF2 文件到 MySQL 的完整步骤拿到这个资源包之后导入流程分四步建库建表 → 准备导入脚本 → 执行导入 → 验证行数。下面以 MySQL 为例写PostgreSQL 流程几乎一致差别只在个别数据类型和路径写法。3.1 建库与执行 DDL 脚本资源包里的 sql 目录一般包含全库建表脚本有的还分 mysql 和 postgresql 两个版本。拿到后先建一个独立库不要跟业务库混在一起后面做权限隔离和备份都方便。CREATE DATABASE snomed_ct CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE snomed_ct; SOURCE /path/to/snomed_ct_schema.sql;执行后检查表清单。核心表至少应该有 concept、description、relationship、identifier 四张外加一张 meta 表记录导入批次信息。用 SHOW TABLES 看一眼SHOW TABLES;预期输出里能看到 concept、description、relationship 等表名。如果发现缺表优先检查 SOURCE 语句的路径是否写对以及脚本里有没有依赖其他前置表。3.2 用 LOAD DATA 批量导入比逐条 INSERT 快两个数量级高频操作是导入 Concept、Description、Relationship 三个快照文件。逐条 INSERT 在百万级行数下会慢到难以接受直接改用 MySQL 的 LOAD DATA INFILE。我一般会先看文件的项目符号格式确认 Tab 分隔、无引号包裹再执行导入。LOAD DATA INFILE /var/lib/mysql-files/sct2_Concept_Snapshot_INT_20240131.txt INTO TABLE concept FIELDS TERMINATED BY \t ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, effectiveTime, active, moduleId, definitionStatusId);这一段的关键点FIELDS TERMINATED BY \t 对应 TSV 的制表符分隔IGNORE 1 LINES 跳过表头字段列表要按照表结构里的列顺序写缺一不可。时间格式是 YYYYMMDDRF2 规范MySQL 的 DATE 字段会自动转成标准时间格式。如果文件路径不在安全目录下MySQL 8 默认会报错。解决方式有两个一是把文件放到 secure-file-priv 指向的目录通常 /var/lib/mysql-files/二是启动参数里临时指定 secure-file-priv。生产环境建议用前者后者安全性差一点。导入 Description 文件时列顺序和 Concept 略有不同多了 languageCode 和 typeId 两个字段LOAD DATA INFILE /var/lib/mysql-files/sct2_Description_Snapshot_INT_20240131.txt INTO TABLE description FIELDS TERMINATED BY \t ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, effectiveTime, active, moduleId, conceptId, languageCode, typeId, term, caseSignificanceId);term 字段是从 Description 文件里提取的纯文本描述导入后直接支持 LIKE 查询和全文检索。如果业务上要做中文支持可以在这个字段上再加全文索引但注意 utf8mb4 下的全文索引有最小词长限制。3.3 导入后的必做校验行数、空值、外键导入完成不是结束校验没过等于白导。我一般按三步走。第一步对行数。SELECT COUNT(*) FROM concept; SELECT COUNT(*) FROM description; SELECT COUNT(*) FROM relationship;基于官方快照文件的行数做比对。每个版本的行数都会印在发布说明里或者导入前自己 wc -l 统计一遍。第二步查空值。Concept 和 Description 表的 id 字段、conceptId 字段不应为空relationship 表的所有外键字段不应为空。SELECT COUNT(*) FROM description WHERE conceptId IS NULL;有结果就说明导入时字段错位了常见原因是表头没跳过或者列顺序和文件的真实顺序不一致。RF2 每个文件表头顺序不完全相同必须先确认。第三步验证外键关系。比如 relationship 表里的 sourceId 应该全部能在 concept 表里找到。直接跑一条 LEFT JOIN 查悬空记录SELECT COUNT(*) FROM relationship r LEFT JOIN concept c ON r.sourceId c.id WHERE c.id IS NULL;这条语句的结果要是 0。如果是新版本发布包偶尔会出现行数对但外键不完整的情况就要对照官方发布说明看是否有已知数据问题。4. 用 SQL 查询 SNOMED CT概念查找、父子关系与祖先路径表建好、数据导入之后才是真正有用的部分。下面几个查询模板都是实际做术语服务时高频用到的可以直接拿去做成 REST 接口的底层 SQL。4.1 按全名或同义词查概念用户输入「lung cancer」这类词先转小写再对 term 字段做模糊匹配。Description 表里包含 FSN 和 Synonym 两种描述FSN 带有语义标签如disorder查询时要区分类型。SELECT c.id AS concept_id, d.term, d.typeId FROM description d JOIN concept c ON d.conceptId c.id WHERE d.active 1 AND c.active 1 AND LOWER(d.term) LIKE %lung cancer% AND d.typeId 900000000000013009;typeId 900000000000013009 是 Synonym 的固定 ID900000000000003001 是 FSN。上面的查询强制只返回同义词匹配结果避免 FSN 标签干扰。如果想要 FSN 结果把最后一行替换成 900000000000003001 即可。参数说明LIKE 匹配两侧加百分号会导致索引失效如果 Description 表数据量超过两百万行建议在 term 字段上加全文索引改用 MATCH AGAINST。MySQL 的全文索引对中文支持略弱但英文术语完全没问题。4.2 查某概念的所有直属父级和子级SNOMED CT 是多父级体系一个概念可能同时挂在多个父级下面。查询直属子级用一条简单 WHERESELECT c.id, d.term FROM relationship r JOIN concept c ON r.destinationId c.id JOIN description d ON c.id d.conceptId AND d.typeId 900000000000003001 WHERE r.sourceId 73211009 AND r.typeId 116680003 AND r.active 1 AND c.active 1 AND d.active 1;sourceId 是被查概念的 IDtypeId 116680003 表示 Is a 关系。查到的结果就是所有下层直接子概念。把 sourceId 换成 destinationId 就能拿到所有直属父级。这里有个容易踩坑的地方relationship 表里的关系是有方向的sourceId 是起点destinationId 是终点搞反了查出来的就是反方向。另外关系表的 active 也要过滤否则会出现已经停用的关系记录。4.3 递归查询完整祖先路径直接子级只能看到一层临床场景往往需要「这个概念归到哪个大类」的完整链条。PostgreSQL 和 MySQL 8 都支持 WITH RECURSIVE写法如下WITH RECURSIVE ancestors AS ( SELECT destinationId, sourceId, 1 AS depth FROM relationship WHERE sourceId 73211009 AND typeId 116680003 AND active 1 UNION ALL SELECT r.destinationId, r.sourceId, a.depth 1 FROM relationship r JOIN ancestors a ON r.sourceId a.destinationId WHERE r.typeId 116680003 AND r.active 1 AND a.depth 10 ) SELECT DISTINCT destinationId FROM ancestors;逻辑说明CTE 的初始查询先拿到概念的直接父级递归部分再拿这些父级的父级。depth 小于 10 作为递归深度上限防止数据异常导致的死循环。实际执行时若某个概念的层级超过 10 层结果会被截断可以按需调大。如果换成查所有后代只需把 JOIN 条件反过来从 sourceId 出发往子级方向递归。注意递归 CTE 在 MySQL 里如果遇到循环引用会报错SNOMED CT 的发布数据理论上没有这个问题但导入时最好先测一条深层级概念。5. 避坑指南导入与查询中最常翻车的 5 个问题RF2 导入的坑大多集中在格式细节上下面这几条是从实际项目中踩出来的。5.1 导入时间字段报错YYYYMMDD 与 DATE 类型的隐式转换现象LOAD DATA 执行时报错 Incorrect date value: 20240131。原因MySQL 的严格模式要求日期字符串必须符合 YYYY-MM-DD 格式RF2 文件里的日期是 YYYYMMDD两者直接映射会失败。解决导入前先确认建表脚本里 effectiveTime 的字段类型。如果是 DATE建议加载到 VARCHAR 然后再转换或者 SELECT 时用 STR_TO_DATE。稳妥做法是建表时直接用 VARCHAR(8) 存查询时再转换。SELECT STR_TO_DATE(effectiveTime, %Y%m%d) FROM concept LIMIT 5;从那以后我所有 RF2 版本的导入脚本里日期字段都默认先存原始格式除非确定目标库关闭了严格模式。5.2 描述文件导入后 term 字段出现中文乱码现象SELECT term 结果里的英文正常但后续导入中文映射词后全是问号或乱码。原因建表时字符集用了 latin1或者 LOAD DATA 语句里没有指定 CHARACTER SET utf8mb4。解决库、表、连接三层都要统一成 utf8mb4。LOAD DATA 语句里显式加 CHARACTER SET utf8mb4同时确认 .txt 文件本身是 UTF-8 编码。LOAD DATA INFILE ... INTO TABLE description CHARACTER SET utf8mb4 FIELDS TERMINATED BY \t;RF2 国际版本身是英文但很多项目会做本地化扩展这个坑碰上的人不少。5.3 Relationship 表导入后外键校验失败现象3.3 节那条 LEFT JOIN 语句查出来大量悬空 sourceId。原因可能是导入时列顺序写错比如把 destinationId 当成 sourceId 写进第一个字段。RF2 的快照文件列顺序是固定的 id、effectiveTime、active、moduleId、sourceId、destinationId、relationshipGroup、typeId、characteristicTypeId、modifierId少写一列就会全线错位。解决先用 SELECT 语句抽查文件前三行确认序列head -n 3 sct2_Relationship_Snapshot_INT_20240131.txt对照列顺序再检查 LOAD DATA 语句里的字段映射。5.4 查询结果带出已废弃概念现象业务方反馈查「高血压」查出几百个无关概念一查发现很多 active 0 的旧名混入。原因Description 表和 Concept 表的 active 字段是两个独立维度。概念停用后描述可能仍标记为 active或者反之。查询时只处理其中一个表的 active 条件不够。解决统一在所有查询里同时过滤 c.active 1 和 d.active 1两个条件缺一不可。关系表也一样未停用的关系可能在概念停用后仍然残留。5.5 递归查询卡死或返回超时现象执行 WITH RECURSIVE 查询某些大类概念时数据库 CPU 拉满几分钟没结果。原因没有 depth 限制且概念层级里有环虽然官方数据不应该有但扩展包偶尔会有或者是 ON 条件里忘了加 active 过滤导致数据规模暴增。解决递归 CTE 里加深度上限和 active 条件先跑出单条结果再放开范围。WITH RECURSIVE ancestors AS (...) SELECT ... WHERE depth 10;6. 从快照到业务系统增量更新与术语服务的下一步导入不是一次性的活。SNOMED CT 每半年发布一次国际版快照日常维护需要把增量包合并进现有表而不是每次都全量重新导入。6.1 增量应用用 Delta 文件合并状态Delta 文件和 Snapshot 文件结构一致但内容是新增、修改或停用的行。合并逻辑一句话概括先按 id 处理新增与更新再按 active 字段处理停用。INSERT INTO concept (id, effectiveTime, active, moduleId, definitionStatusId) SELECT id, STR_TO_DATE(effectiveTime, %Y%m%d), active, moduleId, definitionStatusId FROM tmp_concept_delta ON DUPLICATE KEY UPDATE effectiveTime VALUES(effectiveTime), active VALUES(active), definitionStatusId VALUES(definitionStatusId);tmp_concept_delta 是每个 Delta 文件导入到的临时表。这段 SQL 做到了「新的插入、旧的有效期覆盖、停用的标记失效」。注意 ON DUPLICATE KEY UPDATE 只针对主键冲突行生效不冲突的行就是新增。增量合并的坑在于某个概念可能在一个 Delta 里出现多条记录不同时间点的变更直接按主键覆盖会丢历史。正确做法是先全部插入临时表再用 effectiveTime 做窗口去重。6.2 验证增量导入后的数据一致性增量合并后必须重新跑一遍完整校验概念总数变化、关系外键完整性、描述 active 状态比例。一个快速经验值规则正常国际版快照active 为 1 的概念占比一般在 70% 到 80% 之间如果导入后低于 50%说明增量合并逻辑可能出错。SELECT active, COUNT(*) FROM concept GROUP BY active;6.3 把查询封装成语料服务索引设计与缓存策略导好的表要支撑业务建议按下面三个方向优化。第一Description.term 字段上加全文索引。百万级描述表里做 LIKE %xx% 全模糊查询响应时间会到秒级。全文索引能压到百毫秒内但需要处理最小词长限制和停用词表。第二把高频的「概念 FSN 名称」做成物化视图业务查询直接 JOIN 视图避免每次都递归计算。PostgreSQL 可以建 MATERIALIZED VIEWMySQL 8 可以手动建冗余表定时刷新。第三查询结果加一层 Redis 缓存key 用概念 ID 查询类型缓存 24 小时。术语数据不是高频变化的业务数据缓存过期策略放宽松完全没问题。如果要把这套术语服务暴露给内部系统我一般会做两个接口一个按描述词全文搜索返回概念 ID 列表一个按概念 ID 返回完整父链条和直接子级。底层 SQL 就是第 4 章那几条接口包一层参数校验和分页即可。这套资源把 SNOMED CT 从「一堆 TSV 文本」变成「能跑 SQL 的关系表」对医疗信息化开发者来说足够用。导入时多花一点时间做校验后面业务集成能省下数倍返工成本。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
