仓酷云

标题: MSSQL编程：使用排序划定规矩特性盘算汉字笔画和获得拼... [打印本页]

作者: 兰色精灵 时间: 2015-1-16 22:32
标题: MSSQL编程：使用排序划定规矩特性盘算汉字笔画和获得拼...
也就是说在php本地调用pdoprepare中的mysql_real_escape_string来操作query，使用的是本地单字节字符集，即编码为xbfx5cx27，并带入到mysql中查询，由于使用setnames设置了连接字符集.汉字|排序|拼音

　　SQL　SERVER的排序划定规矩平常利用不是良多，大概很多初学者还对照生疏，但有
一个毛病人人应是常常碰着:SQLSERVER数据库，在跨库多表毗连查询时，若两数据
库默许字符集分歧，体系就会前往如许的毛病：

“没法办理equalto操纵的排序划定规矩抵触。”

一.毛病剖析：
　　这个毛病是由于排序划定规矩纷歧致酿成的，我们做个测试，好比：
createtable#t1(
namevarchar(20)collateAlbanian_CI_AI_WS,
valueint)

createtable#t2(
namevarchar(20)collateChinese_PRC_CI_AI_WS,
valueint)

表建好后，实行毗连查询：

select*from#t1Ainnerjoin#t2BonA.name=B.name

如许，毛病就呈现了：

服务器:动静446，级别16，形态9，行1
没法办理equalto操纵的排序划定规矩抵触。
　　要扫除这个毛病，最复杂办法是，表毗连时指定它的排序划定规矩，如许毛病就
不再呈现了。语句如许写：

select*
from#t1Ainnerjoin#t2B
onA.name=B.namecollateChinese_PRC_CI_AI_WS

二.排序划定规矩简介：

甚么叫排序划定规矩呢？MS是如许形貌的："在MicrosoftSQLServer2000中，
字符串的物理存储由排序划定规矩把持。排序划定规矩指定暗示每一个字符的位形式和存
储和对照字符所利用的划定规矩。"
　　在查询剖析器内实行上面语句，能够失掉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辨别　

辨别巨细写:假如想让对照将年夜写字母和小写字母视为不等，请选择该选项。
辨别重音:假如想让对照将重音和非重音字母视为不等，请选择该选项。假如选择该选项，
对照还将重音分歧的字母视为不等。
辨别化名:假如想让对照将片化名战争化名日语音节视为不等，请选择该选项。
辨别宽度:假如想让对照将半角字符和全角字符视为不等，请选择该选项

三.排序划定规矩的使用：
　　SQLSERVER供应了大批的WINDOWS和SQLSERVER公用的排序划定规矩，但它的使用常常
被开辟职员所疏忽。实在它在理论中年夜有效处。

　　例1:让表NAME列的内容按拼音排序：

createtable#t(idint,namevarchar(20))
insert#tselect1,中
unionallselect2,国
unionallselect3,人
unionallselect4,阿

select*from#torderbynamecollateChinese_PRC_CS_AS_KS_WS
droptable#t
/*了局：
idname
-------------------------------
4阿
2国
3人
1中
*/

　　例2：让表NAME列的内容按姓氏笔画排序：

createtable#t(idint,namevarchar(20))

insert#tselect1,三
unionallselect2,乙
unionallselect3,二
unionallselect4,一
unionallselect5,十
select*from#torderbynamecollateChinese_PRC_Stroke_CS_AS_KS_WS
droptable#t
/*了局：
idname
-------------------------------
4一
2乙
3二
5十
1三
*/

四.在理论中排序划定规矩使用的扩大
　　SQLSERVER汉字排序划定规矩能够按拼音、笔画等排序，那末我们怎样使用这类功效
来处置汉字的一些困难呢？我如今举个例子：

　　　　　　　　　　用排序划定规矩的特征盘算汉字笔画

　　要盘算汉字笔画，我们得先做筹办事情，我们晓得，WINDOWS多国汉字，UNICODE今朝
收录汉字共20902个。简体GBK码汉字UNICODE值从19968入手下手。
　　起首，我们先用SQLSERVER办法失掉一切汉字，不必字典，我们复杂使用SQL语句就
能够失掉：

