（3.6）常用知识-sql server字符编码与排序规则

修改实例、数据库、表、字段参考：https://www.cnblogs.com/gered/p/9376896.html

服务器排序规则参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-2017

数据库排序规则参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-2017

　　概述：如果使用sql server在多种语言环境下开发应用系统，最容易产生额问题就是乱码。保存在数据库中的数据，在某些环境下能显示为正常数据，而在某些环境下，显示出来的是乱码。

      在进行字符数据排序时，sql server究竟是按照上面标准来决定字符数据的先后顺序的？在sql server中，这一切都与排序规则及字符存储采用的数据类型有关。

【0】排序规则基本分解

（1）查看sql server所支持的所有排序规则及描述

select * from ::fn_helpcollations()

　　

（2）排序规则的构成

排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。

例如：

Chinese_PRC_CS_AI_WS

　　前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。

　　但是要注意了，这里每个语言国家的字符集，都会有对应的字符编码页，存储的时候如果输入没有以N'' 开头，就以这个字符编码页来存储，具体后面在【1.2】中会说

排序规则的后半部份即后缀 含义：

_BIN 二进制排序
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分　　　
_KI(KS) 是否区分假名类型,KI不区分，KS区分　
_WI(WS) 是否区分宽度 WI不区分，WS区分　

【1】字符编码

【1.1】字符串存储的选择

在sql server中，字符存储有一下两种选择

　　（1）使用char、varchar、varchar(max)或者text这类非Unicode数据类型

　　使用这些数据类型时，每个字符的位模式以及存储和比较字符使用的规则由排序规则确定，不同的排序规则对应不同的代码页。

　　当使用费Unicode数据类型来存储字符数据时，要求数据存储的代码页和客户端应用程序的代码页必须相同，否则不能保持字符数据的完整性，因为代码页不相同时，客户端和服务器之间的转换可能导致某些字符丢失。

　　当客户端和数据库的代码页不匹配时，对所有发往（或发自）服务器的非Unicode字符串进行代码页转换时必须的，不支持通过禁用sql server ODBC驱动程序的AotoTranslate功能来插入由服务器上其他代码页定义的数据；即使禁用了AotoTranslate，也不能防止SQL语言实践进行代码页转换。

　　因为转换可能导致某些字符丢失，因此，应尽量避免客户端和数据库的代码页不匹配，以避免转换的发生，当然，也可以考虑客户端和数据库使用一种字符集大致相同的代码页，这样，两种嗲妈耶中的大部分字符可以想换转换而不会丢失数据。

　　（2）使用nchar、nvarchar、nvarchar(max)或者ntext这类Unicode数据类型

　　当只使用字符数据和代码页时，在一个数据库内很难以多种语言存储数据，也很难为数据库找到一种能存储所有需要的语言特定字符的代码页。此外，当运行不同代码页的不同客户端读取和更新特殊字符时，很难保证正确转换这些字符。使用Unicode类型可以解决这一类问题。

　　Unicode是一种将码位映射到字符的标准，它的设计涵盖世界上所有语言的全部字符，因此不需要不同的代码页来处理不同的字符集。如果所有使用国际化数据库的应用程序也采用Unicode变量而不是非Unicode变量，那么在系统中的任何地方都无需进行字符转换，客户端与所有其他客户端看到的数据中的字符相同。

　　即使采用了Unicode类型来存储数据，如果数据交互的客户端应用程序使用的是特殊代码页，也必须考虑如何保持字符数据的完整性。

【1.2】输入数据

　　在输入数据时，当把客户端发送的非Unicode数据以Unicode存储在服务器中时，如果具备下列条件之一，则来自任何客户端的任何代码页的数据都可以正确存储。

　　【1】字符串作为远程过程调用（RPC）的参数发送到服务器

　　【2】字符串常量以大写字母N开头。如果没有字母N前缀，则SQL server会将字符串转换为与数据库的默认排序规则相对应的代码页（这个代码页指的是操作系统自带的字符集代码页），次代码页中没有的字符都将丢失。

　　　　那么    select SERVERPROPERTY('SqlCharSetName')--查看服务器排序使用字符集名称 ，就可以查看到当前排序规则对应的代码页。

              这个代码页就是sql server默认的字符集！！！  像我们中国默认的排序规则 Chinese_PRC_CI_AS  ，对应的代码页就是 cp936，在操作系统字符集系统中反应出来的字符集就是 GBK

UTF-8    Unicode
CP437    MS-DOS 拉丁语(美国)
CP850    MS-DOS 拉丁语 1
CP874    拉丁语/泰语
CP932    日语 (Shift_JIS)
CP936    简体中文 (GBK)
CP949    韩语 (EUC-KR)
CP950    繁体中文 (Big5)
CP1251    西里尔语
CP1253    希腊语
CP1256    阿拉伯语
CP1257    波罗的语
CP1258    越南语
ISO-8859-1 / CP1252    拉丁语 - 1
ISO-8859-2 / CP1250    拉丁语 - 2
ISO-8859-3    拉丁语 - 3
ISO-8859-4    拉丁语 - 4
ISO-8859-5    拉丁语/西里尔语
ISO-8859-6    拉丁语/阿拉伯语
ISO-8859-7    拉丁语/希腊语
ISO-8859-8 / CP1255    希伯来语
ISO-8859-9 / CP1254    土耳其语
ISO-8859-13    拉丁语 - 7
ISO-8859-15    拉丁语 - 9

 

像【2.1】例子中的ISO_1 其实就是拉丁语1

 

【2】演示举例

