SQL Server中Collation的作用是什么

SQL Server中Collation的作用是什么，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

　　Collation的组成

　　在讲完字符的存储形式以后，我们就可以讲什么是collation了。Collation描述了数据在数据库中是按照什么规则来描述字符，以及字符时如何被排序和比较的。在SQL Server中，Collation由两部分组成，比如中国的一个collation是 Chinese_PRC_CI_AI_WS ，前半部份是指的是所支持的字符集，与code page相对应，如Chinese_PRC 对应的代码页是936，在这个code page中定义了所有能够使用的字符。后半部CI_AI_WS用于表示排序规则，比如：

　　_CI(CS)表示是否区分字母大小写，CI不区分，CS区分。如果区分大小写，那么排序的时候小写字母的排在大写的前面;如果不区分大小写，那么排序的时候视大小写字母相同。

　　_AI(AS) 表示是否区分重音，AI不区分，AS区分。如果不区分重音，那么排序的时候视“a”和“ấ”为相同字符

　　_KI(KS) 表示是否区分假名类型，KI不区分，KS区分。在日语中应用。

　　_WI(WS) 表示是否区分全半角，WI不区分，WS区分。半角是单字节，全角是双字节。

　　Collation的四个级别

　　Collation一共有四个级别，分别是server-level, database-level, column-level和expression-level。

　　Server-level Collations

　　服务器级别的collation是在安装数据库实例的时候指定的，如果没有特别指定，那么就将windows collation作为server-level collation。Windows collation由操作系统中的区域语言来决定的。

　　因为我们选择的是Chinese(Simplified,PRC)，那么我们默认的server-level collations就是：Chinese_PRC_。Server-level collation也是系统数据库和用户数据库的默认collation。一般情况下server-level collation一旦设定就不能更改，除非将所有数据库中的对象以及数据全部导出，并创建master，再将数据导回才可完成。

　　Database-level collations

　　Database-level collations可以在create database…collate的时候指定，如果要修改database-level collations，可以通过alter database …collate来修改。一般情况是不能修改系统数据库(master等)的collations的，除非使用前面提到的修改server-level collations的方法来修改系统数据库。

　　Column-level collations

　　在创建或更改表时，可使用 COLLATE 子句指定每个字符串列的排序规则。当然也可以修改column-level collations。

　　Expression-level collations

　　Expression-level是指在执行sql语句的时候指定collations，比如：

　　SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

　　这一条查询语句表示按照Latin1_General_CS_AI的排序规则来进行排序。Expression-level collations的一个好处就是非常灵活。

　　实验1：解决collation不匹配导致的冲突

　　在对两个collations级别不同的数据库的表进行连接操作的时候，会报错。这是可以通过expression-level collations来指定使用何种collations来解决问题。比如使用Collate Database_Default 则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。

　　Step1：

　　创建两张表，第一张表使用默认的collation，第二张表在stuname列上指定collation。

　　create table student1

　　(

　　stuid int not null,

　　stuname nvarchar(20) not null,

　　);

　　create table student2

　　(

　　stuid int not null,

　　stuname nvarchar(20) COLLATE Latin1_General_CS_AI not null,

　　);

　　--求表连接Step2：

　　select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname

　　执行上述查询报错如下所示：

　　Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "Chinese_PRC_CI_AS" in the equal to operation.

　　然后在expression-level使用Collate Database_Default

　　select s1.*,s2.* from student1 s1,student2 s2 where s1.stuname=s2.stuname Collate Database_Default

　　上述查询执行成功。

　　需要注意的是collation只能用在字符串类型的列上面，如果在int列上使用collate会报错。

　　实验2：变更collation对数据库的影响。

　　目的1：

　　创建数据库，查看数据的默认database collation与server collation是否一样。

　　目的2：

　　在database collation为Chinese_PRC_CI_AS的数据库中插入中文，然后修改collation为Latin1_General_CS_AI，看看已保存的数据有没有发生变化。如果再次把collation改回到Chinese_PRC_CI_AS，又有什么变化。

