SQLServer字符集验证的实现

文档信息

一、字符集概述

1.1 SQL Server 字符集相关概念

SQL Server 中的字符集和排序规则（Collation）决定了数据如何存储、比较和排序。理解字符集配置对数据库的正常运行至关重要。

1.2 排序规则命名规范

排序规则名称格式示例： Chinese_PRC_CI_AS │ │ │ │ │ │ │ └─ KS: 宽度敏感 (Kana Sensitive) │ │ └─ AI/AS: 重音不敏感/敏感 (Accent Insensitive/Sensitive) │ └─ CI/CS: 大小写不敏感/敏感 (Case Insensitive/Sensitive) └─ 语言/地区: Chinese_PRC, Latin1_General, SQL_Latin1 等 后缀说明_CICase Insensitive - 大小写不敏感_CSCase Sensitive - 大小写敏感_AIAccent Insensitive - 重音不敏感_ASAccent Sensitive - 重音敏感_KIKana Insensitive - 假名不敏感_KSKana Sensitive - 假名敏感_WSWidth Sensitive - 宽度敏感_BINBinary - 二进制排序_BIN2Binary2 - 二进制排序（码点比较）

二、查看当前字符集配置

2.1 查看实例级排序规则

-- 方法1：查看服务器实例排序规则 SELECT SERVERPROPERTY('Collation') AS InstanceCollation; GO -- 方法2：通过系统数据库查看 SELECT name, collation_name FROM sys.databases WHERE database_id <= 4; -- 系统数据库 GO -- 方法3：查看数据库默认排序规则 SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS CurrentDBCollation; GO

预期输出示例：

2.2 查看所有数据库的排序规则

-- 查看所有数据库的排序规则 SELECT database_id AS '数据库ID', name AS '数据库名称', collation_name AS '排序规则', CASE WHEN collation_name = SERVERPROPERTY('Collation') THEN '与实例一致' ELSE '与实例不一致' END AS '状态' FROM sys.databases ORDER BY database_id; GO

2.3 查看列级别的排序规则

-- 查看特定表的列排序规则 SELECT t.name AS '表名', c.name AS '列名', ty.name AS '数据类型', c.max_length AS '最大长度', c.collation_name AS '排序规则', c.is_nullable AS '可空' FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE t.type_desc = 'USER_TABLE' AND c.collation_name IS NOT NULL ORDER BY t.name, c.column_id; GO

2.4 查看支持的排序规则

-- 查看所有可用的排序规则 SELECT name AS '排序规则名称', description AS '描述', COLLATIONPROPERTY(name, 'CodePage') AS '代码页' FROM sys.fn_helpcollations() WHERE name LIKE 'Chinese%' OR name LIKE 'SQL_Latin%' ORDER BY name; GO

三、字符集不一致的影响

3.1 主要影响概览

3.2 详细影响说明

影响 1：跨库 JOIN 时报错

当两个数据库使用不同的排序规则时，直接进行 JOIN 操作会报错。

-- 演示错误：跨库 JOIN 不同排序规则 -- 假设 DB1 使用 Chinese_PRC_CI_AS -- 假设 DB2 使用 Chinese_PRC_CS_AS USE DB1; GO SELECT a.id, b.name FROM table_a a INNER JOIN DB2.dbo.table_b b ON a.col1 = b.col1; -- 这里会报错 GO -- 错误信息： -- Msg 468, Level 16, State 9, Line 1 -- Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_PRC_CS_AS" in the equal to operation.

解决方案：

-- 方案1：使用 COLLATE 子句指定统一排序规则 SELECT a.id, b.name FROM DB1.dbo.table_a a INNER JOIN DB2.dbo.table_b b ON a.col1 COLLATE Chinese_PRC_CI_AS = b.col1 COLLATE Chinese_PRC_CI_AS; GO -- 方案2：使用数据库默认排序规则 SELECT a.id, b.name FROM DB1.dbo.table_a a INNER JOIN DB2.dbo.table_b b ON a.col1 = b.col1 COLLATE DATABASE_DEFAULT; GO

影响 2：大小写敏感问题