【2.1】查看服务器与数据库字符集/排序规则

select SERVERPROPERTY('Collation') --查看服务器默认排序规则
select SERVERPROPERTY('SqlCharSetName')--查看服务器排序使用字符集名称

--查看服务器排序规则(安装时指定的排序规则)  
SELECT SERVERPROPERTY('COLLATION') AS ServerCollation  
,DATABASEPROPERTYEX('db_test','COLLATION') AS TempdbCollation  
,DATABASEPROPERTYEX(DB_NAME(),'COLLATION') AS CurrentDBCollation  
--查看字符集
select SERVERPROPERTY('SqlCharSetName') as '服务器字符集',
DATABASEPROPERTYEX('db_test','SqlCharSetName') as '数据库字符集'

--查看所有数据库排序规则  
SELECT name, collation_name FROM sys.databases 
　　
 --查看当前服务器与指定数据库的排序规则及字符集

　　

--查看所有库的排序规则
　　

 

【2.2】 切换成其他字符集查看

{1}在简体中文的环境下，创建排序规则为拉丁文的数据库db_test，并在该库上演示使用Unicode类型nvarchar来存储数据，是否会产生乱码。

　拉丁文字符集，其实可以粗略理解成只支持阿拉伯字母(即英文字母)和阿拉伯数字；

--创建拉丁文字符排序规则DB
create database db_test
collate Latin1_General_CI_AS
GO
--构造演示数据
use db_test
declare @tb tabl
(
    id int,
    col nvarchar(10)
)

insert @tb(id,col) select id = 1,col='中' union all
select id = 2,col = N'中'

--1.显示数据
select * from @tb

--2.条件检索
select * from @tb
where col='中'

select * from @tb
where col=N'中'

 演示如图：

　　

 

　　结论，不同排序编码对应使用不同字符集。

　　　　在显示数据1中：由于db_test数据库使用的是拉丁文排序规则，无法其代码页无法识别中文，所以出现乱码（为了统一信息sql server会把这种无法识别的字符给一个？号存储表示）

　　　　在显示数据2中：那么在筛选的时候，第1次筛选 where col = '中'，这里'中'这个字符无法识别，会转换成？号到内部，所以所有无法识别的？号数据全都查不出来了。

　　　　而在第2次筛选 where col =N'中'，则可以正常使用，不受排序规则的影响，因为其输入与存储都是用的Unicode 字符标准。

　　数据检索

　　　　如果客户端应用程序不支持Unicode而将数据检索到了非Unicode缓冲区，则客户端只能检索或修改客户机代码页可以表示的数据。

　　　　这样说不太明白，举例：即使用正常的方式存储了中文字符到数据库，但如果在未安装中文字符集的拉丁文系统中检索数据，这些中文字符依然无法正常显示。

　　

 

【3】排序规则

　　在描述字符编码的时候已经提到了排序规则，它仅仅适用于非Unicode数据类型，每个排序规则与某个特定的代码页关联，确定了每个字符的位模式以及存储和比较字符使用的规则。

　　排序规则确定了字符排序规则根据特定的语言和区域设置的标准制定对字符串数据进行排序和比较的规则。以order by 子句为例：如果按升序排列，说英语的人认为字符串Chiapas应该排在Colima之前：但是，对于在墨西哥说西班牙语的人来说，他们会认为以Ch开头的单词应该显示在以C开头的单词列表的末尾。排序规则规定了这些排序和比较规则。利用排序规则的这些特性，可以处理一些特殊的应用需求。

【3.1】区分大小写

　　通过默认设置安装SQL Server时，sql server是不区分大小写的，所以很多用户会误以为sql server的处理是不区分大小写的，其实这个主要跟排序规则有关。

　　下面的示例演示如何通过collate子句来转换表达式的排序规则，从而实现字符的比较按指定的排序规则进行（需要注意示例中的全角和半角字符）

　　

select 
    case when 'a' collate chinese_prc_90_cs_as = 'A'
    then 1 else 0 end　'区分大小写',
    case when 'a'  collate chinese_prc_90_ci_as = 'Ａ' 
    then 1 else 0 end '不区分全角与半角',
    case when 'a'  collate chinese_prc_90_ci_as_ws = 'Ａ' 
    then 1 else 0 end '区分全角与半角'

结果：


参数示例：
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分　　　
_KI(KS) 是否区分假名类型,KI不区分，KS区分　
_WI(WS) 是否区分宽度 WI不区分，WS区分
BIN：指定使用后向后兼容的二进制排序顺序
BIN2：指定使用sql server2005中引入的码位比较寓意的二进制排序顺序

--查看sql server所支持的所有有效的windows与sql排序规则
select * from ::fn_helpcollations() 

 

【3.2】排序规则排序(拼音,笔画,)

　　除了应用排序规则字符的比较方式，还可以使用排序规则实现特定的排序。

（1）按拼音排序　

;with test as
(
    select N'排序规则' as 'col' union all
    select N'在' union all
    select N'中文字符' union all
    select N'用拼音排序' union all
    select N'的应用'  union all
    select N'贼'
)
select * from test order by col collate chinese_prc_90_cs_as_ks_ws

结果如图：

　　 

（2）按笔画排序

;with test as
(
    select N'排序规则' as 'col' union all
    select N'在' union all
    select N'中文字符' union all
    select N'用拼音排序' union all
    select N'的应用'  union all
    select N'贼'
)
select * from test order by col collate chinese_Stroke_prc_90_cs_as_ks_ws

 

很明显，这只按第一个字，如果笔画相同才会考虑第二个字。