SQL Server大小写敏感查询：排序规则与COLLATE索引优化

上个月帮人看一个后台系统的问题测试同事发来一张截图登录框里输入zhangsan能进普通用户页面输入ZhangSan进的还是同一个人的主页输入ZHANGSAN照样进。运维那边查了半天前端校验、密码哈希、缓存 key全都没毛病。最后把 SQL Profiler 挂上去看到那句平平无奇的SELECT * FROM dbo.Users WHERE UserName name参数传进去ZHANGSAN返回的行是zhangsan。问题根本不在代码在于 SQL Server 默认就认为zhangsan和ZHANGSAN是同一回事。这就是SQL Server 查询区分大小写这件事的全部戏剧性所在它不是你写不写对语法的问题而是数据库在你建库那一刻就用一个叫排序规则Collation的设定替你决定了整个库对大小写的态度。等业务跑起来、数据攒到千万级你才发现有几条查询需要严格区分大小写然后开始踩一串坑——索引突然不走、跨库 JOIN 报 468 错误、临时表跟主表对不上。这篇东西写给两类人一类是刚碰上明明两个值不一样查出来却当成一样的新手需要一套能直接抄的写法另一类是被 COLLATE 搞崩过执行计划、想搞清楚背后原理和工程化方案的老手。下面从排序规则的本质讲起一路讲到列改造、计算列加索引、排序规则冲突排查把我实际踩过的坑和实测数据都摊开说。1. 排序规则决定了 SQL Server 对大小写的态度1.1 一个能复现问题的最小例子先把手弄脏。开一个查询窗口随便建个表CREATE TABLE dbo.T_User ( Id INT IDENTITY(1,1) PRIMARY KEY, UserName VARCHAR(50) NOT NULL ); INSERT INTO dbo.T_User(UserName) VALUES (zhangsan), (ZhangSan), (ZHANGSAN);然后跑三条查询你会看到非常反直觉的结果SELECT * FROM dbo.T_User WHERE UserName zhangsan; -- 返回 3 行 SELECT * FROM dbo.T_User WHERE UserName ZHANGSAN; -- 返回 3 行 SELECT * FROM dbo.T_User WHERE UserName LIKE zhang%; -- 返回 3 行三条全命中。很多人第一反应是SQL Server 有 bug其实这是设计如此。你建表的时候没指定排序规则它继承了数据库的默认排序规则而绝大多数人在装实例、建库时选的都是Chinese_PRC_CI_AS或SQL_Latin1_General_CP1_CI_AS这类名字。名字里那个CI就是 Case Insensitive大小写不敏感。想验证自己手上这套环境到底是什么态度两条语句够用了SELECT SERVERPROPERTY(Collation) AS ServerCollation; SELECT DATABASEPROPERTYEX(YourDbName, Collation) AS DbCollation; SELECT name, collation_name FROM sys.columns WHERE object_id OBJECT_ID(dbo.T_User);第三条尤其重要它告诉你每一列真实的排序规则。生产环境里经常出现一个库内不同列排序规则不一致的情况——历史遗留、跨库导数据、有人手动加过COLLATE子句都会导致这种局面。你不查一遍永远不知道手上这张表的UserName列到底是 CI 还是 CS。1.2 排序规则名字里每个段位的意思排序规则这个名字看着像天书其实是有结构的拆开就好懂了。以Latin1_General_100_CS_AS_SC_UTF8为例片段含义说明Latin1_General字符集/语言区域决定字符的排序顺序中文环境常见Chinese_PRC100版本号越新对 Unicode 支持越好100/140/160依次更新CS/CICase Sensitive / Insensitive本文主角决定是否区分大小写AS/AIAccent Sensitive / Insensitive决定是否区分重音符号比如é和eKSKana Sensitive日文假名是否区分平假名与片假名WSWidth Sensitive全角与半角是否区分SCSupplementary Characters支持增补字符emoji、生僻字等四字节字符UTF8UTF-8 存储SQL Server 2019 起支持让 VARCHAR 也能存 UTF-8BIN/BIN2二进制排序直接按码位逐字节比较大小写必然敏感几个实操层面的结论第一CS和BIN2都能实现大小写敏感但行为不完全一样。CS是语言学意义上的区分大小写它依然会做语言相关的排序处理比如把a和A排在一起但判定为不等BIN2则是纯粹的码位比较a的码位 0x61A的码位 0x41谁大谁小一目了然。做账号、Token、编号这类必须严格按字节比对的场景我更倾向BIN2。第二BIN和BIN2不是一个东西。BIN是早期实现在 Unicode 上历史包袱不少对某些字符的比较结果和预期不符BIN2是后来的修正版微软自己的文档也建议优先用BIN2。还在用BIN的老系统能迁就迁。第三中文环境不要无脑用Latin1_General系列去做中文列的比较。虽然Latin1_General_100_CS_AS在实际比较汉字时也能工作但排序顺序、LIKE匹配范围这些细节会和Chinese_PRC_CS_AS有差异。中文列老老实实用Chinese_PRC_CS_AS英文标识类字段用Latin1_General_100_CS_AS或Latin1_General_100_BIN2。1.3 三层排序规则谁说了算SQL Server 里排序规则有三个层级从高到低是列 表达式查询里显式写的 COLLATE 数据库。服务器级别的排序规则其实只影响系统库和新建库的默认值对已有库的具体列不起直接作用。这个优先级顺序解释了很多现象。比如数据库默认是Chinese_PRC_CI_AS但某张表的Code列被单独建成了Chinese_PRC_CS_AS那么WHERE Code abc在这张表上就是大小写敏感的而同一库里别的表还是敏感的。这种库内不一致是排查问题时最容易被忽略的一层。还有一个隐蔽的点临时表和表变量永远使用 tempdb 的排序规则跟你当前所在的业务库没关系。很多人写存储过程主体表在业务库自定义排序规则临时表在 tempdb默认排序规则JOIN 的时候直接报错 468。这个坑在第 6 节会展开讲。2. 单条查询强制大小写敏感COLLATE 的正确用法与错误写法2.1 COLLATE 写在等号右边是最小改动方案当你不方便动表结构——比如老系统、第三方库、生产环境不敢改 DDL——最快的办法是在查询里显式指定排序规则。基本写法就一行SELECT * FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS zhangsan;这样只会命中zhangsan一行ZhangSan和ZHANGSAN被排除掉。LIKE同理SELECT * FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS LIKE zhang%;注意一个细节COLLATE加在列名后面不是加在参数后面。有人写成WHERE UserName zhangsan COLLATE Chinese_PRC_CS_AS语法上不一定报错但语义就变了——它是对字面量应用排序规则比较时 SQL Server 会按排序规则优先级重新推导最终效果不一定是你想要的。永远把 COLLATE 贴在列那一侧。参数化查询里也一样SELECT * FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS UserName;应用层传ZhangSan进来就只有那一条能匹配。这条写法我在当年那个登录串号的问题上直接用了一行改动问题当场消失。2.2 为什么不能把 COLLATE 挂在索引列上用上一节的写法有个巨大的副作用索引基本失效。原因不复杂——索引是按列的原始排序规则建的一棵 B 树排序顺序、比较逻辑都固定死了。你在查询里给这一列套了个不同的排序规则SQL Server 就没法直接在索引上做等值查找Seek只能把整列取出来逐行套上新的排序规则再比也就是全表扫描Scan。如果你写的是-- 这种写法更危险把列整个包起来 WHERE UPPER(UserName) ZHANGSAN -- 或者 WHERE LOWER(UserName) zhangsan那不光是排序规则的问题还叠加了函数包裹列的问题。SQL Server 对UPPER(UserName)这种表达式没法直接利用UserName上的索引除非你专门建一个计算列索引。而且UPPER/LOWER还有一个坑它只能处理英文大小写这一种情况遇到ß、ı这类特殊字符的行为跟排序规则完全不是一回事。做个大小写敏感比较用UPPER/LOWER属于用一个更糙的工具解决一个本来有专门工具的问题。真实项目里我见过有人这么绕WHERE UserName name COLLATE Chinese_PRC_CS_AS OR UserName name -- 兜底这种写法既丢性能又不解决问题纯粹是把两种语义混在一起。看到类似的写法基本可以判定这段代码被反复救火过。2.3 IN、BETWEEN、JOIN 里的写法差异COLLATE不止能用和LIKE。IN的话加在列上就行列表里所有值都按这个规则比SELECT * FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS IN (zhangsan, lisi);BETWEEN也类似注意加在列上而不是边界值上SELECT * FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS BETWEEN a AND m;JOIN 场景要格外小心。如果两张表的关联列排序规则不一致你得在ON里明确指定一个统一规则否则可能报 468 错误。写法是SELECT a.Id, b.OrderNo FROM dbo.T_User a JOIN dbo.T_Order b ON a.UserName COLLATE Chinese_PRC_CS_AS b.UserName COLLATE Chinese_PRC_CS_AS;两边都加别只加一边。只加一边的时候 SQL Server 会按优先级自动补结果有时候能跑通、有时候报错非常依赖具体环境的默认值属于那种在开发机上好好的上生产就炸的写法。还有一个高频误用场景是EXISTSSELECT * FROM dbo.T_User u WHERE EXISTS ( SELECT 1 FROM dbo.T_LoginLog l WHERE l.UserName COLLATE Chinese_PRC_CS_AS u.UserName COLLATE Chinese_PRC_CS_AS );这种写法功能上正确但性能上两张表都得扫。如果这是热点查询必须往第 4、5 节的方案走。2.4 用二进制转换也是一种办法但要想清楚除了COLLATE ... CS_AS另一条路是把值转成二进制来比SELECT * FROM dbo.T_User WHERE CONVERT(VARBINARY(50), UserName) CONVERT(VARBINARY(50), zhangsan);这个办法的好处是完全绕开了排序规则的语言学逻辑纯字节比对a和A必然不同。缺点是一两边都得转索引照样不吃二VARBINARY比较时尾部空格处理和VARCHAR不一样abc和abc 在 VARCHAR 里可能相等转二进制后长度不同就不等了三NVARCHAR转二进制后是 UTF-16 编码人工调试时看到的十六进制串和直觉差别很大。我一般只在一种情况下用这个校验和比对比如判断某条记录的内容是否被静默修改过。做业务查询还是老老实实走COLLATE。3. 强制区分大小写后的执行计划索引到底还能不能用3.1 一次实测三百万行表的 Seek 与 Scan光讲理论不够说个我实际测过的例子。测试表约 300 万行UserName VARCHAR(50)上有非聚集索引列排序规则是库默认的Chinese_PRC_CI_AS。测试机器的配置不重要重点是同一条 SQL 换不同写法后SET STATISTICS IO的输出差异。写了个对比脚本跑了四组SET STATISTICS IO ON; -- A原始写法大小写不敏感 SELECT Id FROM dbo.T_User WHERE UserName zhangsan; -- B列上挂 CS 排序规则 SELECT Id FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_CS_AS zhangsan; -- C用 UPPER 包列 SELECT Id FROM dbo.T_User WHERE UPPER(UserName) ZHANGSAN; -- D列上挂 BIN2 SELECT Id FROM dbo.T_User WHERE UserName COLLATE Chinese_PRC_BIN2 zhangsan;结果大致是这样逻辑读数具体数字随环境浮动看趋势就行写法逻辑读执行计划里的访问方式A 原始约 4 次Index SeekB 加 CS约 2 万次Index ScanC 加 UPPER约 2 万次Index ScanD 加 BIN2约 2 万次Index Scan差距是三个数量级。3 百万行看着还行扫一遍也就零点几秒但如果这张表是 3000 万行的订单表、或者这是每秒几百次的登录接口那这一点就够把数据库压趴下。执行计划里你会看到一个典型信号Seek Predicates 消失了Predicate 出现在 Filter 里。这说明优化器没能在索引上做范围定位只能把索引整个扫一遍再逐行过滤。看到这个形态基本可以确定是表达式破坏了 SARGability可搜索性。3.2 隐式转换叠加排序规则坑上加坑有一种情况比单纯加COLLATE更糟排序规则问题撞上隐式类型转换。典型场景的前端传参是NVARCHAR.NET 的string默认映射成NVARCHAR而数据库列是VARCHAR排序规则又是Chinese_PRC_CI_AS你又在查询里加了个COLLATE Latin1_General_CS_AS-- p 是 NVARCHAR(50) SELECT * FROM dbo.T_User WHERE UserName COLLATE Latin1_General_CS_AS p;这里有两层问题。第一层VARCHAR列和NVARCHAR参数比较SQL Server 会按数据类型优先级把VARCHAR隐式转成NVARCHAR转换发生在列上索引直接废掉。第二层两边排序规则一个是Latin1_General_CS_AS一个是库默认的Chinese_PRC_CI_AS语义上已经拧巴了。判断隐式转换最简单的方法是看执行计划里的警告。SSMS 里执行计划有黄色感叹号鼠标悬停会写着 Type Converted ... might affect CardinalityEstimate in Selectivity computations。看到这个标记就要警惕。正确的收敛方式只有一条参数类型和列类型在应用层就对齐。别指望在 SQL 里靠CONVERT补救那只是把转换从隐式变显式索引照样不吃。3.3 按数据量分档的选型建议我不建议所有场景都上大方案。按数据量和查询频率分三档对号入座数据量级查询频率推荐方案理由万行以下任意查询里直接加COLLATE ... CS_AS全扫也快改表结构反而增加风险十万到百万低频后台报表、定时任务COLLATE 接受扫描单次慢一点可接受改动成本最低百万以上或高频高频登录、接口改列排序规则 或 计算列加索引必须走 Seek否则迟早拖垮实例这张表不是拍脑袋定的。判断标准其实是扫描耗时有没有超过你的接口 P99 预算。百万行以内的VARCHAR(50)扫描一般几十毫秒能回来过千万行就是秒级了接口层直接超时。4. 改列排序规则从 CI 迁移到 CS 的完整操作链4.1 动手之前必须确认的四件事改列排序规则是 DDL 操作一旦出问题回滚成本很高。我在动手前会先跑完这四项检查缺一项都不开始。第一确认目标列有没有被索引引用。有索引的话ALTER COLUMN会触发索引重建锁表时间成倍增加。查一下SELECT i.name AS IndexName, i.type_desc FROM sys.index_columns ic JOIN sys.indexes i ON i.object_id ic.object_id AND i.index_id ic.index_id WHERE ic.object_id OBJECT_ID(dbo.T_User) AND ic.column_id COLUMNPROPERTY(OBJECT_ID(dbo.T_User), UserName, ColumnId);第二确认有没有外键、默认值、CHECK 约束引用这一列。有外键的话ALTER COLUMN会直接失败得先摘掉外键。第三确认下游系统有没有依赖大小写不敏感这个行为。这条最容易被忽略。有些老系统在生产上跑了几年业务逻辑早就默认abc和ABC是同一个人你突然改成敏感等于把一批隐式的脏数据变成显式的不匹配。上线前一定要跑一遍数据体检SELECT UPPER(UserName) AS UpperName, COUNT(*) AS Cnt FROM dbo.T_User GROUP BY UPPER(UserName) HAVING COUNT(*) 1;有重复组的先跟业务确认怎么处理别自己拍板。第四准备回滚脚本并且演练过。回滚不是把之前那句ALTER反过来写一遍那么简单——如果迁移过程中数据被写入了新格式回滚时要把这部分处理好。4.2 在线迁移的四步走法大表直接ALTER COLUMN基本等于给自己挖坑。我用的是一套影子列 双写 切换的流程虽然麻烦但业务不中断。第一步加一个同类型的新列排序规则指定成 CSALTER TABLE dbo.T_User ADD UserName_CS VARCHAR(50) NULL COLLATE Chinese_PRC_CS_AS;第二步回填历史数据。分批更新每批控制在一万行左右避免长事务和日志暴涨DECLARE BatchSize INT 10000; WHILE 1 1 BEGIN UPDATE TOP (BatchSize) dbo.T_User SET UserName_CS UserName WHERE UserName_CS IS NULL; IF ROWCOUNT 0 BREAK; -- 视情况在这里加 WAITFOR DELAY给日志留出截断时间 END循环里的WAITFOR DELAY 00:00:00.100是可选项。生产库如果日志空间紧张加一个短延迟让日志备份能跟上比一次跑到底安全得多。第三步双写切换。应用层同时写两列或者用触发器同步CREATE TRIGGER dbo.trg_T_User_SyncCS ON dbo.T_User AFTER INSERT, UPDATE AS BEGIN UPDATE u SET UserName_CS u.UserName FROM dbo.T_User u JOIN inserted i ON i.Id u.Id; END触发器方案改动小但要注意触发器里的UPDATE会不会再触发自己不会默认不递归以及批量操作时的性能开销。第四步灰度验证后删旧列改名。应用层验证查询都改到新列之后把UserName_CS重命名成UserName。这一步要注意重命名后所有引用旧列名的报表、存储过程、视图都会报错得提前全库搜一遍。4.3 直接 ALTER COLUMN 的代价什么时候可以接受如果表不大或者停机窗口能接受最直接的办法还是ALTER TABLE dbo.T_User ALTER COLUMN UserName VARCHAR(50) COLLATE Chinese_PRC_CS_AS NOT NULL;它的锁表时间跟表大小、索引数量直接相关。经验值千万行以内的表、索引不超过三个一般几分钟内能完成。几亿行的表就老老实实走在线迁移别赌。这里有个细节值得提一句ALTER COLUMN换排序规则时SQL Server 会对数据做一次全量重写因为排序顺序变了物理存储也要变。所以不光是锁的问题数据库文件大小和日志大小都会涨操作前确认磁盘余量至少留出表的 2 倍空间。4.4 改数据库排序规则不等于改列排序规则这里有个流传很广的误解ALTER DATABASE YourDb COLLATE Chinese_PRC_CS_AS;很多人以为跑完这句库里的表就都变成大小写敏感了。并不会。这句话只影响三件事新建的对象、系统对象、以及一部分默认值。已有的用户表列排序规则纹丝不动。这个误解导致过不少改了没用的现场。真要改只能逐列ALTER COLUMN或者导出重建。改库级排序规则通常是在新库初始化阶段做而不是在运行中的老库上做。顺便说一句新库上线时我是这么定的如果业务不确定会不会用到大小写敏感直接给关键标识列单独指定CS_AS或BIN2别指望后期改。账号、邮箱、订单号、Token、密钥摘要这些都是天然应该区分大小写的字段一次定好省无数事。5. 持久化计算列加索引让大小写敏感查询走 Seek 的工程方案5.1 计算列里能不能放 COLLATE能。这可能是很多人不知道的一个用法。给表加一个持久化计算列列定义里直接写COLLATEALTER TABLE dbo.T_User ADD UserName_CS AS (UserName COLLATE Chinese_PRC_CS_AS) PERSISTED;几个关键点PERSISTED是必须的不持久化的计算列不能建索引准确说是不能作为索引键因为它是虚拟的每次都要重算。加了PERSISTED之后SQL Server 会在数据写入时就把计算结果存下来读取时直接拿。COLLATE表达式是确定性的所以可以持久化。这点我专门验证过没有报无法持久化的错误。计算列的排序规则会跟随表达式结果也就是Chinese_PRC_CS_AS。你可以在sys.columns里查collation_name确认SELECT name, collation_name, is_computed, is_persisted FROM sys.columns WHERE object_id OBJECT_ID(dbo.T_User);5.2 建索引并把查询改过去CREATE NONCLUSTERED INDEX IX_T_User_UserName_CS ON dbo.T_User(UserName_CS) INCLUDE (Id);然后查询写成SELECT Id FROM dbo.T_User WHERE UserName_CS zhangsan;这一次执行计划里应该能看到 Index Seek。因为UserName_CS就是一个物理列排序规则就是CS_AS和索引完全一致优化器可以正常利用。这条方案的精妙处在于它把排序规则从一个运行时表达式成本变成了一个写入时的存储成本。读多写少的表非常划算。用户表、字典表、配置表都是典型场景。对比一下前面几种方案方案读性能写性能改动范围适用场景查询加COLLATE扫描无影响只改 SQL小表、低频改列排序规则Seek无影响DDL 可能重建索引全表都要敏感计算列 索引Seek有额外存储与维护DDL 改 SQL部分查询需要敏感UPPER/LOWER扫描无影响只改 SQL不推荐5.3 写入放大的真实成本计算列不是白给的。三个成本要算清楚存储成本。计算列是实打实存了一份数据。VARCHAR(50)的列百万行表多占 50MB 左右看着不多但如果被INCLUDE进了多个索引占用会成倍增加。写入成本。每次 INSERT/UPDATE 都要重新计算并写入计算列同时维护索引。写密集的表日志表、流水表上做这个可能把写入吞吐拖下来 10% 到 20%。我一般会看写读比写远大于读的表不用这套。维护成本。计算列的表达式改了比如排序规则从CS_AS换成BIN2必须ALTER TABLE ... DROP COLUMN再重建索引也要跟着重建。这个操作在大表上就是一次完整的重建时间。还有一个坑计算列上不能直接建外键或唯一约束唯一约束可以建CREATE UNIQUE INDEX外键不行。如果你的业务依赖唯一性保证用唯一索引的形式实现CREATE UNIQUE NONCLUSTERED INDEX UX_T_User_UserName_CS ON dbo.T_User(UserName_CS);这样既保证大小写敏感下的唯一性又能走 Seek一举两得。6. 排序规则冲突跨库 JOIN、临时表与表变量的连环坑6.1 错误 468 的现场还原这个错误几乎每个碰过排序规则的开发者都遇到过Msg 468, Level 16, State 9 Cannot resolve the collation conflict between Chinese_PRC_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation.它的触发条件很简单比较运算两边的排序规则无法自动推导出一个更高优先级的。最常见的是三种组合业务库列 vs 临时表列、A 库列 vs B 库列、VARCHAR列 vs 显式COLLATE过的表达式。复现一下第一种-- 业务库表用 Chinese_PRC_CI_AS CREATE TABLE #Temp (UserName VARCHAR(50)); -- 用 tempdb 默认排序规则 SELECT a.* FROM dbo.T_User a JOIN #Temp b ON a.UserName b.UserName; -- 这里可能报 468是否真的报错取决于你实例上 tempdb 的排序规则和业务库是不是一致。很多人的开发环境和生产环境恰好一致所以本地跑得好好的上线就炸。6.2 临时表和表变量的排序规则到底从哪来这一条必须讲清楚因为它的反直觉程度很高。临时表#Temp和表变量Temp的字符列排序规则来自 tempdb不是当前数据库。创建表时即使写了CREATE TABLE #Temp (col VARCHAR(50))没显式指定COLLATE也是继承 tempdb 的默认值。DECLARE t TABLE (col VARCHAR(50))同理表变量的元数据也走 tempdb 那套规则。所以跨库场景下最稳的写法是在临时表定义里就把排序规则写死CREATE TABLE #Temp ( UserName VARCHAR(50) COLLATE Chinese_PRC_CS_AS );或者用DATABASE_DEFAULT让它跟随当前数据库CREATE TABLE #Temp ( UserName VARCHAR(50) COLLATE DATABASE_DEFAULT );DATABASE_DEFAULT这个关键字特别好用它表示当前上下文数据库的默认排序规则能让临时表自动对齐业务库不用你硬编码具体的规则名。缺点是如果业务库本身改过默认值代码要跟着理解一遍可读性稍差。6.3 三种消解冲突的写法怎么选第一种在 JOIN 条件里加COLLATE两边都加SELECT a.* FROM dbo.T_User a JOIN #Temp b ON a.UserName COLLATE DATABASE_DEFAULT b.UserName COLLATE DATABASE_DEFAULT;优点是改动小、立刻能跑缺点是两边都套了表达式索引全废。临时表小的时候无所谓a那侧是大表就要命了。第二种在临时表定义时就把排序规则对齐JOIN 里不写COLLATECREATE TABLE #Temp ( UserName VARCHAR(50) COLLATE DATABASE_DEFAULT ); SELECT a.* FROM dbo.T_User a JOIN #Temp b ON a.UserName b.UserName;这是我推荐的默认做法。改动只在建表那一步JOIN 保持干净大表那侧还能走索引。缺点是建临时表的地方多的话得记得每处都写。第三种用子查询先把数据转好再 JOINSELECT a.* FROM dbo.T_User a JOIN ( SELECT UserName COLLATE DATABASE_DEFAULT AS UserName FROM #Temp ) b ON a.UserName b.UserName;这种适合临时表数据量小、JOIN 频繁复用的场景把冲突消化在子查询里外层保持干净。缺点是写起来啰嗦别人接手时容易看漏。我一般按这个顺序选能在建表时对齐就在建表时对齐实在不行才在 JOIN 里加 COLLATE。反过来做的项目后面基本都是靠哪里报错补哪里在维护越补越乱。7. 上线前的自检清单与几条踩出来的经验7.1 一张逐项打勾的核对表每次要动排序规则相关的改动我都会过一遍这张表。建议你也存一份。检查项检查方式不通过的后果目标列当前排序规则sys.columns查collation_name改错列白忙一场目标列是否被索引引用sys.index_columnsALTER COLUMN时锁表超预期是否有重复键大小写变体UPPER()分组统计加唯一索引失败应用层参数类型是否匹配对比列类型与驱动映射隐式转换导致索引失效是否有跨库 JOIN搜JOIN里带库名的语句报 468 错误临时表是否显式指定排序规则搜CREATE TABLE #环境切换时行为不一致排序规则变更是否影响报表全库搜列名引用报表数据口径变化回滚脚本是否演练过在测试库实际跑一遍出问题时手足无措7.2 几个让我印象深刻的细节第一CHAR和VARCHAR的尾部空格行为不一样换排序规则时会更明显。CHAR(10)存储abc会自动补 7 个空格而VARCHAR不补。在CI_AS下因为 ANSI 标准里尾部空格不参与比较两者可能相等一旦换成BIN2长度都不同了必然不等。如果业务里有大量CHAR列参与比较改排序规则前一定要拿真实数据跑一遍别只看文档。第二LIKE的通配符在二进制排序下的行为。LIKE [a-z]%这种范围匹配在CI_AS下匹配所有字母开头在CS_AS下大小写敏感意味着[a-z]只匹配小写大写字母不在范围内。这个差异对搜索框功能影响很大很多搜索突然搜不到大写开头的记录的工单都源于此。改动前把带LIKE的查询全部列出来一条条跑测试用例。第三应用层缓存 key 必须跟着改。数据库改成区分大小写了但Redis里的 key 还是拿原始输入拼的比如user:zhangsan和user:ZhangSan会命中同一个 key结果缓存层还在做大小写不敏感的事表象上问题没解决。改库的时候顺手把缓存 key 的生成规则过一遍该加统一前缀就加该做规范化就做。第四日志和监控要同步调整。有监控系统靠 SQL 里的字符串匹配来做告警规则比如WHERE Status error。数据库改成大小写敏感后如果监控系统里写的是ERROR那条规则就永远不命中报警静默失效。这种问题只有出了事故才会发现属于典型的改了数据库忘了改周边。第五别在 SQL 里硬编码排序规则名除非你确信不会迁移。硬编码Chinese_PRC_CS_AS的代码将来数据库要迁到另一个语言区域全部得改。用DATABASE_DEFAULT或者把排序规则收拢到一个配置表里统一管理可维护性会好很多。第六改动落地之后用真实数据做一次回归而不是只看用例通过。我踩过一次坑测试库数据量小样例数据都是小写改完一切正常生产上线后出现一批UserName全大写的历史账号用户反馈登录失败。教训是上线前拿生产脱敏数据跑一遍回归别用构造数据自欺欺人。最后分享个小技巧。如果你不确定一条已经在跑的 SQL 到底受不受排序规则影响最快的验证方法是把 SQL 里的字面量换成正反面两种写法各跑一次看返回行数。返回行数一样就是大小写不敏感不一样就是敏感。这个方法不需要查任何元数据一分钟能确认一批语句排查阶段我经常这么用。