医数智联·第 11 期:PostgreSQL 入门 —— 为什么医学界更推荐它,4 大独有优势

[!ABSTRACT] 核心摘要

• 本期主题:PostgreSQL 入门——为什么医学界更推荐它,以及怎么从 MySQL 迁移
• 核心结论:MySQL 适合「业务数据」(HIS/LIS),PostgreSQL 适合「医学数据」(科研/科研病历/影像元数据)
• 4 大独有优势:JSONB 字段(半结构化数据)、全文检索(中文病历检索)、地理信息(院区/患者分布)、丰富的数据类型(数组/范围/枚举)
• 3 大医院场景:科研病历存储、检验报告半结构化、医学术语词典
• 阅读时长:约 10 分钟

---

一、场景引入:MySQL 在医学数据上「不够用」了

[!example] 一个真实的研究项目
某三甲医院启动「糖尿病视网膜病变」的回顾性研究,需要:

1. 提取 5 年内 2.3 万份 糖尿病住院病历;
2. 提取 5 万次 眼底检查报告(半结构化文本,每份报告约 200-500 字);
3. 提取 8 万条 实验室检验结果;
4. 关联 3 万条 影像元数据(检查部位、影像描述)。

信息科负责人看了一眼 MySQL,说:

「眼底检查报告怎么存?」

整段 200 字的报告,塞进一个 VARCHAR 字段?用 TEXT?用 JSON 字符串?都不优雅。

「全文检索怎么做?」

「找出 5 年内报告里提到’黄斑水肿’的所有病历」,MySQL 的 LIKE ‘%黄斑水肿%’ 跑 30 秒还返回一堆误命中。

「地理信息怎么存?」

「某三甲医院在多个院区,患者来源地区分布」,MySQL 没有原生 GIS 类型。

MySQL 不是不行,但「医学数据」用它,总有一种「凑合」的感觉。

这是医学数据工作者普遍面临的「工具不够用」困境:

医学数据类型	MySQL 表现	PostgreSQL 表现
检验报告半结构化文本	塞 VARCHAR/LONGTEXT,查询性能差	JSONB 字段,原生索引
病历全文检索	LIKE 慢、误命中	原生全文检索 + 中文分词
患者来源地理信息	无 GIS 类型,需用 MySQL Spatial Extension	原生 PostGIS,业界标杆
医学术语层级(ICD 父子关系)	递归查询要写复杂 SQL	原生 CTE 递归,简洁清晰
时序数据(生命体征)	无原生时序类型	时间范围类型(range types)
数据完整性	严格度一般	最严格,接近 SQL 标准

[!quote] 狼叔的判断
“HIS/LIS 选 MySQL,科研/医学数据选 PostgreSQL——这是 2026 年医院数据库的最佳实践。” 这一期,带你了解 PostgreSQL,以及怎么用它解决医学数据的痛点。

---

二、工具原理:PostgreSQL 是什么?

2.1 PostgreSQL 是什么?

[!info] PostgreSQL 简介
PostgreSQL(读作「Post-Gres-Q-L」)是一款开源、对象-关系型数据库(ORDBMS)。

它诞生于 1986 年(加州大学伯克利分校),完全开源 + 社区驱动。

江湖地位:

• Stack Overflow 2024 调研:#1 最被开发者喜爱、最被开发者想用的数据库(连续 5 年);
• 医疗领域:NIH(美国国立卫生研究院)所有生物医学数据库底层都是 PostgreSQL;
• 学术界:绝大多数生物信息学数据库(GenBank、UniProt 等)都基于 PostgreSQL。

2.2 4 大独有优势

[!TIP] 优势 ①:JSONB 字段(半结构化数据)

JSONB 是 PostgreSQL 9.4+ 引入的二进制 JSON 类型:

CREATE TABLE 眼底检查报告 (
  报告ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  检查日期 DATE,
  -- JSONB 字段,可以存任意 JSON
  报告内容 JSONB
);

INSERT INTO 眼底检查报告 (病案号, 检查日期, 报告内容) VALUES
('BA001', '2026-06-15', '{
  "视力": {"左眼": 0.6, "右眼": 0.8},
  "眼底": "双眼黄斑水肿,可见微动脉瘤",
  "分期": "增殖期",
  "建议治疗": ["抗VEGF", "激光光凝"]
}');

-- JSONB 查询:找出所有「增殖期」报告
SELECT * FROM 眼底检查报告
WHERE 报告内容->>'分期' = '增殖期';

-- JSONB 查询:找出所有「建议治疗包含抗VEGF」的报告
SELECT * FROM 眼底检查报告
WHERE 报告内容->'建议治疗' ? '抗VEGF';

[!TIP] 优势 ②:全文检索(中文)

PostgreSQL 的全文检索是原生支持,而且支持中文分词(需安装 zhparser 扩展)。

-- 给报告表加全文检索列
ALTER TABLE 眼底检查报告 ADD COLUMN 报告全文 tsvector;

