[转]SQL数据库查询到的汉字字段是乱码

使用英文版SQL数据库查询到的汉字字段是乱码的解决方案

2007-12-04 14:55:45

标签：函数 SQL 数据库 乱码 排序规则

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://fengyp.blog.51cto.com/276754/53456

我在使用数据库查询语句时，出现了乱码，为了解决这个问题，我花费了不少时间，现将解决办法贴出来，供大家参考。

一、在查询分析器中出现的乱码

这类问题比较好解决，主要是由于查询分析器的工具菜单中的选项栏中连接项中的“执行字符串转换”被打了勾，将勾去掉即可。 

二、在ASP.NET（C#）中出现乱码

一开始我在编写网页程序的时候也出现了查询英文数据库的时候，汉字是乱码的问题，经查，原来是使用了下列语句造成的：

SqlConnection smycn = new SqlConnection(con(cn));

后来我仿造在VB中的解决此问题的方法，改成用下列语句连接数据库，问题得到了解决：

OleDbConnection omycn = new OleDbConnection(con(cn));

三、在SQL函数中出现乱码

SQL函数中乱码是将汉字写入临时表的时候产生的，使用上面的方法解决不了。

我的方法是改变SQL函数中汉字字段的排序方法。下面贴出的是有问题的函数：

CREATE FUNCTION [dbo].[OA提料] (@item varchar(3),@ms varchar(3)) 

RETURNS  @OA  table

(订单号 varchar(25),行号 varchar(3),物料号 varchar(25),物料描述 varchar(35)

,计划数 float,开工日期 smalldatetime,完工日期 smalldatetime,已领料数 float)

as

BEGIN

if (@ms='')

if (len(@item)=2)

begin

insert @OA 

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,rtrim(Mfg_ITMMAST.IMDESC)   物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),4),2)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),rtrim(Mfg_ITMMAST.IMDESC) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

else

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,rtrim(Mfg_ITMMAST.IMDESC)     物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),5),3)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),rtrim(Mfg_ITMMAST.IMDESC) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

else

if (len(@item)=2)

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,rtrim(Mfg_ITMMAST.IMDESC)     物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),4),2)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

       and case when len(@ms)=2 then right(left(Mfg_MS.MSCOMPNO,4),2) else right(left(Mfg_MS.MSCOMPNO,5),3) end=@ms

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),rtrim(Mfg_ITMMAST.IMDESC),Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

else

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,rtrim(Mfg_ITMMAST.IMDESC)    物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),5),3)=@item

       and case when len(@ms)=2 then right(left(Mfg_MS.MSCOMPNO,4),2) else right(left(Mfg_MS.MSCOMPNO,5),3) end=@ms

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),rtrim(Mfg_ITMMAST.IMDESC) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

return

end

下面贴出的没有问题的函数：

CREATE FUNCTION [dbo].[OA提料] (@item varchar(3),@ms varchar(3)) 

RETURNS  @OA  table

(订单号 varchar(25),行号 varchar(3),物料号 varchar(25),物料描述 varchar(35) collate SQL_Latin1_General_CP1_CI_AI

,计划数 float,开工日期 smalldatetime,完工日期 smalldatetime,已领料数 float)

as

BEGIN

 

if (@ms='')

if (len(@item)=2)

begin

insert @OA 

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35))    物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),4),2)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35)) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

else

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35))     物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),5),3)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35)) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

 

end

else

if (len(@item)=2)

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35))      物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),4),2)=@item

       and len(rtrim(Mfg_ITMMAST.IMPN))=11

       and case when len(@ms)=2 then right(left(Mfg_MS.MSCOMPNO,4),2) else right(left(Mfg_MS.MSCOMPNO,5),3) end=@ms

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35)) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

else

begin

insert @OA

select  rtrim(Mfg_ORDMAST.OMON)       订单号

       ,Mfg_SF.LINENUM                行号

       ,rtrim(Mfg_ITMMAST.IMPN)       物料号

       ,cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35))      物料描述

       ,Mfg_SF.QTYREQ                 计划数

       ,Mfg_SF.INSTRTDT               开工日期

       ,Mfg_SF.REQDUEDT               完工日期

       ,sum(Mfg_DFCNTRL.DCQTYISS)     已领料数

from   Mfg_ORDMAST

       left outer join Mfg_DFSFNSF as Mfg_SF on ((Mfg_SF._ORDS_OwnRow = Mfg_ORDMAST._Row) and (Mfg_SF.RECTYPE = 'S'))

       left outer join Mfg_ITMMAST on ((Mfg_ITMMAST._Row = Mfg_SF._ITMS_OwnRow) and (Mfg_SF._ITMS_OwnRec = 19))

       left outer join Mfg_MS on (Mfg_MS._BILL_OwnRow = Mfg_ITMMAST._Row)

       left outer join Mfg_ORDCNTRL on (Mfg_ORDCNTRL.ORDNUM = rtrim(Mfg_ORDMAST.OMON)) and Mfg_ORDCNTRL.LINENUM=Mfg_SF.LINENUM

       left outer join Mfg_DFCNTRL on (Mfg_ORDCNTRL._Row = Mfg_DFCNTRL._CNTRDF_OwnRow)

where  Mfg_ORDMAST._SYSORD_MbrKey like 'M%' and Mfg_SF.ORDSTA=4 and right(left(rtrim(Mfg_ITMMAST.IMPN),5),3)=@item

       and case when len(@ms)=2 then right(left(Mfg_MS.MSCOMPNO,4),2) else right(left(Mfg_MS.MSCOMPNO,5),3) end=@ms

group by rtrim(Mfg_ORDMAST.OMON),Mfg_SF.LINENUM,rtrim(Mfg_ITMMAST.IMPN),cast(rtrim(Mfg_ITMMAST.IMDESC)  as varchar(35)) ,Mfg_SF.QTYREQ

       ,Mfg_SF.INSTRTDT,Mfg_SF.REQDUEDT

end

return

end

红字的内容是没有乱码的函数和有乱码的函数的区别之所在。由于公司有各种各样的数据库，有些数据库会产生乱码，有些数据库不会产生乱码，那么只要看一下产生乱码的数据库的排序规则和不产生乱码的数据库的排序规则，然后将在会产生乱码的数据库中临时表的有汉字内容的字段的排序规则转换成不产生乱码的数据库的排序规则即可。如果没有办法比较，则需要对各种排序规则进行一一测试，总能找到一条规则符合要求。

本文出自 “风雨同舟” 博客，请务必保留此出处http://fengyp.blog.51cto.com/276754/53456