简介：这份数据库脚本集合面向需要将 SNOMED CT 术语集部署到关系型或图数据库的开发者与数据工程师，重点解决从 RF2 分发格式到 MySQL、PostgreSQL、MSSQL 与 Neo4j 的建库、装载和参数配置问题。压缩包共 115 个文件、约 434KB，以 64 个 SQL 脚本为主体，配合 11 个 Python 脚本、8 个 Markdown 说明及多种批处理与模板配置文件，覆盖建表、导入、路径设置和跨库适配等环节。脚本按数据库类型分目录组织，并保留建表、装载、校验与版本更新相关文件，便于按目标库快速定位入口。已有 1104 人学习，说明它在医学术语库构建场景中具有参考价值。读者可了解不同数据库中的表结构设计、RF2 数据装载流程与脚本组织方式，并借助现成脚本减少从零搭建术语库的重复工作，对学习术语编码、表关系映射和批量导入流程也有帮助。

1. 三十多万条概念进关系库：SNOMED CT 落地为什么绕不开数据库

第一次打开 SNOMED CT 的 RF2 全量包，很多人第一反应是上三元组库或图数据库——它本质就是一部带层级的有向无环图。可真到业务侧要按诊断名模糊搜索、按科室过滤、再和病历主表 join，图库那一层反而成了额外负担。在关系数据库里表示 SNOMED CT，说到底是把一部图拆成几张带主键的宽表，再用索引和闭包结构把“图”补回来。这件事适合三类人：正在做临床数据平台、要把术语接进业务库的工程师；要给检索和统计提供标准概念层的分析师；以及被术语升级折磨过一次、想提前把表结构定对的团队。下面按实际落地顺序讲：先拆表，再解决层级查询，最后说那些跑过一遍才会遇到的坑。

2. RF2 拆表：概念、描述、关系怎么落成三张主表

RF2 发布包看起来是一堆制表符分隔的文本文件，命名规则里带 Full、Snapshot、Delta 三种视图。Full 保留每个 id 的全部历史版本，Snapshot 每个 id 只留最新一行，Delta 只有本次发布变更的行。生产库我一般导两份：Snapshot 进主表供线上查询，Delta 进一张带 (id, effective_time) 复合键的历史表做追溯。这个选择必须在建表那天定下来，后面再改代价很大——主表用单列主键还是复合主键，直接决定你能不能不丢历史。

2.1 三条主轴先看清，再决定表结构

不管发布包里有多少文件，核心永远是三张：Concept、Description、Relationship。Concept 是一行一个概念，只有状态和定义性质；Description 是一对多，一个概念至少一条全称名（FSN），若干条同义词；Relationship 是边表，里面既有 IS A 这种层级边，也有“发生在某解剖部位”这种属性边。

SCTID 本身带分区信息：末两位之前的那一段是 partition identifier，00 表示概念、01 表示描述、02 表示关系，1x 段是各种 Refset。所以 concept.id 和 description.id 永远不会撞，三张表各用各的 id 做主键是安全的。这个特性值得在评审时讲清楚，否则总有人问“为什么不搞一张统一的 entity 表”。

不建议把三类东西揉进一张宽表。描述是一对多，硬塞进概念表就得用数组或者拼接字符串，检索和排序立刻失控；关系是标准的边表，单独一张表才能建 (source_id, type_id) 这种复合索引。Refset 另开一组表，语言偏好、映射、历史关联都挂在那里，不要和主表混。

2.2 PostgreSQL 建表：字段类型和几个必须现在定死的选择

id 一律 bigint。SCTID 最长 18 位，bigint 完全放得下，用 varchar 存会让每一个 join 都多一次字符串比较，几十万行上看不出，上千万行的闭包表上就是另一回事。active 在文件里是 1/0，落库转 boolean，别偷懒存 smallint，后面每个 where 都要写 active = 1，很容易漏。term 用 text，不要 varchar(255)——真实数据里确实存在超长描述。

