TSQL：迁移至SQL Server后列内特殊字符乱码修复求助

从MySQL迁移到SQL Server 2017后，存储邮件HTML内容的表出现字符乱码问题：原本的'等特殊字符被存储为‘，当前表排序规则为SQL_Latin1_General_CP1_CI_AS，列数据类型为nvarchar(-1)。尝试修改列的排序规则/字符集为UTF-8无效，已发现的乱码字符包括‘, ’, —, “ ,â€, Â。

这类乱码是双重编码导致的：MySQL中以UTF-8存储的字符，在迁移时被错误地以Latin1（ISO-8859-1，对应SQL Server的SQL_Latin1_General_CP1_CI_AS排序规则）解码，然后存储到nvarchar列中。例如，UTF-8编码的左单引号‘（字节序列0xE2 0x80 0x98）被当成Latin1解码为三个字符：â（0xE2）、€（0x80）、˜（0x98），最终显示为‘。

1. 批量修复现有乱码数据 #

利用SQL Server的编码转换函数，将错误存储的字符还原为原始UTF-8对应的Unicode字符：

-- 先查询验证转换结果，确认无误后再执行更新
SELECT 
    YourColumn AS 原始内容,
    CONVERT(NVARCHAR(MAX), 
        CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), YourColumn)), 
        65001  -- 65001是UTF-8的代码页
    ) AS 修复后内容
FROM YourTable
WHERE YourColumn LIKE '%â€%' OR YourColumn LIKE '%Â%';

-- 执行更新（务必先备份数据）
UPDATE YourTable
SET YourColumn = CONVERT(NVARCHAR(MAX), 
        CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), YourColumn)), 
        65001
    )
WHERE YourColumn LIKE '%â€%' OR YourColumn LIKE '%Â%';

补充：针对已知乱码的定向替换 #

如果编码转换后仍有个别漏网字符，可针对已发现的乱码进行定向替换：

UPDATE YourTable
SET YourColumn = REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(YourColumn, '‘', '‘'), 
                                '’', '’'), 
                            '—', '—'), 
                        '“', '“'), 
                    'Â', ''
                )
WHERE YourColumn LIKE '%â€%' OR YourColumn LIKE '%Â%';

2. 预防未来的乱码问题 #

• 确保写入数据的编码一致性：应用程序连接SQL Server时，指定使用UTF-8编码（连接字符串中添加Character Set=UTF-8），或直接以Unicode（nvarchar）格式写入数据，避免编码转换错误。
• 保持列类型为nvarchar：nvarchar是SQL Server的Unicode存储类型，能原生支持所有Unicode字符，避免因字符集不兼容导致的乱码。

内容的提问来源于stack exchange，提问作者Prawal