-- 实例级别排序规则：Chinese_PRC_CI_AS (大小写不敏感) -- 数据库级别排序规则：Chinese_PRC_CS_AS (大小写敏感) -- 创建测试表 CREATE TABLE TestCase ( id INT, name NVARCHAR(50) ); GO -- 插入数据 INSERT INTO TestCase VALUES (1, 'abc'); INSERT INTO TestCase VALUES (2, 'ABC'); INSERT INTO TestCase VALUES (3, 'aBc'); GO -- 在 Chinese_PRC_CI_AS (不敏感) 环境下 SELECT * FROM TestCase WHERE name = 'abc'; -- 返回 3 行：abc, ABC, aBc -- 在 Chinese_PRC_CS_AS (敏感) 环境下 SELECT * FROM TestCase WHERE name = 'abc'; -- 返回 1 行：abc

影响 3：字符串比较问题

-- 不同排序规则下的字符串比较 -- 代码页不兼容可能导致的问题 DECLARE @var1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS; DECLARE @var2 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS; SET @var1 = '测试'; SET @var2 = '测试'; -- 直接比较会报错 IF @var1 = @var2 PRINT 'Equal'; ELSE PRINT 'Not Equal'; -- 错误信息： -- Msg 468, Level 16, State 9, Line 3 -- Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. -- 解决方案：统一排序规则 IF @var1 COLLATE Chinese_PRC_CI_AS = @var2 COLLATE Chinese_PRC_CI_AS PRINT 'Equal'; ELSE PRINT 'Not Equal';

影响 4：索引使用问题

-- 创建测试表 CREATE TABLE TestIndex ( id INT PRIMARY KEY, col1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS, col2 VARCHAR(50) COLLATE Chinese_PRC_CS_AS ); GO -- 创建索引 CREATE INDEX idx_col1 ON TestIndex(col1); CREATE INDEX idx_col2 ON TestIndex(col2); GO -- 查询时可能无法使用索引 SELECT * FROM TestIndex WHERE col1 = 'test'; -- 可以使用索引 -- 如果与不同排序规则的列比较 SELECT * FROM TestIndex WHERE col1 = col2 COLLATE Chinese_PRC_CS_AS; -- 可能无法使用索引，导致扫描

四、字符集不一致的验证测试

4.1 测试场景 1：创建不同排序规则的数据库

-- 查看当前实例排序规则 DECLARE @InstanceCollation NVARCHAR(128); SET @InstanceCollation = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128)); PRINT '实例排序规则: ' + @InstanceCollation; GO -- 创建与实例相同排序规则的数据库 CREATE DATABASE TestDB_SameCollation COLLATE Chinese_PRC_CI_AS; GO -- 创建与实例不同排序规则的数据库 CREATE DATABASE TestDB_DiffCollation COLLATE Chinese_PRC_CS_AS; GO -- 验证排序规则 SELECT name AS '数据库名称', collation_name AS '排序规则', CASE WHEN collation_name = SERVERPROPERTY('Collation') THEN '与实例一致' ELSE '与实例不一致' END AS '状态' FROM sys.databases WHERE name IN ('TestDB_SameCollation', 'TestDB_DiffCollation'); GO

4.2 测试场景 2：跨库 JOIN 测试

-- 在相同排序规则的数据库中创建表 USE TestDB_SameCollation; GO CREATE TABLE TableA ( id INT IDENTITY PRIMARY KEY, code VARCHAR(20), name NVARCHAR(50) ); GO INSERT INTO TableA (code, name) VALUES ('A001', '名称1'), ('A002', '名称2'); GO -- 在不同排序规则的数据库中创建表 USE TestDB_DiffCollation; GO CREATE TABLE TableB ( id INT IDENTITY PRIMARY KEY, code VARCHAR(20), value INT ); GO INSERT INTO TableB (code, value) VALUES ('A001', 100), ('A003', 300); GO -- 测试跨库 JOIN（会报错） USE TestDB_SameCollation; GO SELECT a.id, a.code, b.value FROM TableA a INNER JOIN TestDB_DiffCollation.dbo.TableB b ON a.code = b.code; GO -- 预期错误： -- Msg 468, Level 16, State 9 -- Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_PRC_CS_AS" in the equal to operation. -- 正确的跨库 JOIN 方式 SELECT a.id, a.code, b.value FROM TableA a INNER JOIN TestDB_DiffCollation.dbo.TableB b ON a.code COLLATE Chinese_PRC_CI_AS = b.code COLLATE Chinese_PRC_CI_AS; GO

4.3 测试场景 3：临时表排序规则