selecttop20902code=identity(int,19968,1)into#tfromsyscolumnsa,syscolumnsb

再用以下语句，我们就失掉一切汉字，它是按UNICODE值排序的：

　　selectcode,nchar(code)asCNWordfrom#t

　　然后，我们用SELECT语句，让它按笔画排序。

selectcode,nchar(code)asCNWord
from#t
orderbynchar(code)collateChinese_PRC_Stroke_CS_AS_KS_WS,code

了局：
codeCNWord
-----------------
19968一
20008丨
20022丶
20031丿
20032T
20033U
20057乙
20058]
20059^
20101|
19969丁
..........

　从下面的了局，我们能够分明的看到，一笔的汉字，code是从19968到20101，从小到年夜排，但到
了二笔汉字的第一个字“丁”，CODE为19969，就不按按次而从头入手下手了。有了这了局，我们就能够轻
松的用SQL语句失掉每种笔画汉字回类的第一个或最初一个汉字。
上面用语句失掉最初一个汉字：

createtable#t1(idintidentity,codeint,cnwordnvarchar(2))

insert#t1(code,cnword)
selectcode,nchar(code)asCNWordfrom#t
orderbynchar(code)collateChinese_PRC_Stroke_CS_AS_KS_WS,code

selectA.cnword
from#t1A
leftjoin#t1BonA.id=B.id-1andA.code<B.code
whereB.codeisnull
orderbyA.id

失掉36个汉字，每一个汉字都是每种笔画数按Chinese_PRC_Stroke_CS_AS_KS_WS排序划定规矩排序后的
最初一个汉字：

