sql中的collation干什么用

一、sql中的collation干什么用

关于SQL Server的排序规则，估计大家都不陌生，在创建数据库时我们经常要选择一种排序规则（conllation），一般我们会留意到每一种语言的排序规则都有许多种，比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多

这些排序规则有什么作用呢？让我们先来看看MS官方的解释：

排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则具有下面的特征：

比如在SQL Server 2005中，排序规则名称由两部份构成，比如 Chinese_PRC_CI_AI_WS

前半部份是指本排序规则所支持的字符集，如Chinese_PRC指针对大陆简体字UNICODE的排序规则。

指定使用向后兼容的二进制排序顺序。

_BIN2指定使用 SQL Server 2005中引入的码位比较语义的二进制排序顺序。

_CI(CS)是否区分大小写，CI不区分，CS区分

_AI(AS)是否区分重音，AI不区分，AS区分

_KI(KS)是否区分假名类型，KI不区分，KS区分

_WI(WS)是否区分全半角，WI不区分，WS区分

既然排序规则如此复杂，那么应用了不同排序规则的列之间默认情况下便不能进行Union、Join、Like等equal操作了，于是便有了排序规则（collation）冲突。

我们知道，SQL Server从2000开始，便支持多个排序规则。SQL Server 2000的数据库可使用除默认排序规则以外的其他排序规则。此外，SQL Server 2000还支持为列专门制定排序规则。

这样一来，我们在写跨表、跨数据库、跨服务器操作的T-SQL时，如果equal的字段排序规则不同，便会发生排序规则冲突。

比如我们先见两个结构相同的表，但字段的排序规则不同：

TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN

TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS

SELECT* from TagsTableA A INNER JOIN TagsTableB B on A.TagName= B.TagName

无法解决 equal to操作中"Chinese_PRC_BIN"和"Chinese_PRC_CI_AS"之间的排序规则冲突。

我们知道，SQL Server的临时表是保存在Tempdb数据库中的。而使用临时表的数据库与临时表的排序规则（conllation）不一定相同。所以，当Tempdb的排序规则与当前使用临时表的数据库排序规则不同时，便会出现排序规则冲突。

一般来说，我们在创建临时表时可能不会注意到排序规则，从而留下排序规则冲突的隐患。

比如Openlab V4.0的Blog模块中的一个存储过程，便有着这种隐患：

/******对?象ó: StoredProcedure [blogs].[up_CreateGetTagIds]脚本日期: 01/20/2010 19:10:32******/

--=============================================

-- Create date:<2010-01-06>

-- Description:<Make sure all the tag EXISTS in DB, and then get their ids.>

-- 2. Insert TagNames into Temp Table.

-- 3. Add new Tags to [Categories] from query Temp Table.

-- 4. Batch Get All Tag Ids from [Categories].

-- 5. Clear and drop Temp Table.

--=============================================

ALTER PROCEDURE [blogs].[up_CreateGetTagIds]

/******************************* SET CONFIG*************************************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/******************************* DECLARE VARIABLE*************************************************/

/********************************BEGIN TRANSATION**********************************************/

-- 2. Insert TagNames into Temp Table.

TG.Tags.value('@i','NVARCHAR(64)') AS TagName

@TagNames.nodes('/ts/t') TG(Tags)

-- 3. Add new Tags to [Categories] from query Temp Table.

2,-- CategoryType, 2 as Post Tag.

TT.TagName,-- Slug, use CategoryName as default.

LOWER(TT.TagName),-- LoweredSlug, use LoweredCategoryName as default.

'',-- Description, Empty as default.

0,-- TotalEntities, 0 as default.

1,-- SortOrder of PostTags can always be 1.

[Categories] C WITH( UPDLOCK, HOLDLOCK)

AND [CategoryType]= 2-- Post Tag.

-- 4. Batch Get All Tag Ids from [Categories].

C.[LoweredCategoryName]= LOWER( TT.TagName)

AND C.[CategoryType]= 2-- Post Tag.

AND C.[State]= 1-- 1 as Normal status.

-- 5. Clear and drop Temp Table.

知道了什么是排序规则冲突，我们接下来分析冲突的解决方案，以数据库级别的排序规则为例，一般来说，解决方案有下面几种

把SQL实例删了重建——大多数情况下等于没说-_-|||

修改数据库的排序规则——参考阿牛兄的这篇文章

在T-SQL中使用COLLATE DATABASE_DEFAULT来解决冲突——接下来主要讨论这个

Collate XXX操作可以用在字段定义或使用时，它会将字段定义或转换成XXX的排序规则格式。而Collate Database_Default则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。

比如在下面的代码中便使用了Collate Database_Default来解决字段在equal操作中的排序规则冲突：

Insert into Security.Report(Name)

Select C.Path From SSRS.Catalog C

Where C.Path Collate Database_Default Like@ReportPath+'/%'

And C.Path Collate Database_Default Not In(Select Name From Security.Report R)

当然，在创建临时表时若对字段定义加上Collate Database_Default，也可以方便地解决潜在的排序规则冲突，比如上一节中提到的存储过程，只要做如下修改即可。

TagName NVARCHAR(64) COLLATE DATABASE_DEFAULT

对于专业的SQLer来说，排序规则的应用场景还有很多，例如利用排序规则特点计算汉字笔划和取得拼音首字母等等，更多信息，请查阅MSDN文档：(en-us,SQL.80).aspx

二、mysql数据库中的collation如何修改

在数据库插入数据时，varchar类型的字段插入中文数据时报错。

通过 show full columns from user_bean;语句查看字段的collation属性，发现字段的collation属性值是 latin1_swedish_ci，说明字段默认为英语。未对汉语进行设置，所以当输入汉语时，mysql会报错为“字符串的值不正确”。

1、编辑mysql的配置文件/etc/my.cnf，vi/etc/my.cnf

2、重启Mysql服务 systemctl restart mysqld

向数据库插入含中文的数据，成功！

注意：上面步骤可以解决以后创建表时字段的collation属性的问题，但前面已经创建的表字段的collation属性值并不会发生变化。

如果要改变之前已经创建好的表，怎么办？

方法1：对原来的表进行修改，可以通过类似语句

alter table user_bean change address address varchar(255) character set utf8 collate utf8_general_ci not null;

方法2：删除原来的表，重新再建。

三、sql数据库COLLATE

1、数据虽然完全相同，但是的两个数据库实例的COLLATE设置不同，两者不兼容。这种情况通常出现在与临时表进行连接的时候。临时表创建在tempdb数据库里，它默认是继承了SQL Server实例的COLLATE设置。如果你的用户数据库是从另外一台服务器附加上来的话，很可能是另一中COLLATE设置。两者不兼容就会出现这种错误。

2、解决办法：可以在创建临时表时，为文本字段指定 COLLATE DATABASE_DEFAULT，从而明确指定从当前用户数据库继承 COLLATE设置。