用户在查询 SQL Server 表中的生僻字时,查询结果出现乱码。本文将介绍该问题的原因以及解决方法。
执行如下代码,查询 SQL Server 表中的生僻字“? (su)”。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <ol class = "linenums" > <li class = "L0" ><span class = "kwd" >use</span><span class = "pln" > tempdb</span></li> <li class = "L1" ><span class = "pln" >go</span></li> <li class = "L2" ><span class = "pln" >IF OBJECT_ID</span><span class = "pun" >(</span><span class = "str" > '#temp' </span><span class = "pun" >,</span><span class = "pln" > </span><span class = "str" > 'U' </span><span class = "pun" >)</span><span class = "pln" > IS NOT NULL</span></li> <li class = "L3" ><span class = "pln" > DROP TABLE </span><span class = "com" >#temp</span></li> <li class = "L4" ><span class = "pln" >GO</span></li> <li class = "L5" ></li> <li class = "L6" ><span class = "pln" >create table </span><span class = "com" >#temp(</span></li> <li class = "L7" ><span class = "pln" >firstName varchar</span><span class = "pun" >(</span><span class = "lit" > 10 </span><span class = "pun" >)</span></li> <li class = "L8" ><span class = "pun" >)</span></li> <li class = "L9" ></li> <li class = "L0" ><span class = "pln" >insert </span><span class = "kwd" >into</span><span class = "pln" > </span><span class = "com" >#temp</span></li> <li class = "L1" ><span class = "kwd" >select</span><span class = "pln" > </span><span class = "str" > '?' </span></li> <li class = "L2" ><span class = "kwd" >union</span><span class = "pln" > all</span></li> <li class = "L3" ><span class = "kwd" >select</span><span class = "pln" > </span><span class = "str" > '库' </span></li> <li class = "L4" ><span class = "pun" >;</span></li> <li class = "L5" ></li> <li class = "L6" ><span class = "kwd" >select</span><span class = "pln" > </span><span class = "pun" >*</span><span class = "pln" > </span><span class = "kwd" >from</span><span class = "pln" > </span><span class = "com" >#temp</span></li> </ol> |
显示结果如下,“? (su)”字并未正确显示,而是出现了问号“?”。
SQL Server 使用 Unicode 编码格式的数据类型(例如 NCHAR、NVARCHAR)来支持包含中文在内的亚洲语言。在查询代码中,数据类型必须是 Unicode 编码的数据类型。但在上述示例代码中使用的数据类型是 VARCHAR,所以导致查询结果出现乱码。
要解决在 SQL Server 的表中查询生僻字出现乱码的问题,只需要将上述示例代码中的数据类型改为 Unicode 编码格式的数据类型即可(下述示例中使用的是 NVARCHAR)。
另外,为避免乱码问题,在向 Unicode 编码格式的数据类型插入数据时,需要使用前置词 N。前置词 N 代表的是 SQL-92 标淮中的国家语言,且 N 必须大写。若您没有在 Unicode 字符串的常数前加 N 做为前置词,则 SQL Server 会在使用字符串之前将其转换成目前资料库的非 Unicode 字码页。
将上述示例中的数据类型 VARCHAR 改为 NVARCHAR,执行如下代码,查询 SQL Server 表中的生僻字“? (su)”。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <ol class = "linenums" > <li class = "L0" ><span class = "kwd" >use</span><span class = "pln" > tempdb</span></li> <li class = "L1" ><span class = "pln" >go</span></li> <li class = "L2" ><span class = "pln" >IF OBJECT_ID</span><span class = "pun" >(</span><span class = "str" > '#temp' </span><span class = "pun" >,</span><span class = "pln" > </span><span class = "str" > 'U' </span><span class = "pun" >)</span><span class = "pln" > IS NOT NULL</span></li> <li class = "L3" ><span class = "pln" > DROP TABLE </span><span class = "com" >#temp</span></li> <li class = "L4" ><span class = "pln" >GO</span></li> <li class = "L5" ></li> <li class = "L6" ><span class = "pln" >create table </span><span class = "com" >#temp(</span></li> <li class = "L7" ><span class = "pln" >firstName nvarchar</span><span class = "pun" >(</span><span class = "lit" > 10 </span><span class = "pun" >)</span></li> <li class = "L8" ><span class = "pun" >)</span></li> <li class = "L9" ></li> <li class = "L0" ><span class = "pln" >insert </span><span class = "kwd" >into</span><span class = "pln" > </span><span class = "com" >#temp</span></li> <li class = "L1" ><span class = "kwd" >select</span><span class = "pln" > N</span><span class = "str" > '?' </span></li> <li class = "L2" ><span class = "kwd" >union</span><span class = "pln" > all</span></li> <li class = "L3" ><span class = "kwd" >select</span><span class = "pln" > N</span><span class = "str" > '库' </span></li> <li class = "L4" ><span class = "pun" >;</span></li> <li class = "L5" ></li> <li class = "L6" ><span class = "kwd" >select</span><span class = "pln" > </span><span class = "pun" >*</span><span class = "pln" > </span><span class = "kwd" >from</span><span class = "pln" > </span><span class = "com" >#temp</span></li> </ol> |
显示结果如下,“? (su)”字正确显示出来了。