|阝马风龙齐龟齿鸩龀龛龆龈龊龠PK[Q

　　下面能够看出：“|”是一切一笔汉字排序后的最初一个字，“阝”是一切二笔汉字排序后的最初
一个字......等等。
　　但同时也发明，从第33个汉字“(33笔)”前面的笔画有些乱，不准确。但不妨，比“”笔画
多的只要四个汉字，我们手工加上：35笔，Q36笔，h39笔，64笔

建汉字笔画表（TAB_HZBH）：
createtabletab_hzbh(idintidentity,cnwordnchar(1))
--先拔出前33个汉字
inserttab_hzbh
selecttop33A.cnword
from#t1A
leftjoin#t1BonA.id=B.id-1andA.code<B.code
whereB.codeisnull
orderbyA.id
--再加最初四个汉字
setidentity_inserttab_hzbhon
go
inserttab_hzbh(id,cnword)
　　　　　select35,N
unionallselect36,NQ
unionallselect39,Nh
unionallselect64,N
go
setidentity_inserttab_hzbhoff
go

　　到此为止，我们能够失掉了局了，好比我们想失掉汉字“国”的笔画：

declare@anchar(1)
set@a=国
selecttop1id
fromtab_hzbh
wherecnword>=@acollateChinese_PRC_Stroke_CS_AS_KS_WS
orderbyid

id
-----------
8
(了局：汉字“国”笔画数为8)

　　下面一切筹办历程，只是为了写上面这个函数，这个函数撇开下面建的一切一时表和固
定表，为了通用和代码转移便利，把表tab_hzbh的内容写在语句内，然后盘算用户输出一串
汉字的总笔画：

createfunctionfun_getbh(@strnvarchar(4000))
returnsint
as
begin
declare@wordnchar(1),@nint
set@n=0
whilelen(@str)>0
begin
set@word=left(@str,1)
--假如非汉字，笔画当0计
set@n=@n+(casewhenunicode(@word)between19968and19968+20901
then(selecttop1idfrom(
select1asid,N|asword
unionallselect2,N阝
unionallselect3,N马
unionallselect4,N风
unionallselect5,N龙
unionallselect6,N齐
unionallselect7,N龟
unionallselect8,N齿
unionallselect9,N鸩
unionallselect10,N龀
unionallselect11,N龛
unionallselect12,N
unionallselect13,N龆
unionallselect14,N龈
unionallselect15,N龊
unionallselect16,N
unionallselect17,N龠
unionallselect18,N
unionallselect19,N
unionallselect20,N
unionallselect21,N
unionallselect22,N
unionallselect23,N
unionallselect24,N
unionallselect25,N
unionallselect26,N
unionallselect27,NP
unionallselect28,N
unionallselect29,NK
unionallselect30,N[
unionallselect31,N
unionallselect32,N
unionallselect33,N
unionallselect35,N
unionallselect36,NQ
unionallselect39,Nh
unionallselect64,N
)T
whereword>=@wordcollateChinese_PRC_Stroke_CS_AS_KS_WS
orderbyidASC)else0end)
set@str=right(@str,len(@str)-1)
end
return@n
end

--函数挪用实例：
selectdbo.fun_getbh(中华国民共和国),dbo.fun_getbh(中A国民共和)
　
　　实行了局：笔画总数分离为39和46，简繁体都行。

固然，你也能够把下面“UNION　ALL”内的汉字和笔画改存在流动表内，在汉字
列建CLUSTEREDINDEX，列排序划定规矩设定为：
　　　Chinese_PRC_Stroke_CS_AS_KS_WS
如许速率更快。假如你用的是BIG5码的操纵体系，你得别的天生汉字，办法一样。
但有一点要记着：这些汉字是经由过程SQL语句SELECT出来的，不是手工输出的，更不
是查字典得来的，由于新华字典究竟分歧于UNICODE字符集，查字典的了局会不正
确。

　　
　　　　用排序划定规矩的特征失掉汉字拼音首字母

　　用失掉笔画总数不异的办法，我们也能够写出求汉字拼音首字母的函数。以下：

createfunctionfun_getPY(@strnvarchar(4000))
returnsnvarchar(4000)
as
begin
declare@wordnchar(1),@PYnvarchar(4000)
set@PY=
whilelen(@str)>0
begin
set@word=left(@str,1)
--假如非汉字字符，前往原字符
set@PY=@PY+(casewhenunicode(@word)between19968and19968+20901
then(selecttop1PYfrom(
selectAasPY,Nasword
unionallselectB,N簿
unionallselectC,Ne
unionallselectD,Nz
unionallselectE,N
unionallselectF,Nv
unionallselectG,NB
unionallselectH,N
unionallselectJ,Nh
unionallselectK,Ni
unionallselectL,Nw
unionallselectM,N
unionallselectN,N
unionallselectO,Na
unionallselectP,N曝
unionallselectQ,N
unionallselectR,NU
unionallselectS,NR
unionallselectT,NX
unionallselectW,NF
unionallselectX,NR
unionallselectY,N
unionallselectZ,N
)T
whereword>=@wordcollateChinese_PRC_CS_AS_KS_WS
orderbyPYASC)else@wordend)
set@str=right(@str,len(@str)-1)
end
return@PY
end

--函数挪用实例：
selectdbo.fun_getPY(中华国民共和国),dbo.fun_getPY(中A国民共和)
了局都为：ZHRMGHG

　你如有乐趣，也可用不异的办法，扩大为失掉汉字全拼的函数，乃至还能够失掉全拼的读
音腔调，不外全拼分类年夜多了。失掉全拼最好是用对比表，两万多汉字搜刮速率很快，用对比
表还能够充实使用表的索引。
排序划定规矩另有良多别的的奇妙用法，限于篇幅在此就不再具体申明。接待人人配合切磋。

限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。

作者: 兰色精灵 时间: 2015-1-19 16:17
数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。

作者: 小魔女 时间: 2015-1-26 21:57
也可谈一下你是怎么优化存储过程的?

作者: 乐观 时间: 2015-2-4 21:04
换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

作者: 活着的死人 时间: 2015-2-10 10:47
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

作者: 若相依 时间: 2015-3-1 10:01
我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

作者: 小妖女 时间: 2015-3-1 10:01
原来公司用过MYSQL自己也只是建个表写个SQL

作者: 冷月葬花魂 时间: 2015-3-10 13:25
索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

作者: admin 时间: 2015-3-17 08:10
如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

作者: 灵魂腐蚀 时间: 2015-3-24 03:57
这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

欢迎光临仓酷云 (http://ckuyun.com/)