-- 临时表使用 tempdb 的排序规则，可能与用户数据库不同 -- 查看当前数据库排序规则 SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS CurrentDBCollation; GO -- 查看 tempdb 排序规则 SELECT DATABASEPROPERTYEX('tempdb', 'Collation') AS TempDBCollation; GO -- 创建临时表并测试 CREATE TABLE #TempTable ( id INT, code VARCHAR(20) ); GO -- 插入数据 INSERT INTO #TempTable VALUES (1, 'A001'), (2, 'A002'); GO -- 创建用户表 CREATE TABLE UserTable ( id INT, code VARCHAR(20) ); GO INSERT INTO UserTable VALUES (1, 'A001'), (2, 'A002'); GO -- 如果排序规则不同，JOIN 可能失败 SELECT t.id, u.code FROM #TempTable t INNER JOIN UserTable u ON t.code = u.code; GO -- 解决方案：明确指定排序规则 SELECT t.id, u.code FROM #TempTable t INNER JOIN UserTable u ON t.code COLLATE DATABASE_DEFAULT = u.code COLLATE DATABASE_DEFAULT; GO

4.4 测试场景 4：字符串函数影响

-- 测试排序规则对字符串函数的影响 DECLARE @str1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS; DECLARE @str2 VARCHAR(50) COLLATE Chinese_PRC_CS_AS; SET @str1 = 'TestString'; SET @str2 = 'teststring'; -- 大小写敏感测试 SELECT CASE WHEN @str1 COLLATE Chinese_PRC_CI_AS = @str2 COLLATE Chinese_PRC_CI_AS THEN 'CI_AS: 相等' ELSE 'CI_AS: 不相等' END AS CI_Result, CASE WHEN @str1 COLLATE Chinese_PRC_CS_AS = @str2 COLLATE Chinese_PRC_CS_AS THEN 'CS_AS: 相等' ELSE 'CS_AS: 不相等' END AS CS_Result; GO -- 排序测试 SELECT 'a' AS Char1, 'A' AS Char2, CASE WHEN 'a' COLLATE Chinese_PRC_CI_AS < 'A' COLLATE Chinese_PRC_CI_AS THEN 'a < A (CI)' ELSE 'a >= A (CI)' END AS CI_Sort, CASE WHEN 'a' COLLATE Chinese_PRC_CS_AS < 'A' COLLATE Chinese_PRC_CS_AS THEN 'a < A (CS)' ELSE 'a >= A (CS)' END AS CS_Sort; GO

五、常见排序规则对比

5.1 中文排序规则对比

5.2 排序规则选择建议

六、字符集不一致的解决方案

6.1 解决方案 1：统一排序规则（推荐）

-- 方案1：重建数据库为统一排序规则 -- 步骤1：备份数据库 BACKUP DATABASE TestDB_DiffCollation TO DISK = 'C:\Backup\TestDB_DiffCollation.bak'; GO -- 步骤2：导出所有数据和对象 -- 使用 SQL Server Management Studio 的生成脚本功能 -- 或使用 bcp 命令导出数据 -- 步骤3：删除数据库 DROP DATABASE TestDB_DiffCollation; GO -- 步骤4：使用统一排序规则创建数据库 CREATE DATABASE TestDB_DiffCollation COLLATE Chinese_PRC_CI_AS; GO -- 步骤5：导入数据和对象

6.2 解决方案 2：使用 COLLATE 子句

-- 在查询中显式指定排序规则 -- 跨库查询 SELECT a.col1, b.col2 FROM DB1.dbo.TableA a INNER JOIN DB2.dbo.TableB b ON a.join_col COLLATE DATABASE_DEFAULT = b.join_col COLLATE DATABASE_DEFAULT; -- 字符串比较 DECLARE @var1 VARCHAR(50) = 'test'; DECLARE @var2 VARCHAR(50) = 'TEST'; IF @var1 COLLATE Chinese_PRC_CI_AS = @var2 COLLATE Chinese_PRC_CI_AS PRINT 'Equal'; -- ORDER BY 指定排序规则 SELECT name FROM users ORDER BY name COLLATE Chinese_PRC_CI_AS;

6.3 解决方案 3：修改列的排序规则

-- 修改现有列的排序规则 -- 注意：修改列排序规则会重建表，大表操作需要谨慎 -- 示例：修改列排序规则 ALTER TABLE TestTable ALTER COLUMN col1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS; GO -- 查看列排序规则修改结果 SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('TestTable'); GO

6.4 解决方案 4：使用计算列

