简介2024年全国5万多家医院数据库压缩包覆盖境内几乎所有注册医疗机构的详细信息包括基本信息、地理位置、科室设置、床位数量、医疗设备、医院等级及特色专科等适合医疗管理者、研究人员、公共卫生决策者及有就医需求的公众参考。包内共3个文件主文件为xlsx格式的数据库另附txt版README说明与html版数据来源介绍压缩包整体14.6MB便于快速下载查阅。已有84人学习下载。借助该库可清晰观察医疗资源分布对比同级医院差距辅助资源配置优化患者可依据专科特色选择就医地点从业者可进行市场定位与职业规划研究者亦能利用第一手数据开展医疗体系、管理效率等分析同时其中信息化建设相关内容对推进医疗大数据应用也有参考价值。1. 一份名为“2024年全国5万多家医院数据库.zip”的数据包一份名为2024年全国5万多家医院数据库.zip的数据文件放到面前先别急着双击解压看 Excel。5 万多家医院的名录用 Excel 打开能看但后面做增删改查、按省份汇总、去重、联表Excel 会越来越吃力。这个标题描述的是一个很常见的数据交付场景ZIP 打包的 CSV 机构名录。正确做法是先校验压缩包再把它导入 MySQL做成一张能查询、能验证、能更新的表。下面按这个顺序走一遍适合数据工程师、后端工程师也适合拿真实规模数据做数据库课程设计的人。这里只讨论机构信息公开字段不涉及任何患者数据。2. 解压“医院数据库.zip”前先核对完整性与编码2.1 用 unzip -t 做 CRC 校验别只看压缩包大小先把它重命名成hospital_database_2024.zip。中文文件名不是不能处理但后面 Python 脚本、MySQL 命令都要引用它统一的 ASCII 文件名能减少转义问题。unzip -t是处理 ZIP 的默认动作它能逐文件读取压缩流并比对 CRC 校验值任何一段数据损坏都会直接报错。从网盘或邮件下载的 ZIP 很容易出现传输中断但文件大小看起来正常的情况只解压不校验可能到导入阶段才发现最后几万行断在了半截。unzip -t hospital_database_2024.zip关键输出是每个文件名后面的 OK以及最后一行No errors detected in compressed data of ... files.。只要出现bad CRC或mismatching local header说明这个 ZIP 已经不完整重新获取比事后补救省事得多。如果压缩包很大unzip -t会把整个文件读一遍几十兆的 CSV 不影响几百兆的文件也建议等它跑完。接下来要看压缩包内部结构不用解压全部先列目录zipinfo -1 hospital_database_2024.zip这一条命令回答三个问题里面是一个 CSV 还是多个文件表头文件名是否包含日期或版本有没有 README。医院数据交付包通常不止一个文件主数据表、说明文档、历史版本可能放在不同子目录。如果发现多个 CSV先确认哪个是 2024 年的全量快照再继续。2.2 中文文件名乱码用 unzip -O GBK 处理ZIP 文件内部没有强制规定文件名编码。Windows 压缩的中文文件名一般按 GBK/CP936 写入Linux 的unzip默认按 UTF-8 解码于是解压出来全是乱码。乱码不影响 CSV 内容但后续脚本要glob或os.listdir时文件名对不上就很难受。unzip -O GBK hospital_database_2024.zip -d hospital_2024/-O GBK指定文件名解码字符集-d hospital_2024/把文件解压到单独目录。部分发行版的unzip编译时没有开启-O会提示invalid option。这种情况用7z替代7z x -o./hospital_2024 hospital_database_2024.zip7-Zip 对中文文件名的兼容性好很多。解压后不要急着导入先做两个检查ls -lh hospital_2024/ wc -l hospital_2024/*.csvwc -l输出的行数如果和“5 万多家”的数量级不符只有几百行那可能解压出了样例或者说明文件。主数据文件至少应该有 5 万行以上能提前发现解压不完整或者拿错了文件。2.3 用 file 和 head 核对字符集与换行符医院名录 CSV 最常见的编码是 UTF-8 和 GBK。用file命令看文件编码file hospital_2024/*.csvfile的输出直接决定后面导入参数的写法常见的三种情况可以这样处理file 输出说明处理动作UTF-8 Unicode text字符集正确直接使用ISO-8859 / Non-ISO extended-ASCII大概率是 GBKiconv 转 UTF-8with CRLF line terminatorsWindows 换行sed 去掉行尾\r我会习惯先把 GBK 转成 UTF-8避免把字符集参数写进 SQL 里还要担心表里混入两种编码iconv -f GBK -t UTF-8 hospital_2024/hospital.csv hospital_2024_hospital_utf8.csv转码后再看表头head -3 hospital_2024_hospital_utf8.csv | cat -Acat -A会把行尾的$和 TAB 显示出来。如果看到^M$说明文件是\r\n行尾需要转成 Unix 换行否则导入后最后一列会带\r按条件查询时永远匹配不上。转换命令如下sed -i s/\r$// hospital_2024_hospital_utf8.csv到这里ZIP 才算是真正准备好进入数据库。表头字段顺序要原样抄进建表 SQL下面进入最关键的导入环节。3. 从 ZIP 到 MySQL5 万条医院数据的建表与导入3.1 字段和类型怎么设计拿到 CSV 表头后先画一张字段映射表。医院名录通常有机构名称、省份、城市、区县、级别、类型、地址、电话。机构代码不一定每行都有但也单独建列CSV 表头字段名类型备注机构代码org_codevarchar(32)允许 NULL有值建议建唯一索引机构名称hospital_namevarchar(128)非空省份provincevarchar(32)城市cityvarchar(32)区县districtvarchar(32)医院级别hospital_levelvarchar(16)如“三级甲等”医院类型hospital_typevarchar(16)综合、专科等地址addressvarchar(255)联系电话telephonevarchar(32)不建 int这里最容易犯的错误是把电话设计成BIGINT或INT。电话里的前导零、分机号中的-以及超过 11 位的号码都会被数值类型破坏。所以电话、统一社会信用代码这类字段见到就统一用VARCHAR。地址字段 255 是起步如果 CSV 里有更长的先SELECT MAX(CHAR_LENGTH(address))再定长度。建表语句如下CREATE TABLE hospital ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, org_code VARCHAR(32) NULL COMMENT 机构代码可能为空, hospital_name VARCHAR(128) NOT NULL, province VARCHAR(32) NULL, city VARCHAR(32) NULL, district VARCHAR(32) NULL, hospital_level VARCHAR(16) NULL COMMENT 如三级甲等, hospital_type VARCHAR(16) NULL, address VARCHAR(255) NULL, telephone VARCHAR(32) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_org_code (org_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT医院机构名录;org_code有唯一键可以挡住重复导入如果 CSV 里org_code大量为空唯一键不影响因为 MySQL 允许多个 NULL。库和表的字符集都用utf8mb4不要用utf8。医院名称和地址里会出现生僻字utf8mb4能存 4 字节字符而utf8遇到这类字会报Incorrect string value。3.2 用 LOAD DATA 一次性导入 CSV5 万行数据并不大但如果一行一行INSERT写 Python 脚本也得几十秒到几分钟。用LOAD DATA LOCAL INFILE更符合这类交付包的导入需求一条语句完成全部导入并且能在导入时处理空值。先在 MySQL 客户端里确认参数SET GLOBAL local_infile 1;然后执行导入mysql --local-infile1 -uroot -p hospital_db -e LOAD DATA LOCAL INFILE /data/hospital_2024_hospital_utf8.csv INTO TABLE hospital CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES (org_code, hospital_name, province, city, district, hospital_level, hospital_type, address, telephone); 逐项看参数FIELDS TERMINATED BY ,表示逗号分隔OPTIONALLY ENCLOSED BY 允许被双引号包围的字段这样地址里出现逗号时不会被错误拆列LINES TERMINATED BY \n匹配第 2 章里清理过的行尾IGNORE 1 LINES跳过 CSV 表头。(org_code, ... telephone)的字段顺序必须和 CSV 表头顺序完全一致否则省份会进城市列查出来的数据全是错位。如果 CSV 里没有机构代码就把字段列表里的第一个字段去掉。导入成功后立刻执行SHOW WARNINGS;LOAD DATA的返回值只显示影响行数真正的错误藏在 warnings 里。最常见的是Data truncated原因是字段长度不够或者列类型不匹配。如果 warnings 很多不要盲目重导先把有问题的数据表备份再单独抽几行看源 CSV。这里建议把成功导入后的行数和源文件行数对比wc -l hospital_2024_hospital_utf8.csv mysql -uroot -p hospital_db -e SELECT COUNT(*) FROM hospital;wc -l的行数包含表头行所以源 CSV 为 50001 行时表内应为 50000 行如果偏差大于 1多半还有空行或换行符问题。3.3 导入后的快速验证与索引导入还没结束先做三条查询确认数据形态。第一条总数第二条分省统计第三条找三级甲等医院SELECT COUNT(*) AS total FROM hospital; SELECT province, COUNT(*) AS cnt FROM hospital GROUP BY province ORDER BY cnt DESC; SELECT COUNT(*) AS grade3a_cnt FROM hospital WHERE hospital_level LIKE %三级甲等%;这三条能看出数据是不是真的进来了、省份字段是否为空、级别枚举能不能被搜索到。如果省市为空的行数异常多回到 CSV 检查是否有多余空格或编码问题。确认无误后给常用的组合查询建索引ALTER TABLE hospital ADD INDEX idx_province_city (province, city);province和city是后续按地域筛选的最高频条件。索引不是越多越好5 万行的表即使全表扫描也很快但一旦要接入 Web 查询或重复执行报表这条复合索引的收益非常明显。4. 清洗 5 万条医院数据时的去重与字段标准化4.1 按 org_code 去重还是按名称加城市去重机构代码是医院名录里最可靠的业务主键。如果 CSV 里每行都有非空的org_code直接对org_code去重即可。但现实中很多交付包会把机构代码留空这时组合字段hospital_name province city是次优选择。先用下面的 SQL 找出完全重复的分组SELECT hospital_name, province, city, COUNT(*) AS cnt FROM hospital GROUP BY hospital_name, province, city HAVING cnt 1;输出结果里cnt 2或更高说明同一家医院出现了多条记录。删除重复记录前必须做一次备份CREATE TABLE hospital_backup AS SELECT * FROM hospital;备份表保留原始导入状态后面清洗逻辑出问题可以从备份恢复。MySQL 8.0 下用窗口函数删除重复行保留机构代码非空且 id 最小的一条DELETE h FROM hospital h JOIN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY hospital_name, province, city ORDER BY CASE WHEN org_code IS NULL OR org_code THEN 1 ELSE 0 END, id ) AS rn FROM hospital ) AS t WHERE rn 1 ) AS duplicates ON h.id duplicates.id;这段 SQL 的顺序很重要ROW_NUMBER() OVER (PARTITION BY ...)先按去重键分组ORDER BY CASE把org_code非空的行排到分组内第一id作为第二排序保证顺序稳定。外层再找到所有rn 1的记录删除。如果 MySQL 是 5.7 没有窗口函数可以用临时表加变量实现但 5 万行数据更建议直接升级到 8.0。4.2 用 pandas 清洗全角字符和电话格式SQL 适合按整表去重字段内部字符的清洗用 Python 更直观。下面的脚本把第 2 章转码后的 CSV 读进来统一名称里的全角括号和空格import pandas as pd df pd.read_csv( hospital_2024_hospital_utf8.csv, dtype{telephone: str, org_code: str}, encodingutf-8, ) df df.fillna() df[hospital_name] ( df[hospital_name] .str.strip() .str.replace(r\s, , regexTrue) .str.replace(, (, regexFalse) .str.replace(, ), regexFalse) )dtype{telephone: str}是必须的。医院电话有010-12345678、0551-6223333各种带区号分机的形式默认读入 pandas 会被推测为 object 或 float一旦转成 float 型区号里的-会直接变成缺失值。强制字符串后再按实际需要处理电话里的空格和连接符df[telephone] df[telephone].str.replace(-, , regexFalse)把电话统一成无横线格式适合做快速检索但如果展示时需要区号格式化建议保留一个telephone_raw列不要原地覆盖。医院级别的写法在不同来源里差异很大。三甲和三级甲等是同一级别但直接分组统计会出现两个桶。用映射字典做标准化level_map { 三甲: 三级甲等, 三级甲等: 三级甲等, 三乙: 三级乙等, 三级乙等: 三级乙等, 二甲: 二级甲等, 二级甲等: 二级甲等, } df[hospital_level] df[hospital_level].map(level_map).fillna(其他)注意fillna(其他)会在不确定的数据上打上“其他”标签这没问题但保留原值更稳妥。清洗和标准化不是一回事清洗改格式标准化改口径不要把两者混在一次循环里做完。4.3 清洗后的二次核对清洗完导出新 CSV再走一遍第 3 章的LOAD DATA导入流程。导入后跑四个检查行数对比、唯一组合数、空值数量、省份取值。SELECT COUNT(*) AS total, COUNT(DISTINCT CONCAT_WS(|, hospital_name, province, city)) AS unique_hospital FROM hospital; SELECT province, COUNT(*) AS cnt FROM hospital WHERE province OR province IS NULL;第一句如果total和unique_hospital接近说明去重生效但两个数量相等未必代表没有重复因为CONCAT_WS可能把不同记录拼成同一个值只能作为参考。第二句专门查空省份如果数量很大多半是 CSV 存在全角空格或字段错位。做完这一步表里的数据基本能支撑日常查询。医院数据会有持续更新下一次解压新 ZIP 时应先比对这次清洗规则而不是重复做一遍。5. 验证医院数据库索引优化、查询口径与数据质量5.1 建立匹配查询习惯的索引5 万行表不是大数据量但业务查询通常集中在“按省份、城市筛选”和“名称模糊搜”。前者用复合索引ALTER TABLE hospital ADD INDEX idx_province_city (province, city);执行EXPLAIN看是否命中索引EXPLAIN SELECT province, city, COUNT(*) AS cnt FROM hospital WHERE province 广东省 GROUP BY city ORDER BY cnt DESC;如果possible_keys里有idx_province_citykey字段也指向它说明索引被使用。type一般会是ref如果看到ALL则需要检查省份条件里的写法是否和索引列一致比如字段中有前导空格WHERE province 广东省就匹配不上。名称模糊搜索用LIKE %医院%不会走 B 树索引。5 万行的数据量全表扫描也能接受但想做更完整的检索体验可以给名称列加ngram全文索引ALTER TABLE hospital ADD FULLTEXT INDEX idx_name_ngram (hospital_name) WITH PARSER ngram;ngram是 MySQL 自带的中文分词插件默认分词长度在配置文件里设置。加完索引后查询用MATCH ... AGAINSTSELECT hospital_name, province, city FROM hospital WHERE MATCH(hospital_name) AGAINST(协和医院 IN NATURAL LANGUAGE MODE) LIMIT 20;注意全文索引和LIKE的结果并不等价MATCH按分词匹配LIKE按子串匹配。对机构名称这类短文本LIKE在 5 万行下依然很快全文索引的作用更多是在将来数据量增长后保留一个可扩展方案。5.2 三个数据质量核对技巧第一个技巧是核对行数但不用COUNT(*)单打独斗SELECT COUNT(*) AS total, SUM(org_code IS NULL OR org_code ) AS null_org_code, COUNT(DISTINCT CONCAT_WS(|, hospital_name, province, city)) AS unique_key_cnt FROM hospital;一次查询同时看到总量、机构代码空值和去重键数量。如果null_org_code占比很高后面的更新操作就不能依赖org_code必须切换到组合去重键。第二个技巧是抽样检查。数据量大时逐行核对不现实我习惯用id % 100 0取第 100 行的整数倍做抽查样本SELECT id, hospital_name, province, city, hospital_level FROM hospital WHERE id % 100 0 ORDER BY id LIMIT 100;把抽出来的记录导出成 CSV和源 CSV 同位置的行做人工比对重点看医院名称、省份、电话三列是否错位。抽样通过不代表全量无误但能快速发现字段顺序错位、行尾带\r这类系统性问题。第三个技巧是固定一份“数据校验 SQL”模板每次导入新数据后依次跑一遍。医院名录这种数据会有月度或季度更新别把校验逻辑写在沟通消息里直接保存成verify_hospital.sql下次拿到新的 ZIP 数据包导入完成后先执行mysql hospital_db verify_hospital.sql再配合wc -l对比两个行数数据有没有问题一眼就能看出来。本文还有配套的精品资源点击获取 从ZIP到MySQL：5万医院数据的导入、清洗与索引优化实践
简介2024年全国5万多家医院数据库压缩包覆盖境内几乎所有注册医疗机构的详细信息包括基本信息、地理位置、科室设置、床位数量、医疗设备、医院等级及特色专科等适合医疗管理者、研究人员、公共卫生决策者及有就医需求的公众参考。包内共3个文件主文件为xlsx格式的数据库另附txt版README说明与html版数据来源介绍压缩包整体14.6MB便于快速下载查阅。已有84人学习下载。借助该库可清晰观察医疗资源分布对比同级医院差距辅助资源配置优化患者可依据专科特色选择就医地点从业者可进行市场定位与职业规划研究者亦能利用第一手数据开展医疗体系、管理效率等分析同时其中信息化建设相关内容对推进医疗大数据应用也有参考价值。1. 一份名为“2024年全国5万多家医院数据库.zip”的数据包一份名为2024年全国5万多家医院数据库.zip的数据文件放到面前先别急着双击解压看 Excel。5 万多家医院的名录用 Excel 打开能看但后面做增删改查、按省份汇总、去重、联表Excel 会越来越吃力。这个标题描述的是一个很常见的数据交付场景ZIP 打包的 CSV 机构名录。正确做法是先校验压缩包再把它导入 MySQL做成一张能查询、能验证、能更新的表。下面按这个顺序走一遍适合数据工程师、后端工程师也适合拿真实规模数据做数据库课程设计的人。这里只讨论机构信息公开字段不涉及任何患者数据。2. 解压“医院数据库.zip”前先核对完整性与编码2.1 用 unzip -t 做 CRC 校验别只看压缩包大小先把它重命名成hospital_database_2024.zip。中文文件名不是不能处理但后面 Python 脚本、MySQL 命令都要引用它统一的 ASCII 文件名能减少转义问题。unzip -t是处理 ZIP 的默认动作它能逐文件读取压缩流并比对 CRC 校验值任何一段数据损坏都会直接报错。从网盘或邮件下载的 ZIP 很容易出现传输中断但文件大小看起来正常的情况只解压不校验可能到导入阶段才发现最后几万行断在了半截。unzip -t hospital_database_2024.zip关键输出是每个文件名后面的 OK以及最后一行No errors detected in compressed data of ... files.。只要出现bad CRC或mismatching local header说明这个 ZIP 已经不完整重新获取比事后补救省事得多。如果压缩包很大unzip -t会把整个文件读一遍几十兆的 CSV 不影响几百兆的文件也建议等它跑完。接下来要看压缩包内部结构不用解压全部先列目录zipinfo -1 hospital_database_2024.zip这一条命令回答三个问题里面是一个 CSV 还是多个文件表头文件名是否包含日期或版本有没有 README。医院数据交付包通常不止一个文件主数据表、说明文档、历史版本可能放在不同子目录。如果发现多个 CSV先确认哪个是 2024 年的全量快照再继续。2.2 中文文件名乱码用 unzip -O GBK 处理ZIP 文件内部没有强制规定文件名编码。Windows 压缩的中文文件名一般按 GBK/CP936 写入Linux 的unzip默认按 UTF-8 解码于是解压出来全是乱码。乱码不影响 CSV 内容但后续脚本要glob或os.listdir时文件名对不上就很难受。unzip -O GBK hospital_database_2024.zip -d hospital_2024/-O GBK指定文件名解码字符集-d hospital_2024/把文件解压到单独目录。部分发行版的unzip编译时没有开启-O会提示invalid option。这种情况用7z替代7z x -o./hospital_2024 hospital_database_2024.zip7-Zip 对中文文件名的兼容性好很多。解压后不要急着导入先做两个检查ls -lh hospital_2024/ wc -l hospital_2024/*.csvwc -l输出的行数如果和“5 万多家”的数量级不符只有几百行那可能解压出了样例或者说明文件。主数据文件至少应该有 5 万行以上能提前发现解压不完整或者拿错了文件。2.3 用 file 和 head 核对字符集与换行符医院名录 CSV 最常见的编码是 UTF-8 和 GBK。用file命令看文件编码file hospital_2024/*.csvfile的输出直接决定后面导入参数的写法常见的三种情况可以这样处理file 输出说明处理动作UTF-8 Unicode text字符集正确直接使用ISO-8859 / Non-ISO extended-ASCII大概率是 GBKiconv 转 UTF-8with CRLF line terminatorsWindows 换行sed 去掉行尾\r我会习惯先把 GBK 转成 UTF-8避免把字符集参数写进 SQL 里还要担心表里混入两种编码iconv -f GBK -t UTF-8 hospital_2024/hospital.csv hospital_2024_hospital_utf8.csv转码后再看表头head -3 hospital_2024_hospital_utf8.csv | cat -Acat -A会把行尾的$和 TAB 显示出来。如果看到^M$说明文件是\r\n行尾需要转成 Unix 换行否则导入后最后一列会带\r按条件查询时永远匹配不上。转换命令如下sed -i s/\r$// hospital_2024_hospital_utf8.csv到这里ZIP 才算是真正准备好进入数据库。表头字段顺序要原样抄进建表 SQL下面进入最关键的导入环节。3. 从 ZIP 到 MySQL5 万条医院数据的建表与导入3.1 字段和类型怎么设计拿到 CSV 表头后先画一张字段映射表。医院名录通常有机构名称、省份、城市、区县、级别、类型、地址、电话。机构代码不一定每行都有但也单独建列CSV 表头字段名类型备注机构代码org_codevarchar(32)允许 NULL有值建议建唯一索引机构名称hospital_namevarchar(128)非空省份provincevarchar(32)城市cityvarchar(32)区县districtvarchar(32)医院级别hospital_levelvarchar(16)如“三级甲等”医院类型hospital_typevarchar(16)综合、专科等地址addressvarchar(255)联系电话telephonevarchar(32)不建 int这里最容易犯的错误是把电话设计成BIGINT或INT。电话里的前导零、分机号中的-以及超过 11 位的号码都会被数值类型破坏。所以电话、统一社会信用代码这类字段见到就统一用VARCHAR。地址字段 255 是起步如果 CSV 里有更长的先SELECT MAX(CHAR_LENGTH(address))再定长度。建表语句如下CREATE TABLE hospital ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, org_code VARCHAR(32) NULL COMMENT 机构代码可能为空, hospital_name VARCHAR(128) NOT NULL, province VARCHAR(32) NULL, city VARCHAR(32) NULL, district VARCHAR(32) NULL, hospital_level VARCHAR(16) NULL COMMENT 如三级甲等, hospital_type VARCHAR(16) NULL, address VARCHAR(255) NULL, telephone VARCHAR(32) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_org_code (org_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT医院机构名录;org_code有唯一键可以挡住重复导入如果 CSV 里org_code大量为空唯一键不影响因为 MySQL 允许多个 NULL。库和表的字符集都用utf8mb4不要用utf8。医院名称和地址里会出现生僻字utf8mb4能存 4 字节字符而utf8遇到这类字会报Incorrect string value。3.2 用 LOAD DATA 一次性导入 CSV5 万行数据并不大但如果一行一行INSERT写 Python 脚本也得几十秒到几分钟。用LOAD DATA LOCAL INFILE更符合这类交付包的导入需求一条语句完成全部导入并且能在导入时处理空值。先在 MySQL 客户端里确认参数SET GLOBAL local_infile 1;然后执行导入mysql --local-infile1 -uroot -p hospital_db -e LOAD DATA LOCAL INFILE /data/hospital_2024_hospital_utf8.csv INTO TABLE hospital CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES (org_code, hospital_name, province, city, district, hospital_level, hospital_type, address, telephone); 逐项看参数FIELDS TERMINATED BY ,表示逗号分隔OPTIONALLY ENCLOSED BY 允许被双引号包围的字段这样地址里出现逗号时不会被错误拆列LINES TERMINATED BY \n匹配第 2 章里清理过的行尾IGNORE 1 LINES跳过 CSV 表头。(org_code, ... telephone)的字段顺序必须和 CSV 表头顺序完全一致否则省份会进城市列查出来的数据全是错位。如果 CSV 里没有机构代码就把字段列表里的第一个字段去掉。导入成功后立刻执行SHOW WARNINGS;LOAD DATA的返回值只显示影响行数真正的错误藏在 warnings 里。最常见的是Data truncated原因是字段长度不够或者列类型不匹配。如果 warnings 很多不要盲目重导先把有问题的数据表备份再单独抽几行看源 CSV。这里建议把成功导入后的行数和源文件行数对比wc -l hospital_2024_hospital_utf8.csv mysql -uroot -p hospital_db -e SELECT COUNT(*) FROM hospital;wc -l的行数包含表头行所以源 CSV 为 50001 行时表内应为 50000 行如果偏差大于 1多半还有空行或换行符问题。3.3 导入后的快速验证与索引导入还没结束先做三条查询确认数据形态。第一条总数第二条分省统计第三条找三级甲等医院SELECT COUNT(*) AS total FROM hospital; SELECT province, COUNT(*) AS cnt FROM hospital GROUP BY province ORDER BY cnt DESC; SELECT COUNT(*) AS grade3a_cnt FROM hospital WHERE hospital_level LIKE %三级甲等%;这三条能看出数据是不是真的进来了、省份字段是否为空、级别枚举能不能被搜索到。如果省市为空的行数异常多回到 CSV 检查是否有多余空格或编码问题。确认无误后给常用的组合查询建索引ALTER TABLE hospital ADD INDEX idx_province_city (province, city);province和city是后续按地域筛选的最高频条件。索引不是越多越好5 万行的表即使全表扫描也很快但一旦要接入 Web 查询或重复执行报表这条复合索引的收益非常明显。4. 清洗 5 万条医院数据时的去重与字段标准化4.1 按 org_code 去重还是按名称加城市去重机构代码是医院名录里最可靠的业务主键。如果 CSV 里每行都有非空的org_code直接对org_code去重即可。但现实中很多交付包会把机构代码留空这时组合字段hospital_name province city是次优选择。先用下面的 SQL 找出完全重复的分组SELECT hospital_name, province, city, COUNT(*) AS cnt FROM hospital GROUP BY hospital_name, province, city HAVING cnt 1;输出结果里cnt 2或更高说明同一家医院出现了多条记录。删除重复记录前必须做一次备份CREATE TABLE hospital_backup AS SELECT * FROM hospital;备份表保留原始导入状态后面清洗逻辑出问题可以从备份恢复。MySQL 8.0 下用窗口函数删除重复行保留机构代码非空且 id 最小的一条DELETE h FROM hospital h JOIN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY hospital_name, province, city ORDER BY CASE WHEN org_code IS NULL OR org_code THEN 1 ELSE 0 END, id ) AS rn FROM hospital ) AS t WHERE rn 1 ) AS duplicates ON h.id duplicates.id;这段 SQL 的顺序很重要ROW_NUMBER() OVER (PARTITION BY ...)先按去重键分组ORDER BY CASE把org_code非空的行排到分组内第一id作为第二排序保证顺序稳定。外层再找到所有rn 1的记录删除。如果 MySQL 是 5.7 没有窗口函数可以用临时表加变量实现但 5 万行数据更建议直接升级到 8.0。4.2 用 pandas 清洗全角字符和电话格式SQL 适合按整表去重字段内部字符的清洗用 Python 更直观。下面的脚本把第 2 章转码后的 CSV 读进来统一名称里的全角括号和空格import pandas as pd df pd.read_csv( hospital_2024_hospital_utf8.csv, dtype{telephone: str, org_code: str}, encodingutf-8, ) df df.fillna() df[hospital_name] ( df[hospital_name] .str.strip() .str.replace(r\s, , regexTrue) .str.replace(, (, regexFalse) .str.replace(, ), regexFalse) )dtype{telephone: str}是必须的。医院电话有010-12345678、0551-6223333各种带区号分机的形式默认读入 pandas 会被推测为 object 或 float一旦转成 float 型区号里的-会直接变成缺失值。强制字符串后再按实际需要处理电话里的空格和连接符df[telephone] df[telephone].str.replace(-, , regexFalse)把电话统一成无横线格式适合做快速检索但如果展示时需要区号格式化建议保留一个telephone_raw列不要原地覆盖。医院级别的写法在不同来源里差异很大。三甲和三级甲等是同一级别但直接分组统计会出现两个桶。用映射字典做标准化level_map { 三甲: 三级甲等, 三级甲等: 三级甲等, 三乙: 三级乙等, 三级乙等: 三级乙等, 二甲: 二级甲等, 二级甲等: 二级甲等, } df[hospital_level] df[hospital_level].map(level_map).fillna(其他)注意fillna(其他)会在不确定的数据上打上“其他”标签这没问题但保留原值更稳妥。清洗和标准化不是一回事清洗改格式标准化改口径不要把两者混在一次循环里做完。4.3 清洗后的二次核对清洗完导出新 CSV再走一遍第 3 章的LOAD DATA导入流程。导入后跑四个检查行数对比、唯一组合数、空值数量、省份取值。SELECT COUNT(*) AS total, COUNT(DISTINCT CONCAT_WS(|, hospital_name, province, city)) AS unique_hospital FROM hospital; SELECT province, COUNT(*) AS cnt FROM hospital WHERE province OR province IS NULL;第一句如果total和unique_hospital接近说明去重生效但两个数量相等未必代表没有重复因为CONCAT_WS可能把不同记录拼成同一个值只能作为参考。第二句专门查空省份如果数量很大多半是 CSV 存在全角空格或字段错位。做完这一步表里的数据基本能支撑日常查询。医院数据会有持续更新下一次解压新 ZIP 时应先比对这次清洗规则而不是重复做一遍。5. 验证医院数据库索引优化、查询口径与数据质量5.1 建立匹配查询习惯的索引5 万行表不是大数据量但业务查询通常集中在“按省份、城市筛选”和“名称模糊搜”。前者用复合索引ALTER TABLE hospital ADD INDEX idx_province_city (province, city);执行EXPLAIN看是否命中索引EXPLAIN SELECT province, city, COUNT(*) AS cnt FROM hospital WHERE province 广东省 GROUP BY city ORDER BY cnt DESC;如果possible_keys里有idx_province_citykey字段也指向它说明索引被使用。type一般会是ref如果看到ALL则需要检查省份条件里的写法是否和索引列一致比如字段中有前导空格WHERE province 广东省就匹配不上。名称模糊搜索用LIKE %医院%不会走 B 树索引。5 万行的数据量全表扫描也能接受但想做更完整的检索体验可以给名称列加ngram全文索引ALTER TABLE hospital ADD FULLTEXT INDEX idx_name_ngram (hospital_name) WITH PARSER ngram;ngram是 MySQL 自带的中文分词插件默认分词长度在配置文件里设置。加完索引后查询用MATCH ... AGAINSTSELECT hospital_name, province, city FROM hospital WHERE MATCH(hospital_name) AGAINST(协和医院 IN NATURAL LANGUAGE MODE) LIMIT 20;注意全文索引和LIKE的结果并不等价MATCH按分词匹配LIKE按子串匹配。对机构名称这类短文本LIKE在 5 万行下依然很快全文索引的作用更多是在将来数据量增长后保留一个可扩展方案。5.2 三个数据质量核对技巧第一个技巧是核对行数但不用COUNT(*)单打独斗SELECT COUNT(*) AS total, SUM(org_code IS NULL OR org_code ) AS null_org_code, COUNT(DISTINCT CONCAT_WS(|, hospital_name, province, city)) AS unique_key_cnt FROM hospital;一次查询同时看到总量、机构代码空值和去重键数量。如果null_org_code占比很高后面的更新操作就不能依赖org_code必须切换到组合去重键。第二个技巧是抽样检查。数据量大时逐行核对不现实我习惯用id % 100 0取第 100 行的整数倍做抽查样本SELECT id, hospital_name, province, city, hospital_level FROM hospital WHERE id % 100 0 ORDER BY id LIMIT 100;把抽出来的记录导出成 CSV和源 CSV 同位置的行做人工比对重点看医院名称、省份、电话三列是否错位。抽样通过不代表全量无误但能快速发现字段顺序错位、行尾带\r这类系统性问题。第三个技巧是固定一份“数据校验 SQL”模板每次导入新数据后依次跑一遍。医院名录这种数据会有月度或季度更新别把校验逻辑写在沟通消息里直接保存成verify_hospital.sql下次拿到新的 ZIP 数据包导入完成后先执行mysql hospital_db verify_hospital.sql再配合wc -l对比两个行数数据有没有问题一眼就能看出来。本文还有配套的精品资源点击获取