您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 實戰：清洗抓取文章的 HTML 標籤與字串函數深度解析

編輯：MySQL 綜合教學

背景與挑戰

專案上線後發現抓取的攻略文章夾雜大量外部連結與失效圖片。初期以 PHP 正則批量清洗，後因資料量大、圖片缺失無法重抓，需直接在資料庫層面精準移除特定 HTML 區塊。本文記錄從 PHP 到 SQL 的演進，並系統整理 LOCATE、REPLACE、SUBSTRING 等核心字串函數用法。

一、 PHP 階段：正則過濾外鏈

public static function filter_newslink($aid) {
    $content = mod_news::get_newscont($aid);
    // 移除圖片外層  標籤，保留 
    $content = preg_replace('/<a (.*)>(<img.*>)<\/a>/i', '${2}', $content);
    // 移除文字超連結，保留純文字
    $content = preg_replace('/<a (.*)>(.*)<\/a>/i', '${2}', $content);
    return mod_news::update_newscontent(['news_id' => $aid, 'content' => $content]);
}

二、 SQL 階段：精準切除「卡牌數值」圖片區塊

利用 MySQL 字串函數組合，定位關鍵字「卡牌數值」至註釋結束符「-->」之間的內容，替換為收尾標籤：

UPDATE CONTENT_TABLE c
SET c.content = REPLACE(
    c.content,
    SUBSTRING(
        c.content,
        POSITION('卡牌數值' IN c.content),
        POSITION('-->' IN c.content) - POSITION('卡牌數值' IN c.content) + 3
    ),
    '</span></h3></div>'
)
WHERE c.news_id IN (SELECT news_id FROM NEWS_TABLE WHERE col_id = 66);

核心邏輯： POSITION 定位起始與結束位置，SUBSTRING 截取目標片段，REPLACE 原地替換。雖效率一般，但針對百篇文章完全可接受。

三、核心字串函數深度解析

1. 位置查找三劍客：`LOCATE`、`POSITION`、`INSTR`

LOCATE(substr, str) ≡ POSITION(substr IN str) ≡ INSTR(str, substr) —— 返回子串首次出現位置（1-based），不存在返回 0。
LOCATE(substr, str, pos) 支援指定起始位置查找。
多字節安全，MySQL 4.0+ 僅在參數含二進位字串時區分大小寫。
效能優於 LIKE '%keyword%'，適合高頻判斷是否包含關鍵字。

2. `REPLACE` 兩大用法

用法一：字串替換函數 `REPLACE(str, from_str, to_str)`

UPDATE `table` SET `name` = REPLACE(`name`, '斗三國', '全民斗三國');

全域替換，多字節安全。

用法二：`REPLACE INTO` —— 插入或更新（Upsert）

語法：REPLACE INTO tbl (col...) VALUES (...) 或 REPLACE INTO tbl SET col=val...

機制：嘗試插入 → 若主鍵/唯一索引衝突 → 刪除舊行 → 插入新行。返回影響行數（刪除+插入）。

⚠ 陷阱：會改變自增主鍵值，導致關聯表外鍵失效、主鍵耗盡。嚴禁用於有外鍵關聯或高頻寫入的表。

3. `SUBSTRING` 四種標準語法

SUBSTRING(str, pos)
SUBSTRING(str FROM pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos FOR len)

支援負數 pos（從尾部倒數）。
FROM 寫法符合標準 SQL。
搭配 POSITION/LOCATE 可實現動態長度截取。

4. 實用切割函數速查

函數	說明	範例
`LEFT(str, len)`	從左截取	`SELECT LEFT(title, 10) FROM news`
`RIGHT(str, len)`	從右截取	`SELECT RIGHT(url, 3) FROM links`
`SUBSTRING_INDEX(str, delim, count)`	按分隔符切割	`SUBSTRING_INDEX('a.b.c', '.', 2) → 'a.b'`；`count` 為負數時從右向左

總結

資料清洗無銀彈，PHP 適合結構化邏輯，SQL 適合定位明確的批量修補。熟練掌握 LOCATE/POSITION/INSTR 定位、SUBSTRING 截取、REPLACE 替換三板斧，配合 REPLACE INTO 的 Upsert 特性，能解決 90% 的髒資料治理場景。切記：REPLACE INTO 的副作用，生產環境請三思！