利用CLR集成扩展RDS SQL Server

1. 启用CLR选项

如果要部署CLR集成，首先需要进入RDS SQL Server控制台的参数设置页面，设置实例级参数clr enabled值为1（该值默认为0），即可启用CLR选项。具体操作，请参见通过控制台设置实例参数。如图1所示：

图1.设置clr enabled参数值为1

2. Demo程序：情绪偏好分析函数

下文将通过一个Demo程序（简单的情绪偏好分析函数）展示如何将CLR部署到RDS SQL Server中。该Demo程序包含了一个简单的词库和分词逻辑，当给定评论内容时，程序会将内容与词库中的词进行简单对比，从而得出情绪分数。Demo程序C#代码如下：

public class SentimentAnalysis
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static 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++;
            }
        }

        return new SqlDouble(wordCount > 0 ? sentimentScore / wordCount : 0);
    }

    private static Dictionary<string, double> InitializeSentimentDictionary()
    {
        return new 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}
        };
    }
}

代码1.CLR情绪偏好分析函数Demo程序

1. 导出CLR程序集为二进制字符串

RDS SQL Server数据库作为PaaS层程序，用户无法直接访问其所在的操作系统，因此不能直接编译代码2创建CLR程序集（示例名为CLRFuncDemo）。您可以先将C#的CLR代码编译为DLL，然后通过拥有服务器操作系统访问权限的RDS SQL Server创建程序集。如代码2所示：

-- 创建CLR程序集
CREATE ASSEMBLY [CLRFuncDemo]
FROM 'E:\Backup\CLRFuncDemo.dll'
WITH PERMISSION_SET = SAFE

代码2.通过访问OS文件创建程序集

接着，通过SSMS将该程序集转化为二进制字符串。如图2所示：

图2.将CLR程序集转化为二进制字符串

2. 导入CLR程序集至RDS SQL Server

拥有当前指定数据库DBO权限的用户，可以通过SSMS连接RDS SQL Server实例，将导出的CLR程序集通过二进制的方式进行创建。如图3所示：

图3.导入程序集，并创建函数

如图4所示，可以看到程序集与CLR函数已经成功导入与创建。

图4.程序集与函数位置

3. 测试CLR函数

下文将通过一个简单的表测试数据Demo，测试CLR函数的可用性。测试数据如代码3所示：

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 SentimentCategory
FROM UserReviews
ORDER BY SentimentScore DESC

代码3.CLR函数测试Demo

结果如图5所示，基本能够符合预期，通过提取评论中的关键字，确认当前的平均值是好评或差评。

图5. CLR函数测试结果

方法一：签名程序集并授予相应权限（微软推荐做法）

签名的作用本质上是让SQL Server确认发布者的身份，并与对应关联最小化权限，从而保证了CLR的安全。该方法整体过程相对较为复杂，具体操作步骤，请参见SQL Quantum Leap。

方法二：使用sp_add_trusted_assembly信任程序集

sp_add_trusted_assembly可以允许单独对程序集进行类似白名单豁免的操作，同时不需要改程序集进行签名。但该方法同样较为复杂，且执行该存储过程需要SA权限，相比方法一，更不推荐。

方法三：禁用clr strict security选项

SQL Server作为PaaS服务，没有实例级的访问权限，因此不能通过默认的方式直接关闭clr strict security选项，一个可选的方案是通过RDS SQL Server提供的SA账号（超级权限账号）关闭该选项。

1. 通过RDS控制台创建超级权限账号。具体操作，请参见创建超级权限账号。

   重要

   由于SA账号的权限视为SQL Server内置的sysadmin组权限，值得注意的是，正如经典台词所说：“能力越大，责任越大”。因此RDS SQL Server实例拥有SA权限后，该实例将不再享受SLA保障；同时这是一项不可逆的单行操作，开启后无法关闭。

   

2. 使用SA账号关闭clr strict security，SQL如下：

   EXEC sp_configure 'show advanced options', 1;
   RECONFIGURE;
   
   EXEC sp_configure 'clr strict security', 0;
   RECONFIGURE;

禁用该选项后，即可成功创建CLR程序集。