Microsoft Word - 文档 1

Size: px

Start display at page:

Download "Microsoft Word - 文档 1"

乙殿鄂
4 years ago
Views:

1 SQL SERVER 中 collate 的含义我们在 create table 时经常会碰到这样的语句, 例如 :password nvarchar(10)collate chinese_prc_ci_as null, 那它到底是什么意思呢? 不妨看看下面 : 首先,collate 是一个子句, 可应用于数据库定义或列定义以定义排序规则, 或应用于字符串表达式以应用排序规则投影语法是 collate collation_name collation_name ::={windows_collation_name} {sql_collation_name} 参数 collate_name 是应用于表达式列定义或数据库定义的排序规则的名称 collation_name 可以只是指定的 Windows_collation_name 或 SQL_collation_name Windows_collation_name 是 Windows 排序规则的排序规则名称参见 Windows 排序规则名称 SQL_collation_name 是 SQL 排序规则的排序规则名称参见 SQL 排序规则名称下面简单介绍一下排序规则 : 什么叫排序规则呢?MS 是这样描述的 :" 在 Microsoft SQL Server 2000 中, 字符串的物理存储由排序规则控制排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则 " 在查询分析器内执行下面语句, 可以得到 SQL SERVER 支持的所有排序规则 select * from ::fn_helpcollations() 排序规则名称由两部份构成, 前半部份是指本排序规则所支持的字符集如 : Chinese_PRC_CS_AI_WS 前半部份 : 指 UNICODE 字符集,Chinese_PRC_ 指针对大陆简体字 UNICODE 的排序规则排序规则的后半部份即后缀含义 : _BIN 二进制排序 _CI(CS) 是否区分大小写,CI 不区分,CS 区分 _AI(AS) 是否区分重音,AI 不区分,AS 区分 _KI(KS) 是否区分假名类型,KI 不区分,KS 区分 _WI(WS) 是否区分宽度 WI 不区分,WS 区分区分大小写 : 如果想让比较将大写字母和小写字母视为不等, 请选择该选项区分重音 : 如果想让比较将重音和非重音字母视为不等, 请选择该选项如果选择该选项, 比较还将重音不同的字母视为不等区分假名 : 如果想让比较将片假名和平假名日语音节视为不等, 请选择该选项区分宽度 : 如果想让比较将半角字符和全角字符视为不等, 请选择该选项利用排序规则特点计算汉字笔划和取得拼音首字母 SQL SERVER 的排序规则平时使用不是很多, 也许不少初学者还比较陌生, 但有一个错误大家应是经常碰到 : SQL SERVER 数据库, 在跨库多表连接查询时, 若两数据库默认字符集不同, 系统就会返回这样的错误 : 无法解决 equal to 操作的排序规则冲突一. 错误分析 : 这个错误是因为排序规则不一致造成的, 我们做个测试, 比如 : create table #t1( name varchar(20) collate Albanian_CI_AI_WS, value int) create table #t2(

2 name varchar(20) collate Chinese_PRC_CI_AI_WS, value int ) 表建好后, 执行连接查询 : select * from #t1 A inner join #t2 B on A.name=B.name 这样, 错误就出现了 : 服务器 : 消息 446, 级别 16, 状态 9, 行 1 无法解决 equal to 操作的排序规则冲突要排除这个错误, 最简单方法是, 表连接时指定它的排序规则, 这样错误就不再出现了语句这样写 : select * from #t1 A inner join #t2 B on A.name=B.name collate Chinese_PRC_CI_AI_WS 二. 排序规则简介 : 什么叫排序规则呢?MS 是这样描述的 :" 在 Microsoft SQL Server 2000 中, 字符串的物理存储由排序规则控制排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则 " 在查询分析器内执行下面语句, 可以得到 SQL SERVER 支持的所有排序规则 select * from ::fn_helpcollations() 排序规则名称由两部份构成, 前半部份是指本排序规则所支持的字符集如 : Chinese_PRC_CS_AI_WS 前半部份 : 指 UNICODE 字符集,Chinese_PRC_ 指针对大陆简体字 UNICODE 的排序规则排序规则的后半部份即后缀含义 : _BIN 二进制排序 _CI(CS) 是否区分大小写,CI 不区分,CS 区分 _AI(AS) 是否区分重音,AI 不区分,AS 区分 _KI(KS) 是否区分假名类型,KI 不区分,KS 区分 _WI(WS) 是否区分宽度 WI 不区分,WS 区分区分大小写 : 如果想让比较将大写字母和小写字母视为不等, 请选择该选项区分重音 : 如果想让比较将重音和非重音字母视为不等, 请选择该选项如果选择该选项, 比较还将重音不同的字母视为不等区分假名 : 如果想让比较将片假名和平假名日语音节视为不等, 请选择该选项区分宽度 : 如果想让比较将半角字符和全角字符视为不等, 请选择该选项三. 排序规则的应用 : SQL SERVER 提供了大量的 WINDOWS 和 SQLSERVER 专用的排序规则, 但它的应用往往被开发人员所忽略其实它在实践中大有用处例 1: 让表 NAME 列的内容按拼音排序 : create table #t(id int,name varchar(20)) insert #t select 1,' 中 ' union all select 2,' 国 ' union all select 3,' 人 ' union all select 4,' 阿 ' select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS

