突破T-SQL限制：利用CLR集成扩展RDS SQL Server的功能边界

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}
        };
    }
}

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

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

消息 10343，级别 14，状态 1，第 12 行
针对带有 SAFE 或 EXTERNAL_ACCESS 选项的程序集“CLRFuncDemo”的 CREATE 或 ALTER ASSEMBLY 失败，因为 sp_configure 的“CLR 严格安全性”选项设置为 1。Microsoft 建议使用其相应登录名具有 UNSAFE ASSEMBLY 权限的证书或非对称密钥为该程序集签名。或者，也可以使用 sp_add_trusted_assembly 信任程序集。

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;