SQL Server Collation介绍

转载 2015年11月18日 13:04:30

Question:

What is Collation? How it will affect SQL Server database, and server?

Answer:

字符的存储

        在将collation之前,我们首先需要知道字符是如何被存储的。在计算机中,所有数据都是用0和1这样的位来描述。一个字节有8位,因此一个字节最多可以描述256个字符。在欧美国家,比如美国,他们的文字字符主要就是26个字母加上一些特殊符号(+-*/等),用一个字节就可以存储,一个国家使用的所有字符就是一个code page,用一个字节存储字符的code page 叫做single-byte code page。但是在亚洲的一些国家,比如中国,常用汉字有几万个,根本不能用一个字节来表示所有的汉字字符,因此需要用两个字节描述。因为两个字节有16位,最多可以描述65536个字符,足够用来描述所有汉语字符以及常用字符,这些字符也是一个code page,不过是double-byte code page,主要针对的是中国。每个国家都有一个code page来对应所使用的字符。比如欧美国家,他们使用拉丁,虽然a-z这26个字母所对应的二进制在code page中是相同的,但是在重音('é'和'á')方面是不相同的。所以如果code page不同,那么相同的二进制代码所表示出来的字符也可能不相同。如果数据在不同code page的计算机上传输,就需要进行code page的转换,如果接收方的code page上没有定义传输方传送过来的特定字符的二进制位,那么就会出现数据丢失。

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个字节。1<=n<=4000
2.varchar(n)按字节存储,最多能够存储n个英文字母,存储n/2个中文字符。但是所占用的存储空间是n个字节。1<=n<=8000
*/


--实验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;
复制代码

总结:

  1. collation的变更不改变数据库原先存储的数据,原来是怎么样,修改以后还是怎样,没有发生改变。
  2. Latin1_General_CS_AI默认是的non-unicode的,所以在这个collation下插入中文变成乱码,必须在插入数据的时候指明使用unicode形式插入,也就是添加关键字“N”,而Chinese_PRC_CI_AS这个collation使用的是double-byte code page,这里面定义了所有中文字符,所以在插入数据的时候不需要指定关键字“N”。
  3. 可以往varchar数据类型的列中插入nvarchar的数据,也就是使用varcha存储unicode的数据。

更多参考:
SQL Server 排序规则
http://www.cnblogs.com/sydeveloper/archive/2012/11/29/2791157.html

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

转自:http://www.cnblogs.com/xwdreamer/archive/2012/07/11/2585993.html


正确设置与使用SQL Server的字符集(Collation,即排序规则)

目录 目录 正确认识SQL Server的字符集 选择合适的SQL Server字符集 错误使用SQL Server的字符集 参考资料正确认识SQL Server的字符集SQL Server作为一个国...

windows2008计划任务无法运行解决方案

近碰到一个问题windows 2008的计划任务执行问题,程序如下图SendMessage V2.0.exe运行需要调用SendMessage V2.0.pdb数据库的数据 症状如下,计划任...
  • quanhj
  • quanhj
  • 2014年11月27日 10:15
  • 23342

HTML我帮您打造微信小程序web可视化开发者工具

HTML我帮您打造微信小程序web可视化开发者工具是一款的可视化Web应用开发和运行平台。基于浏览器的集成开发环境,可视化和智能化的设计,能轻松完成身微信小程序和面向手机的移动应用开发;高效、稳定和可...
  • luck332
  • luck332
  • 2017年01月24日 16:18
  • 4085

SQL Server的 排序规则(collation)冲突和解决方案

什么是排序规则(collation)   关于SQL Server的排序规则,估计大家都不陌生,在创建数据库时我们经常要选择一种排序规则(conllation),一般我们会留意到每一种语言的排序规...

SQL Server的 排序规则(collation)冲突和解决方案

什么是排序规则(collation) 关于SQL Server的排序规则,估计大家都不陌生,在创建数据库时我们经常要选择一种排序规则(conllation),一般我们会留意到每一种语言的...

SQL Server 桌面引擎”(也叫 MSDE 2000)使用方法的简单介绍。

此文见于DBdashi博客,现转载如下:   1.概要           “SQL Server 桌面引擎”(也叫 MSDE 2000)没有自己的用户界面,因为它主要设计为在后台运行。用户通...

Sql Server 数据库恢复和日志文件的详细介绍

Sql Server 数据库恢复和日志文件的详细介绍 1、正确认识日志文件。 一个Sql Server数据库最少要包含一个mdf数据文件和一个ldf日志文件。mdf文件中包含了...

【能力提升】SQL Server常见问题介绍及快速解决建议

前言   本文旨在帮助SQL Server数据库的使用人员了解常见的问题,及快速解决这些问题。这些问题是数据库的常规管理问题,对于很多对数据库没有深入了解的朋友提供一个大概的常见问题框架。   下...

SQL Server数据库的数据类型详细介绍

SQL Server的数据类型介绍(一)   数据类弄是数据的一种属性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数...
  • xchyi
  • xchyi
  • 2011年10月03日 20:00
  • 176

SQL SERVER书签查找的介绍

 一、SQL SERVER书签查找定义   当查询优化器使用非聚集索引进行查找时,如果所选择的列或查询条件中的列只部分包含在使用的非聚集索引和聚集索引中时,就需要一个查找(lookup)来检...
  • whazhl
  • whazhl
  • 2014年09月04日 13:05
  • 461
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server Collation介绍
举报原因:
原因补充:

(最多只允许输入30个字)