　　目的3：

　　在collation为Latin1_General_CS_AI的情况下，插入中文，会有什么情况，如何解决。

　　--实验1：测试nvarchar和varchar的存储长度

　　--创建一个默认collation为Chinese_PRC_CI_AS的数据库TESTDB3

　　USE TESTDB1

　　CREATE TABLE test

　　(

　　lastname NVARCHAR(8) NOT NULL,--nvarchar类型，双字节存储

　　title VARCHAR(8) NOT NULL, --varchar类型，单字节存储

　　);

　　insert into test values('姓名1','标题1');

　　select * from test;

　　insert into test values('123456789','1');--String or binary data would be truncated.

　　insert into test values('12345678','1');

　　insert into test values('1','12345678');

　　insert into test values('一二三四五六七八','一二三四');

　　select * from test;

　　--总结：

　　/*

　　1.nvarchar(n)，按字符来存储，不论是英文字符还是中文字符。最多能够存储n个中文或者是英文，但是所占用的存储空间是2n+2个字节。

　　2.varchar(n)按字节存储，最多能够存储n个英文字母，存储n/2个中文字符。但是所占用的存储空间是n个字节。

　　*/

　　--实验2：collation的变更对数据的影响。

　　USE TESTDB1

　　select * from test;

　　--step1：修改数据库的collation从默认的Chinese_PRC_CI_AS修改为Latin1_General_CS_AI，英语国家都是使用这个排序规则。

　　use master

　　alter database TESTDB1 collate Latin1_General_CS_AI

　　--step2：通过下面的语句可以查出实例中与默认collation不同的数据库，查找到了我们之前的 TESTDB1。

　　use master

　　SELECT

　　NAME AS DATABASE_NAME

　　, DATABASEPROPERTYEX(NAME,'COLLATION') AS DBCOLLATION

　　, SERVERPROPERTY('COLLATION') AS SERVERCOLLATION

　　FROM SYS.DATABASES

　　WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')

　　--step3：在修改完collation以后查看表中的数据，发现数据没有改变。

　　USE TESTDB1

　　select * from test;

　　--step4：在新的collation下面插入数据。

　　insert into test values('姓名1','标题1');--插入以后发现中文都变成了乱码"??"

　　select * from test;

　　--step5：因为lastname是nvarchar类型，我们在插入的时候指定出nvarchar。

　　insert into test values(N'姓名2','标题2');--此时发现姓名没有乱码，标题不用说还是乱码??

　　select * from test;

　　--step6：试一试在varchar类型的字段插入nvarchar是数据。

　　insert into test values(N'姓名3',N'标题3');--发现标题也不是乱码了。

　　select * from test;

　　------------------------------------

　　--step7：将collation从Latin1_General_CS_AI改回默认的Chinese_PRC_CI_AS。

　　use master

　　alter database TESTDB1 collate Chinese_PRC_CI_AS

　　--step8：在修改完collation以后查看表中的数据，发现数据没有改变。

　　USE TESTDB1

　　select * from test;

　　--step9:插入字符，不指定unicode类型，查询显示的是中文，表明当前collation默认使用unicode编码。

　　insert into test values('姓名4','标题4');

　　select * from test;

　　总结：

　　collation的变更不改变数据库原先存储的数据，原来是怎么样，修改以后还是怎样，没有发生改变。

　　Latin1_General_CS_AI默认是的non-unicode的，所以在这个collation下插入中文变成乱码，必须在插入数据的时候指明使用unicode形式插入，也就是添加关键字“N”，而Chinese_PRC_CI_AS这个collation使用的是double-byte code page，这里面定义了所有中文字符，所以在插入数据的时候不需要指定关键字“N”。

　　可以往varchar数据类型的列中插入nvarchar的数据，也就是使用varcha存储unicode的数据。

关于SQL Server中Collation的作用是什么问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。