萬盛學電腦網

瀏覽器 Windows 10 WPS Office 防毒軟體 資料庫 Excel 教學 檔案管理 Word 教學 網頁製作 裝機必備軟體 Linux 教學

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

MySQL REPLACE() 進階批量替換實戰：多場景組合拳、編碼修正、日期標準化與性能優化

延續基礎篇，本文聚焦 REPLACE() 在**複雜數據清洗管線**中的組合應用：多字符並行替換、編碼統一 (全角/半角、中文標點)、日期格式標準化管線、大表分批無鎖執行、應用層流式處理架構。通過 es_product.pro_pub_time 真實場景演示從「臟數據」到「標準化日期」的完整 ETL 過程。

一、多目標並行替換策略

1. 串行 UPDATE (簡單、可控、慢)

UPDATE `t` SET `c` = REPLACE(`c`, 'a', 'x') WHERE INSTR(`c`, 'a')>0;
UPDATE `t` SET `c` = REPLACE(`c`, 'b', 'y') WHERE INSTR(`c`, 'b')>0;
UPDATE `t` SET `c` = REPLACE(`c`, 'c', 'z') WHERE INSTR(`c`, 'c`)>0;

缺點：多次全表掃描、多次鎖衝突、Binlog 放大。

2. 單語句嵌套 REPLACE (單掃描、單鎖、單 Binlog)

UPDATE `t`
SET `c` = REPLACE(REPLACE(REPLACE(`c`, 'a', 'x'), 'b', 'y'), 'c', 'z')
WHERE INSTR(`c`, 'a')>0 OR INSTR(`c`, 'b')>0 OR INSTR(`c`, 'c')>0;

優勢：單次全表掃描、單次鎖、單次 Binlog 寫入、原子性保證所有替換同時生效或全不生效。

注意：替換順序影響結果 (如先替 'a'→'b' 再替 'b'→'c' 會連鎖)，**按業務邏輯精心排序**。

3. CASE WHEN 條件分支替換 (不同列不同規則)

UPDATE `t`
SET `c` = CASE
    WHEN `c` LIKE '%pattern1%' THEN REPLACE(`c`, 'pattern1', 'repl1')
    WHEN `c` LIKE '%pattern2%' THEN REPLACE(`c`, 'pattern2', 'repl2')
    ELSE `c`
END
WHERE `c` LIKE '%pattern1%' OR `c` LIKE '%pattern2%';

二、編碼統一組合拳：全角/半角、中英文標點

-- 統一全角字母數字轉半角 (需自定義函數或應用層處理，MySQL 無內置)
-- 示例：手動列舉常見全角字符
UPDATE `t` SET `c` = REPLACE(`c`, 'ａ', 'a') WHERE INSTR(`c`, 'ａ')>0;
-- ... 批量處理 ａ-ｚ Ａ-Ｚ ０-９

-- 統一中文標點為英文標點 (視業務需求)
UPDATE `t` SET `c` = REPLACE(`c`, '，', ',') WHERE INSTR(`c`, '，')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '。', '.') WHERE INSTR(`c`, '。')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '？', '?') WHERE INSTR(`c`, '？')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '！', '!') WHERE INSTR(`c`, '！')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '：', ':') WHERE INSTR(`c`, '：')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '；', ';') WHERE INSTR(`c`, '；')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '（', '(') WHERE INSTR(`c`, '（')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '）', ')') WHERE INSTR(`c`, '）')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '【', '[') WHERE INSTR(`c`, '【')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '】', ']') WHERE INSTR(`c`, '】')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '「', '"') WHERE INSTR(`c`, '「')>0;
UPDATE `t` SET `c` = REPLACE(`c`, '」', '"') WHERE INSTR(`c`, '」')>0;

工程化建議：將映射表存儲在配置表/代碼常量中，動態生成 SQL 或應用層統一處理，避免硬編碼分散維護。

三、日期格式標準化管線：從混沌到 ISO 8601

場景：es_product.pro_pub_time 字段為 VARCHAR，存儲極不規範日期字符串：'2005年04月'、'2005-04-'、'2005-'、'2005'、'2005.04.01'、'2005/04/01'、'05-04-01' 等。

標準化步驟 (按依賴順序執行，每步分批)

步驟 1：清理無關字符

UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`);
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '[', '') WHERE INSTR(`pro_pub_time`, '[')>0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, ']', '') WHERE INSTR(`pro_pub_time`, ']')>0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.', '-') WHERE INSTR(`pro_pub_time`, '.')>0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '/', '-') WHERE INSTR(`pro_pub_time`, '/')>0;

步驟 2：統一中文連字符

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '－', '-') WHERE INSTR(`pro_pub_time`, '－')>0;

步驟 3：中文年月日轉標準分隔符

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '年', '-') WHERE INSTR(`pro_pub_time`, '年')>0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '月', '-') WHERE INSTR(`pro_pub_time`, '月')>0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '日', '') WHERE INSTR(`pro_pub_time`, '日')>0;

步驟 4：補全不完整日期 (關鍵邏輯，需按長度/模式分層)

-- '2005-04-' (長度>5, 以 - 結尾) → '2005-04-01'
UPDATE `es_product` 
SET `pro_pub_time` = CONCAT(`pro_pub_time`, '01')
WHERE SUBSTRING_INDEX(`pro_pub_time`, '-', -1) = '' 
  AND LENGTH(`pro_pub_time`) > 5;

-- '2005-' (長度=5, 含 -) → '2005-01-01'
UPDATE `es_product` 
SET `pro_pub_time` = CONCAT(`pro_pub_time`, '01-01')
WHERE INSTR(`pro_pub_time`, '-') > 0 
  AND LENGTH(`pro_pub_time`) = 5;

-- 含 '-' 但長度 < 8 (如 '2005-04') → 追加 '-01'
UPDATE `es_product` 
SET `pro_pub_time` = CONCAT(`pro_pub_time`, '-01')
WHERE INSTR(`pro_pub_time`, '-') > 0 
  AND LENGTH(`pro_pub_time`) < 8;

-- 純年份 '2005' (長度=4, 無 -) → '2005-01-01'
UPDATE `es_product` 
SET `pro_pub_time` = CONCAT(`pro_pub_time`, '-01-01')
WHERE INSTR(`pro_pub_time`, '-') = 0 
  AND LENGTH(`pro_pub_time`) = 4;

步驟 5：最終格式化輸出 (轉 DATE/DATETIME 類型列或標準字符串)

-- 選項 A: 更新為標準字符串 'YYYY-MM-DD'
UPDATE `es_product` 
SET `pro_pub_time` = DATE_FORMAT(STR_TO_DATE(`pro_pub_time`, '%Y-%m-%d'), '%Y-%m-%d')
WHERE INSTR(`pro_pub_time`, '-') > 0 
  AND STR_TO_DATE(`pro_pub_time`, '%Y-%m-%d') IS NOT NULL;

-- 選項 B (推薦): 新增 DATE 列, 回填, 切換, 刪舊列
ALTER TABLE `es_product` ADD `pub_date` DATE;
UPDATE `es_product` 
SET `pub_date` = STR_TO_DATE(`pro_pub_time`, '%Y-%m-%d')
WHERE STR_TO_DATE(`pro_pub_time`, '%Y-%m-%d') IS NOT NULL;
-- 驗證無誤後
ALTER TABLE `es_product` DROP `pro_pub_time`, CHANGE `pub_date` `pro_pub_time` DATE;

四、大表無鎖分批執行框架 (生產級)

<?php
/**
 * 大表字符串替換/清洗無鎖執行器
 * 策略: 主鍵範圍分批 + 只更新需變更行 + 進度持久化 + 監控指標
 */
class LargeTableCleaner {
    private PDO $pdo;
    private string $table;
    private string $pk;
    private string $col;
    private array $replacements; // [['from'=>'年', 'to'=>'-'], ...]
    private int $batchSize = 2000;
    private int $sleepUs = 100000;
    private string $progressTable = '_cleaner_progress';

    public function __construct(PDO $pdo, string $table, string $pk, string $col, array $replacements) {
        $this->pdo = $pdo;
        $this->table = $table;
        $this->pk = $pk;
        $this->col = $col;
        $this->replacements = $replacements;
        $this->initProgressTable();
    }

    private function initProgressTable() {
        $this->pdo->exec("CREATE TABLE IF NOT EXISTS `$this->progressTable` (
            `task_name` VARCHAR(100) PRIMARY KEY,
            `last_pk` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `total_affected` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB");
    }

    public function run(string $taskName): array {
        $lastPk = $this->getLastPk($taskName);
        $totalAffected = 0;
        $batch = 0;

        while (true) {
            $sql = $this->buildBatchSql($lastPk);
            $stmt = $this->pdo->prepare($sql);
            $params = array_merge(
                array_values(array_column($this->replacements, 'from')),
                array_values(array_column($this->replacements, 'to')),
                array_values(array_column($this->replacements, 'from')), // FOR WHERE INSTR
                [$lastPk, $this->batchSize]
            );
            $stmt->execute($params);
            $affected = $stmt->rowCount();

            if ($affected === 0) break;

            $totalAffected += $affected;
            $batch++;
            $lastPk = $this->getMaxPkInBatch($lastPk);
            $this->saveProgress($taskName, $lastPk, $totalAffected);

            // 監控指標上報 (示例: 發送到 StatsD/Prometheus Pushgateway)
            $this->reportMetrics($taskName, $batch, $affected, $totalAffected);

            usleep($this->sleepUs);
        }
        return ['batches' => $batch, 'total_affected' => $totalAffected];
    }

    private function buildBatchSql(int $lastPk): string {
        $col = "`{$this->col}`";
        $pk = "`{$this->pk}`";
        $table = "`{$this->table}`";

        // 構建嵌套 REPLACE: REPLACE(REPLACE(col, ? , ?), ?, ?) ...
        $nested = $col;
        foreach ($this->replacements as $r) {
            $nested = "REPLACE($nested, ?, ?)";
        }

        // WHERE 條件: 任一目標字符串存在 AND pk > lastPk
        $whereConds = [];
        foreach ($this->replacements as $r) {
            $whereConds[] = "INSTR($col, ?) > 0";
        }
        $where = implode(' OR ', $whereConds) . " AND $pk > ?";

        return "UPDATE $table SET $col = $nested WHERE $where ORDER BY $pk LIMIT ?";
    }

    private function getLastPk(string $taskName): int {
        $stmt = $this->pdo->prepare("SELECT `last_pk` FROM `$this->progressTable` WHERE `task_name` = ?");
        $stmt->execute([$taskName]);
        return (int)($stmt->fetchColumn() ?? 0);
    }

    private function getMaxPkInBatch(int $lastPk): int {
        $where = implode(' OR ', array_map(fn($r) => "INSTR(`{$this->col}`, ?) > 0", $this->replacements));
        $params = array_values(array_column($this->replacements, 'from'));
        $params[] = $lastPk;
        $params[] = $this->batchSize;
        $sql = "SELECT MAX(`{$this->pk}`) FROM `{$this->table}` WHERE $where AND `{$this->pk}` > ? LIMIT ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)($stmt->fetchColumn() ?? $lastPk);
    }

    private function saveProgress(string $taskName, int $lastPk, int $total) {
        $this->pdo->prepare("REPLACE INTO `$this->progressTable` (`task_name`, `last_pk`, `total_affected`) VALUES (?, ?, ?)")
            ->execute([$taskName, $lastPk, $total]);
    }

    private function reportMetrics(string $task, int $batch, int $affected, int $total) {
        // 實現發送到監控系統
        // file_put_contents("/tmp/$task.metrics", json_encode([...]), FILE_APPEND);
    }
}

// 使用示例
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "user", "pass");
$cleaner = new LargeTableCleaner($pdo, 'es_product', 'id', 'pro_pub_time', [
    ['from' => '年', 'to' => '-'],
    ['from' => '月', 'to' => '-'],
    ['from' => '日', 'to' => ''],
    ['from' => '－', 'to' => '-'],
    ['from' => '.', 'to' => '-'],
    ['from' => '/', 'to' => '-'],
]);
$result = $cleaner->run('es_product_pub_time_standardize');
print_r($result);
?>

五、應用層流式處理架構 (超大表/實時/複雜邏輯)

┌─────────────┐     ┌──────────────┐     ┌─────────────┐     ┌─────────────┐
│ Binlog/CDC  │────▶│ Stream Proc  │────▶│  Transform  │────▶│  Batch Upsert│
│ (Canal/Debez)   │  (Flink/KSQL/  │     │  (Regex,    │     │  (PK IN ...) │
│             │     │  Custom App) │     │   Lookup,   │     │             │
└─────────────┘     └──────────────┘     │   State)    │     └─────────────┘
                                           └─────────────┘

優勢：零數據庫負擔、毫秒級延遲、狀態化處理 (會話窗口、去重)、精確一次語義、水平擴展、邏輯復用 (同邏輯服務多下游)。

結語

MySQL REPLACE() 批量替換從「單條 SQL」進化到「分批無鎖框架」再到「流式架構」，折射出數據量級增長對工程模式的倒逼。核心心法：**數據庫只負責存儲與索引，計算邏輯上移應用層/流式層**。對於日期標準化這類確定性轉換，構建「配置驅動的清洗管線」+「冪等分批執行器」+「全鏈路可觀測」，才是應對數據治理長期戰役的正確姿勢。

上一篇程序編程： MySQL REPLACE() 批量替換字符串實戰：語法、條件控制、編碼陷阱與大表分批策略
下一篇程序編程： Linux/Windows 下 MySQL Root 密碼重置完整指南：skip-grant-tables、init-file、系統服務管理與安全加固