-- 创建触发器,自动把 JSONB 里的中文文本提取到全文检索列
CREATE TRIGGER 报告全文更新
BEFORE INSERT OR UPDATE ON 眼底检查报告
FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(报告全文, 'public.chinese_zh', 报告内容::text);

-- 全文检索:找出「黄斑水肿」相关病历
SELECT 病案号, 检查日期
FROM 眼底检查报告
WHERE 报告全文 @@ plainto_tsquery('chinese_zh', '黄斑水肿')
ORDER BY 检查日期 DESC;

[!TIP] 优势 ③:PostGIS 地理信息

PostGIS 是 PostgreSQL 的地理信息扩展,业界 GIS 标杆。

-- 启用 PostGIS 扩展
CREATE EXTENSION postgis;

-- 患者地址表(经纬度)
CREATE TABLE 患者地址 (
  患者ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  位置 GEOGRAPHY(POINT, 4326)
);

-- 查「医院 5 公里范围内」的所有患者
SELECT 病案号
FROM 患者地址
WHERE ST_DWithin(
  位置,
  ST_MakePoint(114.123, 22.456)::geography,  -- 医院经纬度
  5000  -- 5 公里
);

[!TIP] 优势 ④:丰富数据类型

• 数组类型:TEXT[]、INTEGER[]、JSONB[]
• 范围类型:daterange、numrange(存时间范围/数值范围)
• 枚举类型:CREATE TYPE blood_type AS ENUM ('A', 'B', 'AB', 'O')
• UUID 类型:UUID(医学样本追溯)
• JSON Path:@? '$.症状 ? (@ == "头痛")'

2.3 安装与配置

[!tip] 3 步安装(Windows)

Step 1:下载安装包

• 官网:postgresql.org/download/windows
• 推荐:PostgreSQL 16(2023 年发布,稳定版)

Step 2:运行安装程序

• 默认端口:5432
• 设置超级用户密码:postgres / 123456(学习用,生产环境请强密码)

Step 3:安装 GUI 工具

• DBeaver(免费,推荐):dbeaver.io
• pgAdmin(官方):功能强大但稍慢
• DataGrip(JetBrains,付费,体验最佳)

---

三、医院实战:3 大场景

3.1 场景一:科研病历存储(JSONB + 全文检索)

[!example] 实战 ①:科研病历库
场景:某医院要建立「糖尿病视网膜病变」专病库,存 5 年内 2.3 万份病历 + 眼底报告。

设计:

• 用 PostgreSQL JSONB 存病历的非结构化字段(主诉、现病史、体格检查等)
• 用 PostgreSQL 全文检索支持中文关键词检索
• 用 PostgreSQL 数组字段存「既往史」多个疾病

-- 专病库表结构
CREATE TABLE dr_专病库 (
  病案号 VARCHAR(20) PRIMARY KEY,
  患者ID VARCHAR(20),
  主诊ICD VARCHAR(10),
  入院日期 DATE,
  出院日期 DATE,
  -- 半结构化字段
  病历 JSONB,
  -- 既往史数组
  既往史 TEXT[],
  -- 主治医生
  主治医生 VARCHAR(50)
);

-- 插入示例病历
INSERT INTO dr_专病库 VALUES (
  'BA001', 'P001', 'E11.901',
  '2026-05-10', '2026-05-20',
  '{
    "主诉": "双眼视物模糊 3 个月",
    "现病史": "3 个月前无明显诱因出现视物模糊,伴眼前黑影飘动",
    "体格检查": {
      "视力": {"左眼": 0.5, "右眼": 0.6},
      "眼压": {"左眼": 16, "右眼": 15}
    },
    "诊断": ["2 型糖尿病", "糖尿病视网膜病变(增殖期)", "高血压"]
  }',
  ARRAY['2 型糖尿病(10 年)', '高血压(5 年)', '高脂血症(3 年)'],
  '张医生'
);

-- 查询:找出所有「增殖期」病历
SELECT 病案号, 主治医生
FROM dr_专病库
WHERE 病历->'诊断' ?| ARRAY['糖尿病视网膜病变(增殖期)'];

-- 全文检索:找出所有「黄斑水肿」病历
SELECT 病案号, 主治医生, 入院日期
FROM dr_专病库
WHERE 病历::text ILIKE '%黄斑水肿%'
ORDER BY 入院日期 DESC
LIMIT 100;

3.2 场景二:检验报告半结构化存储

[!example] 实战 ②:检验报告
场景:检验科 LIS 系统每天产生 5000+ 份报告,每份报告含 20-50 项检验项目。

设计:用 JSONB 存每份报告的所有项目值,避免 5000 列的宽表。

-- 检验报告主表
CREATE TABLE 检验报告 (
  报告ID SERIAL PRIMARY KEY,
  病案号 VARCHAR(20),
  检验日期 DATE,
  检验类型 VARCHAR(50),
  -- JSONB 存所有项目结果
  项目结果 JSONB
);