CREATE SCHEMA IF NOT EXISTS snomed; -- 概念：partition identifier = 00 CREATE TABLE snomed.concept ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, definition_status_id bigint NOT NULL -- 原语 / 完全定义 ); -- 描述：partition identifier = 01，一个概念多条 CREATE TABLE snomed.description ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, concept_id bigint NOT NULL REFERENCES snomed.concept(id), language_code char(2) NOT NULL, type_id bigint NOT NULL, -- 全称名 / 同义词 term text NOT NULL, case_significance_id bigint NOT NULL ); -- 关系：partition identifier = 02，IS A 也在这张表里 CREATE TABLE snomed.relationship ( id bigint PRIMARY KEY, effective_time date NOT NULL, active boolean NOT NULL, module_id bigint NOT NULL, source_id bigint NOT NULL REFERENCES snomed.concept(id), destination_id bigint NOT NULL REFERENCES snomed.concept(id), relationship_group smallint NOT NULL, type_id bigint NOT NULL, characteristic_type_id bigint NOT NULL, -- 推断 / 断言 modifier_id bigint NOT NULL );

主键的选择是有代价的。上面这份只存 Snapshot，所以 id 单列主键；如果打算把 Full 也留在同一张表里做历史追溯，主键必须改成 (id, effective_time)，否则同一 id 的历史行根本插不进去。我在两个项目里分别用过两种方案，结论是：业务查询走 Snapshot 单表、历史追溯另开一张后缀 _hist 的表，比一张复合主键表混着用要清爽得多，查询计划也稳定。

提示：装载阶段先别建外键，把 REFERENCES 注释掉，等数据灌完再 ALTER TABLE 补上。外键检查会让 COPY 慢一个数量级。

2.3 用 COPY 装载，别用 INSERT

一百多万条描述、几百万条关系，用 INSERT 一条条送是纯粹浪费时间。RF2 是干净的制表符分隔文本，直接用 COPY。用 psql 的话一行就够：

# 文件名里的 <release> 换成你拿到的发布标识，不要写死日期 psql -d terminology -c "\copy snomed.concept (id, effective_time, active, module_id, definition_status_id) \ FROM 'sct2_Concept_Snapshot_INT_<release>.txt' \ WITH (FORMAT text, DELIMITER E'\t', NULL '')"

需要做字段映射（1/0 转 true/false）或者要边导边校验时，用 Python 包一层，按批次提交，避免一个大事务撑爆 WAL：

import csv, io, psycopg2 def load_rf2(conn, path, table, columns, bool_idx=(), batch=20000): """按批次把 RF2 文件 COPY 进表；bool_idx 是需要 1/0 转 t/f 的列下标。""" stmt = f"COPY {table} ({','.join(columns)}) FROM STDIN" total = 0 with open(path, encoding="utf-8-sig", newline="") as fh, conn.cursor() as cur: buf = [] # RF2 不使用引号包裹，字段内不含换行，QUOTE_NONE 避免误判 for row in csv.reader(fh, delimiter="\t", quoting=csv.QUOTE_NONE): for i in bool_idx: row[i] = "t" if row[i] == "1" else "f" buf.append("\t".join(row)) if len(buf) >= batch: cur.copy_expert(stmt, io.StringIO("\n".join(buf) + "\n")) conn.commit() total += len(buf) buf.clear() if buf: cur.copy_expert(stmt, io.StringIO("\n".join(buf) + "\n")) conn.commit() total += len(buf) return total conn = psycopg2.connect("dbname=terminology") load_rf2(conn, "sct2_Description_Snapshot-en_INT_<release>.txt", "snomed.description", ["id","effective_time","active","module_id","concept_id", "language_code","type_id","term","case_significance_id"], bool_idx=(2,))

几个参数值得说清楚。encoding="utf-8-sig"是为了吃掉文件头可能存在的 BOM，否则第一条记录的 id 会带不可见字符，主键插入失败而报错信息完全看不出原因。batch=20000是经验值：太小提交次数多，太大单次事务的 WAL 压力上来，遇到锁等待不好中断。quoting=csv.QUOTE_NONE必须加，RF2 里描述文本本身可能出现引号，让 csv 模块去解析引号会把一行拆错。

如果某一列存在空值，COPY 的 text 格式默认把\N当 NULL，而 RF2 里空字段通常直接是两个制表符相邻，所以上面用NULL ''显式声明空串即 NULL。这一条不写，导入后会出现一堆空字符串，和 NULL 混在一起，后面IS NULL判断全失效。

2.4 装完先跑四个校验，别急着写业务查询

数据灌进去不等于导对了。每次升级后我都会先跑这四条，任何一条结果异常就不往下走。

