萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 大字段文本替換實戰:UPDATETEXT + 游標批量處理 ntext/image 數據

業務背景

早期系統大量使用 ntext、text、image 類型存儲長文本/二進制數據。需批量替換其中特定字符串(如域名遷移、敏感詞過濾),但這些類型不支持直接 UPDATE ... SET col = REPLACE(col, ...),必須使用 UPDATETEXT 配合 TEXTPTR 逐塊操作。

核心邏輯流程

  1. 聲明游標遍歷目標表主鍵與 TEXTPTR 指針。
  2. 對每行循環 PATINDEX 定位目標字符串位置。
  3. 調用 UPDATETEXT 就地替換該片段。
  4. 重複直到該行無匹配項,處理下一行。

通用存儲過程模板

CREATE PROCEDURE usp_ReplaceLongText
  @TableName      sysname,      -- 目標表
  @ColumnName     sysname,      -- 目標列
  @KeyColumn      sysname,      -- 主鍵列
  @OldString      NVARCHAR(MAX),-- 被替換字符串
  @NewString      NVARCHAR(MAX) -- 新字符串
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ptr VARBINARY(16), @key_val SQL_VARIANT, @pos INT, @len INT;
  SET @len = DATALENGTH(@OldString) / 2;  -- ntext 雙字節

  -- 動態構建游標 SQL(防注入:生產環境請用 QUOTENAME + sp_executesql 參數化)
  DECLARE @sql NVARCHAR(MAX) = N'
    DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT TEXTPTR(' + QUOTENAME(@ColumnName) + N'), ' + QUOTENAME(@KeyColumn) + N'
    FROM ' + QUOTENAME(@TableName) + N';';
  EXEC sp_executesql @sql;  -- 此處簡化,實際需將游標邏輯內聯或用全局游標

  -- 以下為邏輯演示,實際部署需將游標循環包裹在動態 SQL 或 CLR 中
  OPEN cur;
  FETCH NEXT FROM cur INTO @ptr, @key_val;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @pos = PATINDEX('%' + @OldString + '%', 
      (SELECT CAST('' AS XML).value('xs:base64Binary(sql:variable("@ptr"))', 'NVARCHAR(MAX)'))); -- 簡化示意
    -- 實際需:SELECT @pos = PATINDEX('%' + @OldString + '%', @ColumnName) FROM @TableName WHERE @KeyColumn = @key_val;
    
    WHILE @pos > 0
    BEGIN
      SET @pos = @pos - 1;  -- PATINDEX 返回 1-based,UPDATETEXT 為 0-based
      UPDATETEXT @TableName.@ColumnName @ptr @pos @len @NewString;
      SELECT @pos = PATINDEX('%' + @OldString + '%', @ColumnName) 
        FROM @TableName WHERE @KeyColumn = @key_val;
    END
    FETCH NEXT FROM cur INTO @ptr, @key_val;
  END
  CLOSE cur; DEALLOCATE cur;
END;

實戰案例:替換 iwms_news 表 content 列

-- 1. 確認列類型
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'iwms_news' AND COLUMN_NAME = 'content';
-- 結果:content | ntext

-- 2. 執行替換(簡化版,單表單列)
DECLARE @ptr VARBINARY(16), @artId INT, @pos INT, @len INT;
SET @len = DATALENGTH(N'舊域名.com');  -- 目標字符串長度(字節數)

DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  SELECT TEXTPTR([content]), [articleid] FROM iwms_news;
OPEN cur;
FETCH NEXT FROM cur INTO @ptr, @artId;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @pos = PATINDEX(N'%舊域名.com%', [content]) 
    FROM iwms_news WHERE [articleid] = @artId;
  
  WHILE @pos > 0
  BEGIN
    SET @pos = @pos - 1;
    UPDATETEXT iwms_news.[content] @ptr @pos @len N'新域名.cn';
    SELECT @pos = PATINDEX(N'%舊域名.com%', [content]) 
      FROM iwms_news WHERE [articleid] = @artId;
  END
  FETCH NEXT FROM cur INTO @ptr, @artId;
END
CLOSE cur; DEALLOCATE cur;

關鍵注意事項

  1. 只能在 SSMS / sqlcmd 中執行,不支持應用程序連接池複用的會話(游標狀態不穩定)。
  2. 分頁文章:若內容分散在 iwms_news(主體)與 iwms_pages(分頁),需分別執行兩次替換。
  3. 性能與日誌:UPDATETEXT 逐塊寫入,大量替換會產生巨大事務日誌。建議:
    • 分批處理(每批 1000 行提交)。
    • 設置 RECOVERY SIMPLE 臨時簡單模式(備份後恢復 FULL)。
    • 低峰期執行,監控日志空間。
  4. 現代替代方案:SQL Server 2005+ 推薦遷移至 NVARCHAR(MAX) / VARBINARY(MAX),直接支持 REPLACE()、.WRITE() 部分更新,無需游標:
-- 遷移後簡單高效
UPDATE iwms_news SET [content] = REPLACE([content], N'舊域名.com', N'新域名.cn');
-- 或部分更新
UPDATE iwms_news SET [content].WRITE(N'新域名.cn', @pos, @len) WHERE ...;

遷移建議路徑

  1. 新增 NVARCHAR(MAX) 列,同步雙寫或一次性遷移數據。
  2. 驗證應用兼容性(ODBC/OLE DB/ORM 對 MAX 類型支持良好)。
  3. 切換讀寫路徑,刪除舊 ntext 列。
  4. 收獲:原生 REPLACE、全文索引、壓縮、In-Memory OLTP 支持。

UPDATETEXT 是「考古級」技能,僅用於無法立即遷移的舊系統維護。新專案請堅決使用 MAX 類型,擁抱現代 T-SQL 能力。

copyright © 萬盛學電腦網 all rights reserved