-- 创建计算列用于跨库连接 CREATE TABLE TableA ( id INT PRIMARY KEY, code VARCHAR(20), -- 计算列：统一排序规则 code_normalized AS code COLLATE Chinese_PRC_CI_AS PERSISTED ); GO -- 创建表 CREATE TABLE TableB ( id INT PRIMARY KEY, code VARCHAR(20) COLLATE Chinese_PRC_CS_AS ); GO -- 使用计算列进行连接 SELECT a.id, b.id FROM TableA a INNER JOIN TableB b ON a.code_normalized = b.code COLLATE Chinese_PRC_CI_AS; GO

七、最佳实践建议

7.1 设计阶段建议

7.2 开发阶段建议

-- 建议在存储过程和视图中使用 DATABASE_DEFAULT CREATE VIEW vw_CrossDatabaseJoin AS SELECT a.id AS id_a, b.id AS id_b, a.col1, b.col2 FROM DB1.dbo.TableA a INNER JOIN DB2.dbo.TableB b ON a.join_key COLLATE DATABASE_DEFAULT = b.join_key COLLATE DATABASE_DEFAULT; GO

7.3 运维阶段建议

7.4 监控 SQL

-- 监控因排序规则导致的查询警告 SELECT st.text AS SQLText, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS StatementText, qp.query_plan, qs.execution_count, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE st.text LIKE '%COLLATE%' OR qp.query_plan.exist('//*[local-name()="PlanAffectingConvert"]') = 1 ORDER BY avg_elapsed_time DESC; GO

八、验证检查清单

九、诊断 SQL 脚本

9.1 全面诊断脚本

-- SQL Server 字符集诊断脚本 -- 1. 实例级排序规则 PRINT '=== 实例级排序规则 ==='; SELECT SERVERPROPERTY('Collation') AS InstanceCollation, SERVERPROPERTY('Edition') AS ServerEdition, SERVERPROPERTY('ProductVersion') AS ProductVersion; GO -- 2. 所有数据库排序规则 PRINT '=== 所有数据库排序规则 ==='; SELECT database_id, name AS DatabaseName, collation_name AS Collation, compatibility_level AS CompatibilityLevel, state_desc AS State, CASE WHEN collation_name = SERVERPROPERTY('Collation') THEN '✓ 一致' ELSE '✗ 不一致' END AS MatchInstance FROM sys.databases ORDER BY database_id; GO -- 3. 不一致数据库详情 PRINT '=== 与实例排序规则不一致的数据库 ==='; SELECT name AS DatabaseName, collation_name AS DatabaseCollation, SERVERPROPERTY('Collation') AS InstanceCollation FROM sys.databases WHERE collation_name <> SERVERPROPERTY('Collation') AND database_id > 4; -- 排除系统数据库 GO -- 4. 列级别排序规则统计 PRINT '=== 列级别排序规则统计 ==='; SELECT OBJECT_NAME(c.object_id) AS TableName, c.name AS ColumnName, ty.name AS DataType, c.collation_name AS ColumnCollation, DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation, CASE WHEN c.collation_name = DATABASEPROPERTYEX(DB_NAME(), 'Collation') THEN '✓ 一致' ELSE '✗ 不一致' END AS MatchDatabase FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE t.is_ms_shipped = 0 -- 排除系统表 AND c.collation_name IS NOT NULL ORDER BY OBJECT_NAME(c.object_id), c.column_id; GO -- 5. 不同代码页的列 PRINT '=== 使用不同代码页的列 ==='; SELECT DB_NAME() AS DatabaseName, OBJECT_NAME(c.object_id) AS TableName, c.name AS ColumnName, c.collation_name AS Collation, COLLATIONPROPERTY(c.collation_name, 'CodePage') AS CodePage FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE t.is_ms_shipped = 0 AND c.collation_name IS NOT NULL GROUP BY c.collation_name, COLLATIONPROPERTY(c.collation_name, 'CodePage') ORDER BY CodePage; GO

9.2 生成修复建议

-- 生成修复建议脚本 DECLARE @InstanceCollation NVARCHAR(128); SET @InstanceCollation = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128)); -- 生成修改数据库排序规则的脚本 SELECT 'ALTER DATABASE [' + name + '] COLLATE ' + @InstanceCollation + ';' AS FixScript, '注意：修改数据库排序规则不影响现有列，需要单独修改列' AS Warning FROM sys.databases WHERE collation_name <> @InstanceCollation AND database_id > 4; -- 生成修改列排序规则的脚本 SELECT 'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ' + 'ALTER COLUMN [' + c.name + '] ' + UPPER(ty.name) + CASE WHEN ty.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN '(' + CAST(c.max_length AS VARCHAR) + ')' ELSE '' END + ' COLLATE ' + @InstanceCollation + ';' AS FixScript, '注意：修改列排序规则会重建表，大表操作需谨慎' AS Warning FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE t.is_ms_shipped = 0 AND c.collation_name IS NOT NULL AND c.collation_name <> @InstanceCollation ORDER BY SCHEMA_NAME(t.schema_id), t.name, c.column_id; GO

