早期系統大量使用 ntext、text、image 類型存儲長文本/二進制數據。需批量替換其中特定字符串(如域名遷移、敏感詞過濾),但這些類型不支持直接 UPDATE ... SET col = REPLACE(col, ...),必須使用 UPDATETEXT 配合 TEXTPTR 逐塊操作。
TEXTPTR 指針。PATINDEX 定位目標字符串位置。UPDATETEXT 就地替換該片段。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;
-- 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;
iwms_news(主體)與 iwms_pages(分頁),需分別執行兩次替換。UPDATETEXT 逐塊寫入,大量替換會產生巨大事務日誌。建議:
RECOVERY SIMPLE 臨時簡單模式(備份後恢復 FULL)。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 ...;
NVARCHAR(MAX) 列,同步雙寫或一次性遷移數據。UPDATETEXT 是「考古級」技能,僅用於無法立即遷移的舊系統維護。新專案請堅決使用 MAX 類型,擁抱現代 T-SQL 能力。