-- 插入血常规报告示例
INSERT INTO 检验报告 (病案号, 检验日期, 检验类型, 项目结果) VALUES
('BA001', '2026-06-15', '血常规', '{
  "白细胞": {"值": 6.5, "单位": "10^9/L", "参考范围": "4.0-10.0", "异常": false},
  "红细胞": {"值": 4.2, "单位": "10^12/L", "参考范围": "4.0-5.5", "异常": false},
  "血红蛋白": {"值": 130, "单位": "g/L", "参考范围": "120-160", "异常": false},
  "血小板": {"值": 350, "单位": "10^9/L", "参考范围": "100-300", "异常": true}
}');

-- 查询:找出所有「血小板异常」报告
SELECT 病案号, 检验日期, 项目结果->'血小板' AS 血小板值
FROM 检验报告
WHERE (项目结果->'血小板'->>'异常')::boolean = true;

3.3 场景三:医学术语层级(ICD 父子关系)

[!example] 实战 ③:ICD 术语树
场景:ICD-10 编码是有层级的(E11 = 2 型糖尿病,E11.9 = 不伴有并发症,E11.901 = 伴有多个并发症)。

设计:用 PostgreSQL 的 CTE 递归查询,3 行 SQL 就能查一个编码的所有父节点。

-- ICD 术语表
CREATE TABLE icd_terms (
  icd_code VARCHAR(10) PRIMARY KEY,
  icd_name VARCHAR(200),
  parent_code VARCHAR(10),
  level INT
);

-- 插入示例
INSERT INTO icd_terms VALUES
('E11', '2 型糖尿病', NULL, 1),
('E11.9', '2 型糖尿病,不伴有并发症', 'E11', 2),
('E11.901', '2 型糖尿病伴多个并发症', 'E11.9', 3);

-- 递归查询:找出「E11.901」的所有父节点
WITH RECURSIVE icd_tree AS (
  SELECT icd_code, icd_name, parent_code, level, 1 AS depth
  FROM icd_terms WHERE icd_code = 'E11.901'
  UNION ALL
  SELECT t.icd_code, t.icd_name, t.parent_code, t.level, tree.depth + 1
  FROM icd_terms t
  INNER JOIN icd_tree tree ON t.icd_code = tree.parent_code
)
SELECT icd_code, icd_name, level, depth
FROM icd_tree
ORDER BY depth DESC;

---

四、避坑指南:5 个让新手「踩雷」的常见错误

[!warning] 雷区 ①:从 MySQL 直接迁移,忘了改语法
症状:LIMIT 10 OFFSET 20 在 PostgreSQL 可以,但 LIMIT 10, 20(逗号写法)是 MySQL 专属。
解决:迁移前查一下 PostgreSQL 兼容性表:compat.postgresql.org。

[!warning] 雷区 ②:JSONB 字段不建索引
症状:WHERE 报告内容->>'分期' = '增殖期' 跑全表扫描,2.3 万行病历要 5 秒。
解决:JSONB 字段建 GIN 索引:CREATE INDEX idx_报告内容 ON 眼底检查报告 USING GIN (报告内容);

[!warning] 雷区 ③:用 VARCHAR(255) 当 TEXT 用
症状:存病历全文时,超过 255 字符被截断。
解决:PostgreSQL 用 TEXT 类型无长度限制,MySQL 那种 VARCHAR(255) 习惯要改。

[!warning] 雷区 ④:不开启 SSL
症状:数据库连接明文传输,病案数据泄露风险。
解决:生产环境必须开启 SSL:pg_hba.conf 里设置 hostssl all all 0.0.0.0/0 md5。

[!warning] 雷区 ⑤:不熟悉 CTE 递归
症状:写层级查询(ICD 父子、组织架构)用了 5 层嵌套子查询,性能差 + 难维护。
解决:用 CTE 递归,见 3.3 节的示例。

---

五、PostgreSQL vs MySQL 怎么选?

[!TIP] 选型决策树

你的数据是什么?
├─ 业务交易数据(HIS/LIS/电子病历)
│   └─ ✅ MySQL(事务强、性能高)
│
├─ 科研数据(专病库、回顾性研究、影像元数据)
│   └─ ✅ PostgreSQL(JSONB + 全文检索 + GIS)
│
├─ 统计分析(BI 报表、领导驾驶舱)
│   └─ ✅ ClickHouse / Doris(列式数据库)
│
└─ 时序数据(生命体征、监护仪数据)
    └─ ✅ InfluxDB / TimescaleDB

结论:HIS 用 MySQL,科研用 PostgreSQL,两者并存,各自发挥优势。

---

六、下期预告

[!quote] 第 12 期
PostgreSQL 进阶 —— JSON 字段 + 全文检索 + GIS 在医学数据中的实战

深入讲 JSONB 查询运算符、全文检索性能调优、PostGIS 实际医院场景。

---

[!TIP] 互动
如果你觉得这篇「医数智联·第 11 期」对你有用,请点赞、在看、转发给科里的同事。

我是白衣狼,一个用数字化工具把医院管理做「轻」的实战派。咱们下期见。