一文搞定SQLServer 2022中文编码问题

编码问题不是大问题，但是解决不完的问题，尤其涉及到所个数据源跨部门数据操作的项目，那么编码一定会有问题，最近接触到SQLServer项目就是如此，花了一天，解决问题，花了一天沟通上下游，确定之后的流程，最后的感触，沟通能力>编码能力，作以下总结：

一、常见中文乱码问题类型

1.1 数据存储乱码

示例现象：

• 插入的中文数据变成"???"或"¿¿¿¿"

• 显示为方框"□□□"

• 显示为乱码符号"測海"

1.2 查询结果乱码

示例现象：

• SSMS中显示正常，应用程序显示乱码

• 导出数据为CSV后乱码

• 不同客户端工具显示不一致

二、根本原因分析

2.1 字符集配置问题

问题点	影响范围	典型表现
数据库默认排序规则不兼容中文	整个数据库	所有中文字段存储异常
字段级别字符集设置错误	单个字段	特定字段乱码
连接字符集不匹配	当前会话	查询结果乱码

2.2 数据传输问题

场景	可能原因
客户端应用程序显示乱码	应用程序字符编码设置错误
Web页面显示乱码	缺少HTML meta charset声明
数据导出乱码	导出文件编码格式不匹配

三、解决方案与具体操作

3.1 数据库级别修复方案

-- 1. 检查当前数据库排序规则
SELECT name, collation_name 
FROM sys.databases
WHERE name = DB_NAME();
​
-- 2. 修改数据库排序规则(需重建数据库)
CREATE DATABASE MyDB COLLATE Chinese_PRC_CI_AS;
​
-- 3. 修改现有数据库排序规则(影响现有数据)
ALTER DATABASE MyDB COLLATE Chinese_PRC_CI_AS;

3.2 表字段级别修复

-- 1. 检查表字段排序规则
SELECT 
    c.name AS column_name,
    t.name AS data_type,
    c.collation_name
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('YourTableName');
​
-- 2. 修改字段排序规则
ALTER TABLE YourTableName 
ALTER COLUMN YourColumnName NVARCHAR(100) COLLATE Chinese_PRC_CI_AS;

3.3 连接字符串配置

// ADO.NET 连接字符串示例
"Server=myServer;Database=myDB;User Id=myUser;Password=myPass;"
+ "Persist Security Info=True;"
+ "TrustServerCertificate=True;"

3.4 数据修复操作

-- 1. 临时修复已损坏数据(假设原编码是GBK)
UPDATE DamagedTable
SET ChineseColumn = CONVERT(NVARCHAR(100), 
    ChineseColumn = CAST(ChineseColumn AS VARBINARY(100))
FROM YourTable;
​
-- 2. 正确插入中文数据
INSERT INTO TestTable (ChineseColumn)
VALUES (N'中文内容'); -- 注意N前缀

四、典型问题场景与解决方案

4.1 场景一：插入中文变成问号

问题SQL：

INSERT INTO Products (ProductName) VALUES ('中文商品名');

解决方案：

1. 确保字段是NVARCHAR类型

2. 使用N前缀：

   INSERT INTO Products (ProductName) VALUES (N'中文商品名');

4.2 场景二：SSMS显示正常但应用程序乱码

问题表现：

• 应用程序显示为"測海"等乱码

解决方案：

1. 检查应用程序连接字符串：

   // 添加Charset参数
   "Server=...;Charset=utf8;"

2. 确保应用程序使用UTF-8编码

4.3 场景三：导出CSV文件乱码

问题表现：

• Excel打开CSV时中文显示为乱码

解决方案：

1. 使用BCP命令导出时指定代码页：

   bcp "SELECT * FROM YourTable" queryout "data.csv" -c -C 65001 -S server -U user -P password

   (65001是UTF-8代码页)

五、预防措施

5.1 最佳实践清单

1. [ ] 新建数据库时指定中文排序规则：

   CREATE DATABASE MyDB COLLATE Chinese_PRC_CI_AS;

2. [ ] 所有中文字段使用NVARCHAR/NCHAR类型

3. [ ] 插入中文数据始终使用N前缀：

   INSERT ... VALUES (N'中文')

4. [ ] 应用程序连接字符串明确指定字符集

5. [ ] 定期检查数据库排序规则一致性

5.2 环境检查脚本

-- 检查服务器级排序规则
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
​
-- 检查所有数据库排序规则
SELECT name, collation_name 
FROM sys.databases
WHERE collation_name NOT LIKE 'Chinese%';
​
-- 检查非Unicode文本字段
SELECT 
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type,
    c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('VARCHAR','CHAR','TEXT')
AND c.collation_name NOT LIKE 'Chinese%';

六、高级问题处理

6.1 修复已损坏数据

-- 假设数据以GBK编码错误存储为VARCHAR
-- 步骤1：将错误数据转为二进制
SELECT 
    ChineseColumn,
    CAST(ChineseColumn AS VARBINARY(100)) AS BinaryData
FROM DamagedTable;
​
-- 步骤2：通过二进制转换修复
UPDATE DamagedTable
SET ChineseColumn = 
    CAST(
        CAST(CAST(ChineseColumn AS VARBINARY(100)) AS VARCHAR(100))
        COLLATE Chinese_PRC_CI_AS
    )
WHERE <条件>;

6.2 跨数据库数据传输

当数据需要在不同排序规则的数据库间传输时：

-- 使用COLLATE子句明确指定排序规则
INSERT INTO TargetDB.dbo.TargetTable (ChineseColumn)
SELECT ChineseColumn COLLATE Chinese_PRC_CI_AS
FROM SourceDB.dbo.SourceTable;

通过以上系统化的解决方案，可以彻底解决SQL Server中的中文乱码问题，并预防未来可能出现的数据显示异常情况。