3 drop table #t /* 结果 : id name 4 阿 2 国 3 人 1 中 */ 例 2: 让表 NAME 列的内容按姓氏笔划排序 : create table #t(id int,name varchar(20)) insert #t select 1,' 三 ' union all select 2,' 乙 ' union all select 3,' 二 ' union all select 4,' 一 ' union all select 5,' 十 ' select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS drop table #t /* 结果 : id name 4 一 2 乙 3 二 5 十 1 三 */ 四. 在实践中排序规则应用的扩展 SQL SERVER 汉字排序规则可以按拼音笔划等排序, 那么我们如何利用这种功能来处理汉字的一些难题呢? 我现在举个例子 : 用排序规则的特性计算汉字笔划要计算汉字笔划, 我们得先做准备工作, 我们知道,WINDOWS 多国汉字,UNICODE 目前收录汉字共个简体 GBK 码汉字 UNICODE 值从开始首先, 我们先用 SQLSERVER 方法得到所有汉字, 不用字典, 我们简单利用 SQL 语句就可以得到 : select top code=identity(int,19968,1) into #t from syscolumns a,syscolumns b 再用以下语句, 我们就得到所有汉字, 它是按 UNICODE 值排序的 : select code,nchar(code) as CNWord from #t 然后, 我们用 SELECT 语句, 让它按笔划排序 select code,nchar(code) as CNWord from #t order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code 结果 :

4 code CNWord 一丨丶丿乀乁乙乚乛亅丁... 从上面的结果, 我们可以清楚的看到, 一笔的汉字,code 是从到 20101, 从小到大排, 但到了二笔汉字的第一个字丁,CODE 为 19969, 就不按顺序而重新开始了有了这结果, 我们就可以轻松的用 SQL 语句得到每种笔划汉字归类的第一个或最后一个汉字下面用语句得到最后一个汉字 : create table #t1(id int identity,code int,cnword nvarchar(2)) insert #t1(code,cnword) select code,nchar(code) as CNWord from #t order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code select A.cnword from #t1 A left join #t1 B on A.id=B.id 1 and A.code where B.code is null order by A.id 得到 36 个汉字, 每个汉字都是每种笔划数按 Chinese_PRC_Stroke_CS_AS_KS_WS 排序规则排序后的最后一个汉字 : 亅阝马风龙齐龟齿鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘上面可以看出 : 亅是所有一笔汉字排序后的最后一个字, 阝是所有二笔汉字排序后的最后一个字... 等等但同时也发现, 从第 33 个汉字龗 (33 笔 ) 后面的笔划有些乱, 不正确但没关系, 比龗笔划多的只有四个汉字, 我们手工加上 : 齾 35 笔, 齉 36 笔, 靐 39 笔, 龘 64 笔建汉字笔划表 (TAB_HZBH): create table tab_hzbh(id int identity,cnword nchar(1)) 先插入前 33 个汉字 insert tab_hzbh select top 33 A.cnword from #t1 A left join #t1 B on A.id=B.id 1 and A.code where B.code is null order by A.id 再加最后四个汉字 set identity_insert tab_hzbh on go