-- 1) 概念量级与生效比例，用来和上一个版本对比 SELECT count(*) AS total, count(*) FILTER (WHERE active) AS active FROM snomed.concept; -- 2) 生效概念里有没有缺全称名的（正常应为 0） SELECT count(*) FROM snomed.concept c WHERE c.active AND NOT EXISTS ( SELECT 1 FROM snomed.description d WHERE d.concept_id = c.id AND d.active AND d.type_id = 900000000000003001); -- 3) 关系两端的 id 是否都在概念表里（孤儿边，正常应为 0） SELECT count(*) FROM snomed.relationship r WHERE r.active AND ( NOT EXISTS (SELECT 1 FROM snomed.concept c WHERE c.id = r.source_id) OR NOT EXISTS (SELECT 1 FROM snomed.concept c WHERE c.id = r.destination_id)); -- 4) IS A 是否存在自环（正常应为 0） SELECT count(*) FROM snomed.relationship WHERE active AND type_id = 116680003 AND source_id = destination_id;

第 2 条是最容易被忽略的：某些本地扩展模块的概念只有同义词没有全称名，做展示时取不到名字，界面上就是一串数字。第 3、4 条用来兜住文件截断和增量包错位——关系表少导一半，查询不会报错，只会静默地少返回结果，这种问题在业务侧往往要一两周才被发现。

3. 层级查询：递归 CTE 撑不住的时候，闭包表怎么建

层级是 SNOMED CT 最核心的用法：查“某个诊断下所有子类”“某个部位包含哪些结构”。关系表里 IS A 的 type_id 是固定的 116680003，这条边把整部术语连成一棵（严格说是张有向无环图，一个概念可以有多个父类）。业务 SQL 里天天写递归 CTE，性能迟早出问题。

3.1 递归 CTE 的最小写法与环保护

向上找祖先是最常见的需求，比如给一个概念打上所有上位标签：

WITH RECURSIVE up AS ( SELECT r.destination_id AS ancestor_id, 1 AS depth, ARRAY[r.source_id] AS path FROM snomed.relationship r WHERE r.active AND r.type_id = 116680003 AND r.source_id = :sctid UNION ALL SELECT r.destination_id, up.depth + 1, up.path || r.source_id FROM up JOIN snomed.relationship r ON r.source_id = up.ancestor_id WHERE r.active AND r.type_id = 116680003 AND up.depth < 32 AND NOT r.destination_id = ANY(up.path) -- 环保护 ) SELECT DISTINCT ancestor_id FROM up;

path数组是防护措施。正常情况下 IS A 不会成环，但本地扩展模块加上人工维护的数据，什么都有可能；一旦成环，没有 path 判断的递归会一直跑到内存耗尽，把连接池占死，排查起来非常费劲。depth < 32是第二道闸，SNOMED CT 的实际深度不会超过二十层，超过基本可以判定数据有问题。

向下找后代更贵，因为一个高层概念（比如“临床发现”）下面挂着十几万个子类，递归 CTE 要走十几万次索引扫描。单次查询可能几百毫秒，QPS 一上来就顶不住。这时候不是调优的问题，是模型选错了。

3.2 闭包表：一次算完，换掉每次查询的递归

闭包表就是把“祖先-后代-层数”这三元组全部物化下来。代价是体积，收益是任意层级查询都退化成一次主键索引扫描。

DROP TABLE IF EXISTS snomed.isa_closure; CREATE TABLE snomed.isa_closure ( ancestor_id bigint NOT NULL, descendant_id bigint NOT NULL, depth smallint NOT NULL, PRIMARY KEY (ancestor_id, descendant_id) ); INSERT INTO snomed.isa_closure (ancestor_id, descendant_id, depth) WITH RECURSIVE edge AS ( SELECT source_id, destination_id FROM snomed.relationship WHERE active AND type_id = 116680003 ), walk AS ( SELECT source_id AS ancestor_id, destination_id AS descendant_id, 1 AS depth FROM edge UNION -- 用 UNION 而不是 UNION ALL 去重 SELECT w.ancestor_id, e.destination_id, w.depth + 1 FROM walk w JOIN edge e ON e.source_id = w.descendant_id WHERE w.depth < 32 ) SELECT ancestor_id, descendant_id, min(depth) FROM walk GROUP BY 1, 2; -- 自己到自己，深度 0，方便写「含自身」的查询 INSERT INTO snomed.isa_closure SELECT id, id, 0 FROM snomed.concept WHERE active;