十、常见问题

Q1：如何判断字符集不一致是否影响了性能？

诊断方法：

-- 查看执行计划中的排序规则转换警告 SELECT qs.execution_count, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, st.text AS SQLText, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qp.query_plan.exist('//*[local-name()="Sort"][@Implicit="1"]') = 1 OR st.text LIKE '%COLLATE%' ORDER BY avg_elapsed_time DESC;

Q2：修改数据库排序规则会影响数据吗？

回答：

Q3：如何选择合适的排序规则？

建议：

Q4：排序规则与 Unicode 的关系？

说明：

十一、补充测试验证结果

11.1 测试环境

11.2 创建的敏感测试数据库

11.3 实际验证结果

测试 1：大小写不敏感 (Chinese_PRC_CI_AS)

-- 测试环境：TestDB_SameCollation (Chinese_PRC_CI_AS) CREATE TABLE TestCaseSensitivity ( id INT, name VARCHAR(50) ); INSERT INTO TestCaseSensitivity VALUES (1, 'abc'), (2, 'ABC'), (3, 'aBc'); SELECT COUNT(*) FROM TestCaseSensitivity WHERE name = 'abc';

实际结果： 返回 3 行
结论： ✅ 验证通过 - Chinese_PRC_CI_AS 大小写不敏感

测试 2：大小写敏感 (Chinese_PRC_CS_AS)

-- 测试环境：TestDB_DiffCollation (Chinese_PRC_CS_AS) CREATE TABLE TestCaseSensitivity_CS ( id INT, name VARCHAR(50) ); INSERT INTO TestCaseSensitivity_CS VALUES (1, 'abc'), (2, 'ABC'), (3, 'aBc'); SELECT COUNT(*) FROM TestCaseSensitivity_CS WHERE name = 'abc';

实际结果： 返回 1 行
结论： ✅ 验证通过 - Chinese_PRC_CS_AS 大小写敏感

测试 3：二进制排序 (Chinese_PRC_BIN)

-- 测试环境：TestDB_Chinese_BIN (Chinese_PRC_BIN) CREATE TABLE TestCaseBIN (id INT, name VARCHAR(50)); INSERT INTO TestCaseBIN VALUES (1, 'abc'), (2, 'ABC'), (3, '测试'); SELECT COUNT(*) FROM TestCaseBIN WHERE name = 'abc';

实际结果： 返回 1 行
结论： ✅ 验证通过 - Chinese_PRC_BIN 二进制排序，严格区分大小写

测试 4：拉丁字符集 (Latin1_General_CI_AS)

-- 测试环境：TestDB_Latin1_CI_AS CREATE TABLE TestLatin1 (id INT, content VARCHAR(50)); INSERT INTO TestLatin1 VALUES (1, 'test'), (2, '中文测试'); SELECT * FROM TestLatin1 WHERE content = 'test';

实际结果： 成功存储和查询中文字符
结论： ✅ 验证通过 - Latin1 字符集也能处理中文（推荐使用 NVARCHAR）

测试 5：跨库 JOIN 排序规则冲突

-- 测试：Chinese_PRC_BIN 与 Latin1_General_CI_AS 跨库 JOIN SELECT a.id FROM TestDB_Latin1_CI_AS.dbo.TestLatin1 a INNER JOIN TestDB_Chinese_BIN.dbo.TableBIN b ON a.content = b.code;

实际结果：

Msg 468, Level 16, State 9 Cannot resolve the collation conflict between "Chinese_PRC_BIN" and "Latin1_General_CI_AS"

结论： ✅ 验证通过 - 不同排序规则跨库 JOIN 报错

测试 6：tempdb 排序规则

实际查询结果：

结论： ✅ 验证通过 - 临时表与用户数据库排序规则不一致

11.4 补充测试结论

11.5 验证检查清单（更新）

十二、参考资源

到此这篇关于SQL Server 字符集验证的实现的文章就介绍到这了,