解决 SQL Server 表中的中文乱码问题

  • A+
所属分类:SQL Server
高性能企业级服务器首台5折

背景信息

用户在查询 SQL Server 表中的生僻字时,查询结果出现乱码。本文将介绍该问题的原因以及解决方法。

问题复现示例

执行如下代码,查询 SQL Server 表中的生僻字“䅇 (su)”。

  1. 1
    <span class="kwd"><span class="hljs-keyword">use</span></span><span class="pln"> tempdb</span>
  2. 1
    <span class="pln"><span class="hljs-attribute">go</span></span>
  3. 1
    <span class="pln"><span class="hljs-keyword">IF</span> OBJECT_ID</span><span class="pun">(</span><span class="str"><span class="hljs-string">'#temp'</span></span><span class="pun">,</span> <span class="str"><span class="hljs-string">'U'</span></span><span class="pun">)</span><span class="pln"> IS <span class="hljs-literal">NOT</span> <span class="hljs-literal">NULL</span></span>
  4. 1
    <span class="pln">    DROP <span class="hljs-keyword">TABLE</span> </span><span class="com">#temp</span>
  5. 1
    <span class="pln"><span class="hljs-attribute">GO</span></span>
  6. 1
     
  7. 1
    <span class="pln">create <span class="hljs-keyword">table</span> </span><span class="com">#temp(</span>
  8. 1
    <span class="pln"><span class="hljs-selector-tag">firstName</span> <span class="hljs-selector-tag">varchar</span></span><span class="pun">(</span><span class="lit"><span class="hljs-number">10</span></span><span class="pun">)</span>
  9. 1
    <span class="pun">)</span>
  10. 1
     
  11. 1
    <span class="pln">insert </span><span class="kwd"><span class="hljs-keyword">into</span></span> <span class="com"><span class="hljs-comment">#temp</span></span>
  12. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span> <span class="str"><span class="hljs-string">'䅇'</span></span>
  13. 1
    <span class="kwd"><span class="hljs-class"><span class="hljs-keyword">union</span></span></span><span class="pln"><span class="hljs-class"> <span class="hljs-title">all</span></span></span>
  14. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span> <span class="str"><span class="hljs-string">'库'</span></span>
  15. 1
    <span class="pun"><span class="hljs-comment">;</span></span>
  16. 1
     
  17. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span> <span class="pun">*</span> <span class="kwd"><span class="hljs-keyword">from</span></span> <span class="com"><span class="hljs-meta">#temp</span></span>

显示结果如下,“䅇 (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. 1
    <span class="kwd"><span class="hljs-keyword">use</span></span><span class="pln"> tempdb</span>
  2. 1
    <span class="pln"><span class="hljs-attribute">go</span></span>
  3. 1
    <span class="pln"><span class="hljs-keyword">IF</span> OBJECT_ID</span><span class="pun">(</span><span class="str"><span class="hljs-string">'#temp'</span></span><span class="pun">,</span> <span class="str"><span class="hljs-string">'U'</span></span><span class="pun">)</span><span class="pln"> IS <span class="hljs-literal">NOT</span> <span class="hljs-literal">NULL</span></span>
  4. 1
    <span class="pln">    DROP <span class="hljs-keyword">TABLE</span> </span><span class="com">#temp</span>
  5. 1
    <span class="pln"><span class="hljs-attribute">GO</span></span>
  6. 1
     
  7. 1
    <span class="pln">create <span class="hljs-keyword">table</span> </span><span class="com">#temp(</span>
  8. 1
    <span class="pln"><span class="hljs-selector-tag">firstName</span> <span class="hljs-selector-tag">nvarchar</span></span><span class="pun">(</span><span class="lit"><span class="hljs-number">10</span></span><span class="pun">)</span>
  9. 1
    <span class="pun">)</span>
  10. 1
     
  11. 1
    <span class="pln">insert </span><span class="kwd"><span class="hljs-keyword">into</span></span> <span class="com"><span class="hljs-comment">#temp</span></span>
  12. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span><span class="pln"> N</span><span class="str"><span class="hljs-string">'䅇'</span></span>
  13. 1
    <span class="kwd"><span class="hljs-class"><span class="hljs-keyword">union</span></span></span><span class="pln"><span class="hljs-class"> <span class="hljs-title">all</span></span></span>
  14. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span><span class="pln"> N</span><span class="str"><span class="hljs-string">'库'</span></span>
  15. 1
    <span class="pun"><span class="hljs-comment">;</span></span>
  16. 1
     
  17. 1
    <span class="kwd"><span class="hljs-keyword">select</span></span> <span class="pun">*</span> <span class="kwd"><span class="hljs-keyword">from</span></span> <span class="com"><span class="hljs-meta">#temp</span></span>

显示结果如下,“䅇 (su)”字正确显示出来了。

正确显示结果

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: