构建视频 URL 规范化流水线
我在生产环境的 SQLite 数据库上运行了一个查询，发现了一个巨大的问题。
videos 表有 41,283 行，但只有 29,000 个唯一的标题。我们多次存储了相同的视频。
为什么会发生这种情况？同一个 YouTube 视频以不同的 URL 格式进入：
• 短链接：youtu.be/ID
• 桌面端链接：youtube.com/watch?v=ID
• 移动端链接：m.youtube.com/watch?v=ID
• Shorts：youtube.com/shorts/ID
一个视频意味着四行数据库记录和四个几乎相同的网页。这浪费了我的抓取预算，并在 Google Search Console 中触发了错误。
我经营着 TrendVidStream。我们从八个不同的地区获取趋势数据。每个地区都可能使用不同的 URL 形式呈现同一个热门视频。
我使用 PHP 8.4 和 SQLite 构建了一个流水线来解决这个问题。
流水线的工作原理如下：
- 提取 (Extract)：将任何 URL 变体转换为稳定的 11 位视频 ID。
- 规范化 (Normalize)：根据该 ID 创建一个单一的规范 (canonical) URL。
- 验证 (Validate)：在操作数据库之前进行快速检查。
- Upsert：使用 SQLite 的 UPSERT 功能来合并数据，而不是添加新行。
- 输出 (Emit)：添加
canonical标签和 301 重定向，以便搜索引擎只找到一个 URL。
关键技术决策：
• 使用精确的域名白名单。不要使用后缀匹配。这可以防止安全风险。
• 永远不要将 ID 转为小写。YouTube ID 是区分大小写的。将其转为小写会导致不同的视频被错误合并。
• 在 video_id 上使用 UNIQUE 约束。这是防止重复的最强防御。
• 使用 SQLite 的 WAL 模式。这能让写入操作既快速又可靠。
结果：
我们的视频表从 41,283 行减少到了 28,094 行。我们没有丢失任何视频。Google Search Console 的错误从 412 个降到了 9 个。搜索结果变得更加整洁，因为我们不再重复显示同一个视频四次。
教训很简单：当你从多个来源聚合数据时，首先要在系统中建立身份识别机制。提取一个稳定的 ID，通过数据库约束来强制执行它，并确保每次写入都是幂等的 (idempotent)。
你不需要复杂的工具。PHP 和 SQLite 就足够了。