写爬虫的人肯定都经历过这个阶段一开始用 CSV、JSON 存数据觉得轻巧方便跑了一个月后发现查数据靠读全文件、更新数据靠重写全量、看到重复记录还得自己手工去重整个人直接麻了。所以爬虫学到“数据保存与入库”这一块MySQL 和 PostgreSQL 就是绕不开的两个关键词。这一节我打算用一篇完整的内容把关系型数据库入门阶段最实用的东西串一遍表结构到底怎么设计、索引什么时候该加、以及真正解决爬虫重复数据难题的 Upsert 思想。它适合所有刚跑通爬虫但还没正经用过数据库的初学者哪怕你连 SQL 都没写过一行只要跟着思路走也能把“抓到的数据安全落库”这件事做好。1. 为什么爬虫做到这一步必须认真选数据库1.1 数据量一上来文件存储先扛不住很多爬虫教程走到解析完数据就结束了示例代码里都是json.dump()或者to_csv()看起来很简单但实际项目里这种方案只能撑住小体量。我自己踩过的坑是这样的第一个爬虫项目攒了大概二十万条商品价格记录全部堆在几个 CSV 文件里。一开始我写了个脚本加载全部数据去重跑一次要十几秒。后来越攒越多加载一次就要半分钟而且一旦进程中途崩溃整个文件可能损坏之前抓的全白费。最难受的是“更新一条记录”——你得先读整个文件替换目标行再写回去效率低到让人怀疑人生。这就是数据库存在的意义它把数据组织成结构化的表查询走索引更新走事务并发读写也有成熟机制。爬虫数据落库之后你再也不用担心“文件太大加载不动”“重复数据没法高效清洗”这种问题后面哪怕要按时间范围统计、按关键词过滤也只是一条 SQL 的事。1.2 MySQL 和 PostgreSQL 怎么选先看需求再看名气每次讲到数据库总有人问一句“到底学哪个”。我的观点是先搞清楚两者的差异再按自己的场景选。MySQL 的优点是生态成熟、资料多、安装简单入门教程遍地都是。尤其在国内互联网公司MySQL 的使用率非常高你以后去面试、去接手老项目大概率碰到的就是它。PostgreSQL 则是功能更全面JSONB 类型、更严格的约束机制、更丰富的索引类型写起复杂查询来经常能省不少事。用一句不算严谨但很好记的话概括MySQL 像一把基础扎实的瑞士军刀PostgreSQL 更像一个功能繁多的工具箱。为了让你看得更直观我做了个对比表维度MySQLPostgreSQL学习资料丰富度极高中文资料多较少但官方文档质量高默认字符集处理需要主动设置 utf8mb4原生 UTF-8 体验更好JSON 支持JSON 类型起步较晚JSONB 强大且支持索引Upsert 语法ON DUPLICATE KEY UPDATEON CONFLICT...DO UPDATE适合场景通用业务、快速上手复杂查询、数据清洗分析给初学者的建议也很简单如果你当下就想把爬虫数据存起来、希望出问题时能快速搜到答案选 MySQL如果你愿意多花两三天适应 PostgreSQL 的命令和习惯并且后面可能要拿这些数据做复杂统计分析选 PostgreSQL。两个都学一遍最好反正 SQL 基础是通的学会一个另一个迁移成本非常低。2. 建表前必须懂的几个基础概念2.1 表、字段、主键和 Excel 其实很像很多人一听“关系型数据库”就觉得高深其实你把它想成强化版 Excel 就很好理解。一张表就是一个二维表格行是一条记录列是一个字段。比如你爬淘宝商品价格每条商品记录就是一行商品名、价格、抓取时间分别是三列。Python 里的一个dict对应一行数据dict里的每个 key 就是字段名value 就是字段值。主键也简单它是每一行数据的唯一标识相当于身份证号。一旦设了主键数据库就不允许出现两行相同主键的数据。很多人一开始不懂把主键设成自增数字id这没问题但爬虫场景光有自增 id 不够因为同一条网页链接可能被爬两次每次插入都会生成新的 id数据照样重复。所以后面还要引入唯一键的概念这一点我放在第 3 章详细讲。2.2 爬虫表里最常见的字段类型建表时最常纠结的问题就是“这个字段该用什么类型”。我给你梳理一份爬虫场景最高频的类型清单INTEGER/BIGINT存数量、id。如果数值可能超过二十亿直接上BIGINT。VARCHAR(n)存字符串比如标题、名称。n 是最大字符数别把范围设太大够用就行。TEXT存长文本。爬虫爬到的正文、描述往往很长用TEXT比较合适。DECIMAL(10,2)/NUMERIC(10,2)存价格、金额。涉及小数运算一定别用FLOAT会出现精度问题。DATETIME/TIMESTAMP存抓取时间、更新时间。JSON/JSONB存原始网页解析后的完整数据。PostgreSQL 的 JSONB 还能直接参与查询非常推荐。你在 Python 里可能习惯把所有东西都塞进一个dict但数据库设计更讲究“拆开”。原则很简单经常要用来过滤、排序的字段单独建列比如价格、发布时间不常查、但又想保留的原始数据丢进 JSON 字段里存着既不丢信息也不让表结构变得臃肿。2.3 主键和唯一键去重的第一道防线没有做过数据库的人常忽略一个问题爬虫程序重启、请求重试、断点续爬都会导致同一条数据被抓两次。如果不加约束表里就会出现大量重复记录。主键约束保证主键列不重复但很多爬虫数据本身没有天然主键。这时候你需要“唯一键”去声明某个字段不能重复。比如商品详情页的 URL理论上和商品是一一对应的把它设成唯一键后第二次插入相同 URL 时数据库就会报错。这其实是好事因为报错意味着你可以捕获异常决定是跳过还是更新。我见过不少新手在这段时间乱设唯一键比如把商品名设成唯一结果一个商品在不同店铺卖名字一样但实际根本不是同一条数据。唯一键一定要选“业务上不会重复”的字段。URL、平台内部 ID、url 的 hash 值都比名字可靠得多。3. 表设计实操从零设计一张不后悔的数据表3.1 设计前先列字段再做减法我在设计爬虫表时第一步永远是先想清楚“我到底要拿这些数据做什么”。比如我要爬某个电商网站的商品价格需求是每天记录商品价格变化顺便保留每次抓到的原始响应。这时业务字段就能列出来了商品名称商品链接业务唯一标识当前价格抓取时间原始 JSON 响应看起来好像很简单但如果我顺手加一个“来源站点”字段后面再爬其他站点时同一张表就能复用如果加一个updated_at字段就能知道这条记录最近一次更新是什么时候如果加一个url_hash字段就能让唯一索引长度更短、效率更高。这些都是在实际使用中慢慢补出来的经验。拿我刚才这个需求表设计可以精简成下面这样3.2 MySQL 建表 DDL 实操MySQL 建表语句给你贴一份可以直接用的CREATE TABLE product_price ( id BIGINT AUTO_INCREMENT PRIMARY KEY, source_site VARCHAR(50) NOT NULL DEFAULT , product_name VARCHAR(200) NOT NULL, source_url VARCHAR(2000) NOT NULL, url_hash CHAR(40) NOT NULL, price DECIMAL(10, 2) NOT NULL, raw_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_url_hash (url_hash), KEY idx_source_site (source_site), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这里有个非常关键的坑source_url本身虽然是业务唯一标识但 URL 很长在 utf8mb4 字符集下直接给整个 URL 建唯一索引很容易突破索引长度限制。所以我是用url_hash字段存 URL 的哈希值比如 SHA1 的 40 位十六进制字符串。这样既能保证唯一性又能让索引体积小很多。created_at字段用了数据库默认时间插入时不需要在 Python 代码里传值。updated_at在 MySQL 里配合ON UPDATE CURRENT_TIMESTAMP每次行记录被更新时自动刷新非常适合记录爬虫采集时间。3.3 PostgreSQL 建表 DDL 实操及差异同样的表换成 PostgreSQL 的写法CREATE TABLE product_price ( id BIGSERIAL PRIMARY KEY, source_site VARCHAR(50) NOT NULL DEFAULT , product_name VARCHAR(200) NOT NULL, source_url TEXT NOT NULL, url_hash CHAR(40) NOT NULL, price NUMERIC(10, 2) NOT NULL, raw_json JSONB NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uk_product_url UNIQUE (url_hash) ); CREATE INDEX idx_source_site ON product_price (source_site); CREATE INDEX idx_created_at ON product_price (created_at);两边的差异其实很明显。PostgreSQL 的自增主键不是AUTO_INCREMENT而是BIGSERIALNUMERIC和 MySQL 的DECIMAL等价JSONB 比 MySQL 的 JSON 类型更强可以直接用raw_json-title这样写 SQL 做过滤。唯一的遗憾是 PostgreSQL 没有ON UPDATE CURRENT_TIMESTAMP这种自动更新列所以我在代码里插入和更新时手动赋值updated_at NOW()。如果你想追求自动化也可以触发器搞定但爬虫项目里手动维护完全够用。3.4 默认值、非空和注释三个月后的救命稻草很多新手建表时为了省事什么约束都不加结果爬虫遇到字段缺失就把整条记录丢了特别可惜。我给所有爬虫表的建议是能用默认值的字段全给默认值。比如source_site默认created_at默认当前时间计数类字段默认 0。这样即使 Python 代码里少传一个参数数据库也不会报错还能维持数据完整性。字段注释也一定要写。CREATE TABLE里每个字段后面可以加COMMENTPostgreSQL 里用COMMENT ON COLUMN table.column IS ...。你可能现在觉得多余但三个月后你再回来看表看到url_hash这个字段绝对会忘记当初为什么要加它。注释就是写给未来自己看的。非空约束NOT NULL要慎用。只有业务上绝对必须有值的字段才设非空比如主键。像raw_json、备注这类字段没有程度就让它空着别因为非空约束把整条采集任务搞崩。4. 索引给数据库建目录别乱建4.1 索引到底为什么能快你可以把表想象成一本书没有索引的表就像一本没有目录的书。你要找“Python爬虫”这几个字只能从第一页翻到最后一页这叫全表扫描。有了索引相当于在书最后加了个目录页码路径都排好序一查就知道去哪页速度自然快。数据库索引最常见的底层结构是 B 树它把索引列的值排好序查询时会按二分查找的方式快速定位到目标行。听起来复杂但使用的时候你根本不需要理解树内部只需要知道查询条件里用了哪个字段这个字段上如果有索引查询就会快很多。不过索引不是越多越好。每建一个索引插入和更新数据时都要额外维护这个索引结构。爬虫高频插入的场景里索引过多会拖慢写入速度。所以建索引的原则是只给查询频率高、区分度高的字段建。4.2 爬虫表里最值得建的两种索引整理了一下爬虫表最常遇到的查询需求其实就两种按业务标识查单条记录按时间范围查批次数据。对应地最值得建的索引也是两种。第一种是唯一索引直接建在url_hash或者平台唯一 ID 上它一方面保证业务数据不重复另一方面让按业务标识查询的速度接近“按主键查询”的级别非常划算。第二种是普通索引建在created_at这类的字段上方便你做完采集后按时间范围统计“今天抓了多少条”“最近一周价格变化了多少”。另外如果经常要按“某个来源 时间范围”一起查可以考虑建一个联合索引比如(source_site, created_at)。注意联合索引里字段顺序有讲究最常用作等值查询的列放前面范围查询的列放后面。4.3 用 EXPLAIN 看一眼查询到底走没走索引索引建了到底有没有生效别靠猜。MySQL 和 PostgreSQL 都提供了EXPLAIN关键字可以在真实执行查询前把执行计划打印出来。EXPLAIN SELECT * FROM product_price WHERE url_hash abc123...;在 MySQL 里重点看type字段如果显示ALL说明全表扫描索引没走显示const或ref就是精准索引匹配。在 PostgreSQL 里看Seq Scan字样出现它就表示顺序扫描没走索引看到Index Scan或Bitmap Index Scan说明索引起了作用。我在实际排查慢查询时经常遇到一种情况查询条件字段上明明有索引但因为它被包在函数里比如WHERE LOWER(url_hash) ...索引直接失效。这种问题等到索引篇后半段再展开你先记住一句话别在查询条件里对索引字段做运算。5. 保存数据的主角Upsert 思想5.1 为什么爬虫数据特别需要 Upsert如果你只是把数据一股脑 INSERT 进数据库很快就撞上唯一键冲突。这时新手通常会先SELECT查一遍查不到再INSERT查到了就UPDATE。流程听着没问题但存在两个隐患一是多一次查询就多一次网络往返数据量大时不划算二是“查和写”两步之间如果发生并发操作很容易出现重复数据。Upsert 的思想就是“有则更新无则插入”。它把“查 判断 写”合并成一次数据库操作数据库自己判断记录存不存在存在就按你写的规则更新不存在就插入。这个操作在爬虫场景里完美契合了“增量更新”的需求每天重新爬一遍页面同一 URL 的数据被重复采集时直接覆盖旧数据新 URL 则自动插入。可以说Upsert 是爬虫入库最核心的保存思想没有之一。5.2 MySQL 的 ON DUPLICATE KEY UPDATEMySQL 里实现 Upsert 的语法是ON DUPLICATE KEY UPDATE。它的含义是当插入的数据触发了唯一键冲突不再报错而是转换成更新操作。给一个实际的 SQL 示例INSERT INTO product_price (source_site, product_name, source_url, url_hash, price, raw_json) VALUES (shop_a, 无线鼠标, https://example.com/mouse, abc123..., 99.00, {name:无线鼠标}) ON DUPLICATE KEY UPDATE product_name VALUES(product_name), price VALUES(price), raw_json VALUES(raw_json), updated_at CURRENT_TIMESTAMP;这里的VALUES(product_name)指的就是当前这条待插入数据里的product_name。意思很清楚如果url_hash对应的记录已经存在就用新数据覆盖商品名、价格和原始 JSON同时刷新更新时间。MySQL 8.0.20 之后官方开始推荐更严格的别名写法INSERT INTO product_price (source_site, product_name, source_url, url_hash, price, raw_json) VALUES (shop_a, 无线鼠标, https://example.com/mouse, abc123..., 99.00, {name:无线鼠标}) AS new ON DUPLICATE KEY UPDATE product_name new.product_name, price new.price;实际项目里两种写法都能用但如果你是刚起步直接用旧版就行等升级版本后再慢慢迁移。5.3 PostgreSQL 的 ON CONFLICT 怎么用PostgreSQL 对应的是ON CONFLICT语法。它比 MySQL 更规范一点你需要在语句里明确“冲突的目标”是什么唯一约束再决定冲突后怎么做。拿之前那张表举例url_hash上有唯一约束那么写法就是这样INSERT INTO product_price (source_site, product_name, source_url, url_hash, price, raw_json) VALUES (shop_a, 无线鼠标, https://example.com/mouse, abc123..., 99.00, {name:无线鼠标}) ON CONFLICT (url_hash) DO UPDATE SET product_name EXCLUDED.product_name, price EXCLUDED.price, raw_json EXCLUDED.raw_json, updated_at NOW();看到那个EXCLUDED了吗它代表“本应该插入但因冲突被排除掉”的那一行数据。换句话说EXCLUDED.price就是准备插入的新价格。这套语义很直观我要插入的数据遇到冲突时拿自己手里的新值把旧记录更新掉。如果冲突时你什么都不想干只要跳过就可以写DO NOTHING。这在断点续爬、防止重复插入时会很好用。5.4 配合 Python 的完整落库代码光有 SQL 还不够得看怎么在 Python 里用起来。我用最方便的pymysql和psycopg2各给一段示例基本套路是一样的。MySQL 版import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasecrawler, charsetutf8mb4 ) sql INSERT INTO product_price (source_site, product_name, source_url, url_hash, price, raw_json) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE product_name VALUES(product_name), price VALUES(price), raw_json VALUES(raw_json), updated_at CURRENT_TIMESTAMP data (shop_a, 无线鼠标, https://example.com/mouse, abc123..., 99.00, {name:无线鼠标}) with conn.cursor() as cursor: cursor.execute(sql, data) conn.commit()PostgreSQL 版import psycopg2 conn psycopg2.connect( host127.0.0.1, userpostgres, passwordyour_password, databasecrawler ) sql INSERT INTO product_price (source_site, product_name, source_url, url_hash, price, raw_json) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (url_hash) DO UPDATE SET product_name EXCLUDED.product_name, price EXCLUDED.price, raw_json EXCLUDED.raw_json, updated_at NOW() data (shop_a, 无线鼠标, https://example.com/mouse, abc123..., 99.00, {name:无线鼠标}) with conn.cursor() as cursor: cursor.execute(sql, data) conn.commit()注意两边的参数占位符不一样pymysql用%spsycopg2也是用%s所以这里几乎可以无缝切换。实际采集时你通常会在一个循环里对多条数据执行executemany做批量 Upsert效率和单条执行完全不是一个级别。另外提一句如果你用 SQLAlchemy它也有方言级的 Upsert 用法我这里不展开写。但逻辑是一模一样的只是换层皮。6. 实操过程中最容易踩的坑6.1 连接数据库最常见的几个报错跑爬虫的人第一次连数据库十个有八个会遇到下面这些报错MySQL 这边最经典的是Cant connect to MySQL server on localhost和ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock。意思很简单MySQL 服务没启动或者你连的地址、端口不对。Windows 上要先确认服务在“服务管理器”里已经启动Linux 上执行systemctl status mysql看状态。还有一个容易忽略的是 Python 连接时用了localhost某些环境会走 Unix socket如果你只想走 TCP就明确写127.0.0.1不要写localhost。PostgreSQL 这边新手遇到最多的是password authentication failed for user postgres。这通常是安装时设置的密码和连接时传入的密码不一致。另外默认端口是 5432不要和 MySQL 的 3306 搞混。连接问题解决后接着就是编码问题。中文乱码、表情符号存不进去十有八九是字符集没配置对。连接 MySQL 时记得带上charsetutf8mb4建库建表时也统一用utf8mb4。PostgreSQL 原生就能处理 Unicode乱码的概率小很多。6.2 批量插入和更新怎么才能更快爬虫采集是高频操作大多是插入和更新。想要快第一个优化点是“不要一条一条 commit”。你可以把一次任务里的几十、几百条数据攒到一个事务里最后统一commit()这样读写次数从 N 次变成 1 次速度提升非常明显。在 Python 里用executemany批量执行同一条 SQL再把所有数据整理成列表传入是最省事的方式。另一个优化点是控制单批数据的条数。数据量太大时SQL 长度可能超过数据库允许范围网络传输时间长也容易在中间断掉。我习惯每批 500 到 1000 条既能利用批量插入的优势也能把单次失败的损失控制在可接受范围内。如果并发量再高一点就要考虑连接池。每次请求都新建数据库连接的开销很大SQLAlchemy 自带连接池用它能省不少事。初学阶段单连接跑爬虫够用但如果你开多线程抓取就必须让每个线程从连接池里拿连接用完释放否则很快会报“too many connections”。6.3 常见问题速查表我把自己遇到过的、以及身边朋友踩过的坑整理成一张速查表你可以直接收藏现象可能原因快速处理MySQL 连接超时服务没启动 / 端口错误 / 防火墙拦截检查服务状态使用 127.0.0.1 连接MySQL 建表报索引长度超限URL 直接做唯一索引改用 url_hash 字段建索引中文乱码表字符集或连接字符集不是 utf8mb4建表用 utf8mb4连接加 charset 参数PostgreSQL 认证失败密码错误 / pg_hba.conf 配置问题重置密码或修改认证方式插入重复数据报错唯一键冲突改用 Upsert 语法让数据库合并处理查询很慢索引没生效 / 查询条件有函数运算用 EXPLAIN 分析调整索引或查询方式表行数特别多但查询很快插入很慢索引过多只保留高频查询的索引数据类型不符报错Python 传了字符串给数字字段在入库前做一次类型转换这张表虽然不能覆盖所有问题但爬虫入库阶段的高频坑基本都在里面了。6.4 说说数据库连接池和并发这件事爬虫跑到多线程阶段你一定会遇到数据库连接不够用的问题。有人说“开十个线程就创建十个连接”结果一个线程崩溃就把连接池拖垮了。我自己的习惯是优先用 SQLAlchemy 的create_engine它内部维护连接池设置一个合适的pool_size和max_overflow让并发线程从池子里拿连接。用完连接后在with块里自动归还避免连接泄漏。这个操作虽然不起眼但在长时间跑爬虫任务时省下来的报错排查时间非常可观。如果你的爬虫只是单线程跑暂时不需要考虑连接池但一定要记住连接用完要关掉。哪怕程序正在报错也要在异常处理里保证conn.close()被调用到。长时间不关连接数据库会积累大量空闲连接最终连不上自己的库。7. 保存数据之外我给爬虫加的两个小习惯最后分享两个我自己一直在用的小习惯谈不上标准答案但对长期维护爬虫项目帮助很大。每张爬虫表我都会额外放一个raw_json字段把网页解析后的原始结构完整存进去。这样做最大的好处是以后表结构设计得不合理或者数据清洗规则要改还能从原始 JSON 里重新提取、重新入库不用重新爬一遍。另一个习惯是采集任务完成后跑一遍简单的统计 SQL比如看看今天新增了多少行、更新了多少行、有没有大量重复数据。这能帮我在数据源头出问题的时候第一时间发现异常而不是等到分析阶段才发现数据已经坏了。数据库这东西初次接触会觉得概念又多又枯燥但只要动手建一张表、跑通一次 Upsert后面的东西基本都是举一反三。希望这篇内容能帮你把 Python 爬虫的数据保存阶段顺利走完。