一、立项背景与目标
本项目面向专利数据研究工作,将 CNIPA 公开的中国专利数据(40 个 CSV 文件,合计 110 GB,5007 万条记录)导入 MySQL 8.0.28,建立可供研究分析使用的关系型数据库。
目标为支持三类分析需求:按城市、年份、专利类型组合检索单条专利记录;按城市与年份进行分组聚合统计;保留原始数据以便回溯核验。
二、功能模块
1. 数据导入模块
按文件分批解析源数据,处理字段数不一致(34 列为主,部分年份存在尾部空列或额外列)、UTF-8 BOM 头、CRLF 换行与控制字符等异常。
2. 清洗与建模模块
统一字段语义;城市字段存在 440 种写法(含「北京市」与「北京」并存、台湾县市、省直辖县级市等),经映射表归并为标准城市名;按申请号、专利类型、城市、申请年组合去重,区分「申请公开」与「授权公告」两类记录。
3. 检索与聚合模块
在城市与年份维度建立复合索引,提供明细查询与分组聚合两类接口。
4. 数据校验模块
采用逐文件行数核对、与官方年报比对、内部行数等式三种方式验证数据完整性。
三、业务流程
源文件分批解析 → 装入暂存表 → 字段标准化 → 城市名归并 → 去重 → 生成主表 → 建立索引 → 三层校验 → 对外提供查询接口。
一、整体架构
数据库为 MySQL 8.0.28,存储引擎 InnoDB,共 9 张表。主表 patent_clean 存储清洗后数据,数据量 157.57 GB,行数 5007 万,索引占用 5.94 GB。原始底库 patent 保留全部 37 列明细,数据量 123.66 GB,用于回溯。其余 7 张表为聚合结果表、城市映射表与装载进度台账。
二、负责模块与量化结果
1. 数据导入。41 个源文件全部导入,逐文件核对解析记录数与库内行数,41/41 一致。
2. 锁表问题处理。INSERT ... SELECT 在 REPEATABLE READ 隔离级别下对每行加共享锁,触发 ERROR 1206(锁表溢出),事务回滚。经 INNODB_TRX 确认锁定行数为 79,628,448。调整 binlog_format 为 ROW、隔离级别为 READ COMMITTED 后,锁定行数降至 15,550,事务正常完成。
3. 索引优化。未建索引时按城市与年份查询超过 120 秒并触发 Error 2013(客户端超时)。通过在线 DDL 增加 (city, app_year) 复合索引(ALGORITHM=INPLACE,LOCK=NONE,执行期间不阻塞写入),降至 0.03 秒。
4. 断点续跑。装载按 40 万行分块提交,每块完成后写入进度台账 etl_chunk_progress。断电重启后从未完成分块继续,不重复导入。
5. 数据校验。逐文件行数核对;与 CNIPA 官方年报比对;内部行数等式 59,512,027 − 705,360 − 675,935 − 86 = 58,130,732,与按专利类型分项之和一致。
三、难点与解决方案
1. 年报比对口径。官方年报「合计」栏包含国外申请(2006 年国外占比约 42%),直接比对会得出数据缺失过半的错误结论,故采用「国内」栏。
2. 覆盖率差异。发明申请 84.6% 至 99.6%(2017 年起官方口径由受理量改为申请量);实用新型 71% 至 94%(本库收录授权公告记录);外观设计 60% 至 90%(部分申请未公开)。差异均源于统计口径,已在数据说明中记录。
3. 服务关闭静默失败。mysqladmin shutdown 因配置参数残留未生效,脚本未检查返回码,导致误判为已关闭。改用 SQL SHUTDOWN,并增加 3306 端口探测确认连接断开。