这里UNION是关键。一个概念可以同时通过多条路径到达同一个祖先（多父类场景），用UNION ALL会让中间结果按路径数指数膨胀，几百万行能撑出几十亿行中间集，直接把临时表空间写满。加上顶层min(depth)取最短路径，深度字段才有意义——用来做“只取 N 层以内”的裁剪。

体积上，闭包表通常比概念表大一两个数量级，这个开销得提前和存储方沟通。我在一个项目里为了省空间试过只存到第五层，结果业务侧要做“某大类下所有子类”的统计时直接缺数据，最后老老实实全量重建。省这个空间不值得。

有了闭包表，查询就变成这样：

-- 「临床发现」及其所有后代（含自身） SELECT c.id, d.term FROM snomed.isa_closure cl JOIN snomed.concept c ON c.id = cl.descendant_id AND c.active JOIN snomed.description d ON d.concept_id = c.id AND d.active WHERE cl.ancestor_id = 404684003;

3.3 增量升级时闭包表怎么重建

最省事也最稳的做法是全量重建：新版本导进主表，在一个事务里重建isa_closure_new，校验通过后ALTER TABLE ... RENAME换名。全量重建的耗时和概念数成正比，通常在一杯咖啡的范围内，比维护一套增量逻辑出错率低得多。

真要做增量，思路是只重算受影响的子图：把本次 Delta 里 IS A 边有增删的 source_id 收集起来，向上取这些节点的全部祖先集合 A，向下取全部后代集合 D，删除ancestor_id ∈ A AND descendant_id ∈ D的行，再对 A 里每个节点重跑一次向下递归补回去。逻辑不复杂，但边界特别多（新增的边可能让两个原本无关的子树连起来），没有专门的回归用例就不要碰。

注意：闭包表的 PRIMARY KEY (ancestor_id, descendant_id) 本身就是最好的索引，不要再额外建 (descendant_id) 单列索引除非确实有反向查询，否则只是白白拖慢重建。

4. 避坑与排查：升级、失效概念和 Refset 上最容易翻车的地方

前三章是“怎么建”，这一章是“建完为什么会出错”。下面五条都是我在真实升级窗口里踩过的，按现象、原因、解决写，能对号入座就省一次通宵。

4.1 增量包直接追加上去，历史版本把查询打乱

现象：升级后同一批检索条件返回的结果翻倍，界面上同一个诊断名出现两遍，但数据库里的概念总数看起来又没错。

原因：RF2 的 Delta 是“变更行”而不是“替换行”，同一个 id 会因为状态变化、描述修订出现多行。直接往单列主键的表里 append，要么主键冲突整批失败，要么当初为了“能插进去”把主键去掉了，结果同一个概念留下 active 为真和为假的两行。业务 SQL 里只写了WHERE concept_id = ?没带 active 判断，重复就此产生。

解决：Delta 一律先进 staging 表，按 (id, effective_time) 去重保留最新一行，再做 upsert。别在业务表上直接跑 MERGE 逻辑。

-- 1) staging 表内同 id 只留 effective_time 最大的那一行 DELETE FROM stg.description s USING stg.description t WHERE s.id = t.id AND s.effective_time < t.effective_time; -- 2) 再合并进主表，只接受更新的版本 INSERT INTO snomed.description AS d (id, effective_time, active, module_id, concept_id, language_code, type_id, term, case_significance_id) SELECT id, effective_time, active, module_id, concept_id, language_code, type_id, term, case_significance_id FROM stg.description ON CONFLICT (id) DO UPDATE SET effective_time = excluded.effective_time, active = excluded.active, term = excluded.term WHERE excluded.effective_time > d.effective_time;

WHERE excluded.effective_time > d.effective_time这句不能省。重跑一次导入脚本时，没有它就会用旧版本覆盖新版本，而这种回退在数据上看不出任何异常，只有等到某个新概念查不到才发现。

4.2 概念失效了，旧病历里的编码突然查不到名字

现象：历史病历展示时，一部分诊断名变成空白或者直接显示数字 id。查库发现那个概念的 active 已经是 false。

原因：概念被停用后，通常会给出历史关联，指向替代概念。这部分数据不在三张主表里，而在历史关联类 Refset 中，很多人导数据时只导了 Concept/Description/Relationship，把这组文件漏了。于是所有“旧编码到新编码”的映射断掉。

