突破T-SQL限制：利用CLR整合擴充套件RDSSQLServer的功能邊界

2025-05-07 18:29 阿里雲開發者

阿里妹導讀
CLR整合為SQL Server提供了強大的擴充套件能力，突破了T-SQL的限制，極大地拓展了SQL 的應用場景，如：複雜字串處理、高效能計算、影像處理、機器學習整合、自定義加密解密等，使開發人員能夠利用 .NET Framework的豐富功能來處理複雜的資料庫任務。
一、前言
SQL Server 的 Common Language Runtime (CLR) 整合是一種允許開發人員使用 .NET Framework 的程式語言（如 C# ）編寫和執行儲存過程、觸發器、使用者自定義函式（UDF）以及其他資料庫物件的功能。透過 CLR 整合，SQL Server 可以利用 .NET 能力處理更復雜的任務，例如字串操作、檔案處理、正則表示式解析等。這為開發人員提供了更強大的工具，尤其是在面對計算密集型任務或處理 T-SQL 無法有效解決的業務邏輯時，CLR 整合成為一種靈活且高效的解決方案。
本文透過DEMO，詳細為大家介紹如何在阿里雲資料庫RDS SQL Server（下文簡稱 RDS SQL Server）中部署和使用CLR整合函式。
二、環境準備
先決條件
如果要部署CLR整合，首先需要配置 RDS SQL Server例項級引數“clr enabled”為1（該值預設為0）。登入RDS SQL Server控制檯，進入引數設定頁面，找到 clr enabled引數，即可啟用CLR選項，如圖1所示：
圖1.設定“clr enabled”引數值為1 
DEMO程式：情緒偏好分析函式
下面我們透過一個DEMO程式（簡單的情緒偏好分析函式）闡述如何將CLR部署到RDS SQL Server。該DEMO程式設定一個簡單的詞庫和分詞邏輯，給定評論內容，將內容與詞庫中的詞簡單對比，得出情緒分。
DEMO程式 C# 程式碼如下：
publicclassSentimentAnalysis{    [SqlFunction(IsDeterministic = true, IsPrecise = true)]publicstatic SqlDouble AnalyzeSentiment(SqlString text){if (text.IsNull)return SqlDouble.Null;        var sentimentDictionary = InitializeSentimentDictionary();string input = text.Value.ToLower();double sentimentScore = 0;int wordCount = 0;int i = 0;while (i < input.Length)        {bool matched = false;// 從當前位置開始嘗試匹配詞典中的最長詞語            foreach (var entry in sentimentDictionary.Keys.OrderByDescending(k => k.Length))            {if (i + entry.Length <= input.Length && input.Substring(i, entry.Length) == entry)                {                    sentimentScore += sentimentDictionary[entry];                    wordCount++;                    i += entry.Length;  // 跳過已匹配的詞語                    matched = true;break;                }            }// 如果沒有匹配到詞典中的詞語，則跳過該字元if (!matched)            {                i++;            }        }returnnew SqlDouble(wordCount > 0 ? sentimentScore / wordCount : 0);    }privatestatic Dictionary<string, double> InitializeSentimentDictionary(){returnnew Dictionary<string, double>        {            {"好", 1.0}, {"喜歡", 1.0}, {"優秀", 1.0}, {"棒", 1.0}, {"滿意", 0.8},            {"不錯", 0.6}, {"還行", 0.2}, {"一般", 0},            {"差", -0.6}, {"糟糕", -0.8}, {"失望", -0.8}, {"爛", -1.0}, {"討厭", -1.0}        };    }}
三、將CLR程式集匯入RDS SQL Server
匯出CLR程式集為二進位制字串
資料庫作為PaaS層程式，使用者無法訪問RDS SQL Server所在的作業系統，因此不能直接編譯如下“建立程式集”的程式碼。我們可以先將C#的CLR程式碼編譯為DLL，然後透過擁有OS訪問許可權的RDS SQL Server建立程式集，如下程式碼所示。
-- 建立程式集CREATE ASSEMBLY [CLRFuncDemo]FROM 'E:\Backup\CLRFuncDemo.dll'WITH PERMISSION_SET = SAFE
接著，透過RDS SQL Server的匯出功能，將該程式集匯出為二進位制字串，如圖2。
圖2.將CLR程式集匯出為二進位制字串
匯入CLR程式集至RDS SQL Server

通過擁有當前指定資料庫DBO許可權的使用者連入RDS SQL Server，將匯出的CLR程式集透過二進位制的方式進行建立，如圖3所示。

圖3.匯入程式集，並建立函式
在圖4中，可以看到程式集與CLR函式已經被成功匯入與建立。
圖4.程式集與函式位置
測試CLR函式
下面我們透過一個簡單的表測試資料，DEMO測試CLR函式的可用性，程式碼如下所示。
CREATE TABLE UserReviews(    ReviewID INT IDENTITY(1,1) PRIMARY KEY,    Username NVARCHAR(50),    ProductName NVARCHAR(100),    ReviewContent NVARCHAR(MAX),    ReviewDate DATETIME)INSERT INTO UserReviews(Username,ProductName,ReviewContent,ReviewDate)VALUES('Chen Qi','Smartphone E',N'這個手機太爛了,電池續航糟糕,我非常失望!','2024-03-25 13:00:00'),('Zhou Ba','Laptop F',N'體驗非常差,散熱不好,效能糟糕,不推薦!','2024-03-26 14:30:00'),('Sun Jiu','Headphones G',N'音質太差了,這耳機真是糟糕透頂,讓我很失望!','2024-03-27 16:10:00'),('Wu Shi','Tablet H',N'這個平板還行,效能一般,用起來也算可以.','2024-03-28 10:00:00'),('Zhang Yi','Smartphone I',N'手機用起來一般,沒有特別好,也沒有特別差,算是中規中矩.','2024-03-29 11:20:00'),('Li Si','Laptop B',N'這個筆記本真的是優秀! 效能很棒,非常喜歡,滿意極了!','2024-03-20 09:15:00'),('Wang Wu','Headphones C',N'聲音非常棒,材質很好,使用體驗一流,我非常滿意!','2024-03-22 11:00:00'),('Zhao Liu','Tablet D',N'這款平板真是不錯,效能很強,續航也不錯,我非常喜歡!','2024-03-24 15:45:00'),('Wang Kang','Smartphone K',N'手機非常好用,拍照效果非常棒,我非常喜歡!','2024-03-31 12:05:00');SELECT    ReviewID,    Username,    ProductName,    ReviewContent,    dbo.AnalyzeSentiment(ReviewContent) AS SentimentScore,    CASE        WHEN dbo.AnalyzeSentiment(ReviewContent) > 0.3 THEN '好評'        WHEN dbo.AnalyzeSentiment(ReviewContent) < -0.3 THEN '差評'        ELSE '中立'    END AS SentimentCategoryFROM UserReviewsORDER BY SentimentScore DESC
結果如圖5所示，基本能夠符合預期，透過提取評論中的關鍵字，確認當前的平均是好評或差評。
圖5. CLR函式測試結果
四、其他安全問題
如果在 RDS SQL Server 中建立或修改 CLR 程式集 “CLRFuncDemo” 時遇到了以下錯誤：
訊息 10343，級別 14，狀態 1，第 12 行針對帶有 SAFE 或 EXTERNAL_ACCESS 選項的程式集“CLRFuncDemo”的 CREATE 或 ALTER ASSEMBLY 失敗，因為 sp_configure 的“CLR 嚴格安全性”選項設定為 1。Microsoft 建議使用其相應登入名具有 UNSAFE ASSEMBLY 許可權的證書或非對稱金鑰為該程式集簽名。或者，也可以使用 sp_add_trusted_assembly 信任程式集。
這個報錯是由於自動SQL Server 2017開始，微軟對CLR整合的安全性做了更嚴格的限制，透過引入clr strict security選項，並將預設值設定為1，確保只有經過簽名的程式集才能以安全級別（SAFE 或 EXTERNAL_ACCESS）進行載入。
如果希望解決該問題，嘗試使用下面3種方法之一：
方法一：簽名程式集並授予相應許可權（微軟推薦做法）
簽名的作用本質上是讓SQL Server確認釋出者的身份，並與對應關聯最小化許可權，從而保證了CLR的安全。
整體過程相對較為複雜，具體操作步驟參考：
https://sqlquantumleap.com/2017/09/29/sqlclr-vs-sql-server-2017-part-6-trusted-assemblies-cant-do-module-signing/
方法二：使用 sp_add_trusted_assembly 信任程式集
sp_add_trusted_assembly 可以允許單獨對程式集進行類似白名單豁免的操作，同時不需要改程式集進行簽名。
但該方式同樣較為複雜，且執行該儲存過程需要SA許可權，相比方法一，更不推薦。
方法三：停用“clr strict security”選項
SQL Server作為PaaS服務，沒有例項級的訪問許可權，因此不能透過預設的方式直接關閉clr strict security選項，一個可選的方案是透過RDS提供的超級管理員賬號關閉該選項。
警告：停用 clr strict security 會使所有 CLR 程式集以更高的許可權執行，可能帶來安全隱患。
我們可以透過控制檯建立“超級許可權賬號”，該賬號的許可權視為sql server內建的sysadmin組許可權，值得注意的是，就像經典臺詞所說：“能力越大，責任越大”，擁有SA許可權後，RDS不再保障SLA，同時該操作是單行票，啟用後無法關閉。
使用SA賬號關閉clr strict security的SQL如下所示：
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'clr strict security', 0;RECONFIGURE;
停用該選項後，可以成功建立。
五、總結
CLR整合為SQL Server提供了強大的擴充套件能力，使開發人員能夠利用.NET Framework的豐富功能來處理複雜的資料庫任務。本文透過詳細的步驟和例項演示瞭如何在阿里雲RDS SQL Server中部署和使用CLR整合功能。
CLR的整合極大地拓展了SQL Server的應用場景。透過CLR，我們可以在SQL Server中實現T-SQL難以實現的業務場景，比如：
  1. 複雜字串處理：例如利用.NET的正則表示式庫，實現醫療病歷文字解析、資料清洗等高階文字處理
  2. 高效能計算：例如處理金融領域的期權定價等複雜數學運算，發揮編譯執行的效能優勢
  3. 檔案和網路操作：例如實現物流行業的自動化單據處理，支援跨系統資料交換
  4. 自定義加密解密：例如為銀行業提供端到端加密方案，保護敏感資料安全
  5. 影像處理：例如支援零售行業的商品圖片分析、質量檢測等多媒體處理需求
  6. 機器學習整合：例如實現電商平臺的即時商品推薦等智慧分析功能
  7. 複雜業務邏輯：例如處理信用卡額度評估等多維度決策場景
使用CLR而不是在應用程式中實現上述功能， 從效能角度看，資料庫端直接處理可以減少資料傳輸開銷，提供更高的處理效率。從安全性來看，敏感資料的處理限制在資料庫內部，能夠有效降低風險。從維護角度考慮，業務邏輯集中儲存便於統一管理和版本控制，同時支援多個應用系統共享相同的業務規則。
在實際專案中，T-SQL和應用程式層是實現業務功能的主要選擇。T-SQL適合資料的查詢、處理和基礎業務邏輯，應用程式層則擅長處理複雜互動和業務流程。CLR並非要替代這兩者，而是在特定場景下的一個補充選項。當我們遇到需要複雜字串處理、高效能計算或者外部系統整合等場景，而T-SQL實現困難、放在應用程式層有效能、部署或安全層面的限制時，CLR可以作為一個補充方案。

相關文章