5 insert tab_hzbh(id,cnword) select 35,N' 齾 ' union all select 36,N' 齉 ' union all select 39,N' 靐 ' union all select 64,N' 龘 ' go set identity_insert tab_hzbh off go 到此为止, 我们可以得到结果了, 比如我们想得到汉字国的笔划 : nchar(1) 国 ' select top 1 id from tab_hzbh where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id id 8 ( 结果 : 汉字国笔划数为 8) 上面所有准备过程, 只是为了写下面这个函数, 这个函数撇开上面建的所有临时表和固定表, 为了通用和代码转移方便, 把表 tab_hzbh 的内容写在语句内, 然后计算用户输入一串汉字的总笔划 : create function fun_getbh(@str nvarchar(4000)) returns int as nchar(1),@n int while len(@str)>0 如果非汉字, 笔划当 0 计 when unicode(@word) between and then (select top 1 id from ( select 1 as id,n' 亅 ' as word union all select 2,N' 阝 ' union all select 3,N' 马 ' union all select 4,N' 风 ' union all select 5,N' 龙 ' union all select 6,N' 齐 ' union all select 7,N' 龟 ' union all select 8,N' 齿 ' union all select 9,N' 鸩 ' union all select 10,N' 龀 ' union all select 11,N' 龛 ' union all select 12,N' 龂 '

6 union all select 13,N' 龆 ' union all select 14,N' 龈 ' union all select 15,N' 龊 ' union all select 16,N' 龍 ' union all select 17,N' 龠 ' union all select 18,N' 龎 ' union all select 19,N' 龐 ' union all select 20,N' 龑 ' union all select 21,N' 龡 ' union all select 22,N' 龢 ' union all select 23,N' 龝 ' union all select 24,N' 齹 ' union all select 25,N' 龣 ' union all select 26,N' 龥 ' union all select 27,N' 齈 ' union all select 28,N' 龞 ' union all select 29,N' 麷 ' union all select 30,N' 鸞 ' union all select 31,N' 麣 ' union all select 32,N' 龖 ' union all select 33,N' 龗 ' union all select 35,N' 齾 ' union all select 36,N' 齉 ' union all select 39,N' 靐 ' union all select 64,N' 龘 ' ) T where word>=@word collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id ASC) else 0 ) 1) 函数调用实例 : select dbo.fun_getbh(' 中华人民共和国 '),dbo.fun_getbh(' 中華人民共和國 ') 执行结果 : 笔划总数分别为 39 和 46, 简繁体都行当然, 你也可以把上面 UNION ALL 内的汉字和笔划改存在固定表内, 在汉字列建 CLUSTERED INDEX, 列排序规则设定为 : Chinese_PRC_Stroke_CS_AS_KS_WS 这样速度更快如果你用的是 BIG5 码的操作系统, 你得另外生成汉字, 方法一样但有一点要记住 : 这些汉字是通过 SQL 语句 SELECT 出来的, 不是手工输入的, 更不是查字典得来的, 因为新华字典毕竟不同于 UNICODE 字符集, 查字典的结果会不正确用排序规则的特性得到汉字拼音首字母用得到笔划总数相同的方法, 我们也可以写出求汉字拼音首字母的函数如下 : create function fun_getpy(@str nvarchar(4000)) returns nvarchar(4000) as

7 nvarchar(4000) while set 如果非汉字字符, 返回原字符 set when between and then (select top 1 PY from ( select 'A' as PY,N' 驁 ' as word union all select 'B',N' 簿 ' union all select 'C',N' 錯 ' union all select 'D',N' 鵽 ' union all select 'E',N' 樲 ' union all select 'F',N' 鰒 ' union all select 'G',N' 腂 ' union all select 'H',N' 夻 ' union all select 'J',N' 攈 ' union all select 'K',N' 穒 ' union all select 'L',N' 鱳 ' union all select 'M',N' 旀 ' union all select 'N',N' 桛 ' union all select 'O',N' 漚 ' union all select 'P',N' 曝 ' union all select 'Q',N' 囕 ' union all select 'R',N' 鶸 ' union all select 'S',N' 蜶 ' union all select 'T',N' 籜 ' union all select 'W',N' 鶩 ' union all select 'X',N' 鑂 ' union all select 'Y',N' 韻 ' union all select 'Z',N' 咗 ' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) ) 1) 函数调用实例 : select dbo.fun_getpy(' 中华人民共和国 '),dbo.fun_getpy(' 中華人民共和國 ') 结果都为 :ZHRMGHG

i n i ho n n n n n ng

A i i i i i i i i n i ho n n n n n ng o o o o o o o b B b b p b b b b b b b p b b b b b b i b i b b i b b i b i b i b bn bn bn bn bn bn bn b bn bn bn bn bn bn bng bng bng b pn bng bng bng pn bng bo bo