解决：把历史关联 Refset 一并导入，单独建一张映射表，字段至少包含旧 id、关联类型、新 id。展示层取名字的顺序改成：当前概念名 → 历史关联找到的替代概念名 → 原 id 兜底。同时给业务侧一个明确的“该概念已停用”标记，不要静默替换，临床语义上停用和替换不是一回事。

4.3 stated 和 inferred 混在一张表里，层级少了一半

现象：用 IS A 做后代查询，某些概念明明有上级，却查不到它属于哪个大类；或者统计出来的子类数量比预期少很多。

原因：关系表里混着两种性质的关系——人工断言的（stated）和推理机推导的（inferred）。断言关系只覆盖部分概念，完整层级在推断关系里。装载时没做区分，查询里也没按 characteristic_type_id 过滤，两种数据混在一起，结果就是既不完整也不一致。更隐蔽的情况是只导了断言关系，看起来数据量正常，层级却处处是断点。

解决：主表里保留全部关系，但在建闭包表和做层级查询时，统一按推断关系的 characteristic_type_id 过滤，把条件封进视图，别让业务方自己写。视图里同时固定active AND type_id = 116680003，这三个条件应该是绑定在一起的。

4.4 term 检索大小写、变音符号和全称名混用

现象：搜索“diabetes”能搜到，搜“Diabetes”结果变少；带重音的拉丁字母完全搜不到；列表里展示的诊断名长得不像人话，一堆括号和“（disorder）”。

原因：LIKE '%xx%'在默认排序规则下是大小写敏感的；变音符号没做归一化；展示时取的是全称名而不是语言 Refset 里标记为首选的同义词。全称名是给人和机器做唯一性判断用的，带语义标签，不适合直接上界面。

解决：建表达式索引lower(term)，或者装 unaccent 扩展后对归一化结果建 GIN 索引；展示名走语言 Refset 的首选标记，取不到再退化到全称名。这两件事都应该在建表当天定好，事后补索引要锁表，几百万行的表上就是一次停机。

提示：给展示名的取数逻辑单独做一个视图，业务方只查视图不查 description 表。这样语言偏好规则调整时，改一处就够了。

4.5 索引建在装载之前，全量导入慢十倍

现象：一次全量升级从二十分钟变成三个多小时，磁盘 IO 打满，其他业务跟着抖。

原因：主表和关系表上已经有四五个索引加外键，每条 COPY 进去的行都要维护一遍 B-tree、检查一次外键引用。数据量翻倍时这个代价是非线性的。

解决：升级流程固定成“删索引 → 清表 → COPY → 建索引 → 加约束 → ANALYZE”。ANALYZE这一步不能省，尤其闭包表刚建完，没有统计信息时优化器会选错连接顺序，一条本该 50 毫秒的查询跑成 5 秒，还找不到原因。

5. 进阶：把子集和“可临床使用”视图提前算好

最后说一个让整套方案真正好用的技巧：不要让别人直接面对原始表。业务方要的从来不是“SNOMED CT 全库”，而是几个稳定子集——诊断、操作、解剖部位，外加“当前可临床使用”的过滤条件。这些应该在库里算好，而不是让每个查询自己拼。

做法是用闭包表把常用根节点的后代预计算成视图，把 active、模块、关系性质这些条件全部封进去：

CREATE MATERIALIZED VIEW snomed.mv_clinical_finding AS SELECT c.id, d.term FROM snomed.concept c JOIN snomed.description d ON d.concept_id = c.id AND d.active AND d.type_id = 900000000000013009 JOIN snomed.isa_closure cl ON cl.descendant_id = c.id WHERE c.active AND cl.ancestor_id = 404684003; CREATE UNIQUE INDEX ON snomed.mv_clinical_finding (id);

建完唯一索引就能用REFRESH MATERIALIZED VIEW CONCURRENTLY在线刷新，升级窗口不用停查询。根节点换成“操作”“解剖结构”就是另外两个视图，业务侧按视图查，规则调整只改一处。

配套的是一个回归用例表。每次升级前后跑同一批断言，任何一条不符合预期就直接回滚，不靠人眼比对：

这套东西的价值在第二次升级时才体现出来。第一次升级我图快，只比对了总数，结果描述文件少导了一个语言，界面上大半诊断名变英文，被业务方追着问了两天。从那以后我的习惯是：任何一次术语升级，先跑断言表，过了再刷新视图，两步都留日志。术语库这种“查询不报错但结果不对”的系统，后悔药只能提前吃。

希望帮到你。

本文还有配套的精品资源，点击获取