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

转载 2018年04月17日 09:04:25

正确认识SQL Server的字符集


SQL Server作为一个国际化产品,支持多语言环境。在SQL Server中,字符集被称为排序规则(即Collation)。排序规则不仅影响记录行的sort顺序,还影响中文显示是否乱码等。在SQL Server中,排序规则可在3处地方设置:

服务器级别 =>instances
db库级别 =>databases
表列级别 =>columns

SQL Server Collation继承方式
上图所示为SQL Server默认情况下对Collation继承的方式。

即在SQL Server软件安装时我们可设置服务器级别的排序规则,也就是instances的排序规则。如下所示是使用T-SQL查询当前instances的排序规则信息:

--查询当前SQL Server服务器的排序规则
SELECT SERVERPROPERTY(N'Collation')

Chinese_PRC_CI_AS

当然你也可以使用SSMS查看服务器属性:
这里写图片描述

若在创建databases时我们未指定排序规则,databases则会使用instances的排序规则。如下所示是通过sql代码查询或修改SQL Server中databases的排序规则信息:

--在创建database时指定排序规则:SQL_Latin1_General_CP1_CI_AS
USE master;
GO

CREATE DATABASE mydb
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

--通过目录视图sys.databases查询databases的排序规则
SELECT
    name,
    collation_name
FROM sys.databases
WHERE name = N'mydb';

mydb    SQL_Latin1_General_CP1_CI_AS

--修改现有databases的排序规则
ALTER DATABASE mydb
COLLATE Chinese_PRC_CI_AS

而表中的列(columns)默认情况是继承databases的排序规则(除非在创建表时对列的排序规则进行指定),我们可通过目录视图sys.columns查询表中columns的排序规则信息。

这里需注意:SQL Server的排序规则只影响字符型的列,如char, varchar, text, nchar, nvarchar, ntext,因此目录视图sys.columns中非字符型的排序规则显示为NULL

--注意:只有字符型的列才会显示排序规则信息,非字符型的显示为NULL
SELECT name, collation_name
FROM sys.columns
where collation_name is NOT NULL

选择合适的SQL Server字符集


在安装SQL Server时,你可能困惑应该选择哪种字符集,SQL Server或者Windows的。官方推荐使用SQL Server的字符集,而非Windows的字符集。原因是,SQL Server字符集是基于Windows衍生出来的,同时保证SQL Server版本间的兼容性, 如SQL Server 2014可使用的字符集比2008多
这里写图片描述

--查看当前SQL Server支持的排序规则
SELECT * from ::fn_helpcollations()

注意字符集的名字缩写与对应的的描述,如CI表示不区分大小写、
支持的排序规则

错误使用SQL Server的字符集


我们不难理解:只需保持SQL Server中3处字符集设置的地方:instances、databases、columns设置一致即是正确的使用方式。

那么当SQL Server中instances与databases对排序规则设置不一致时,将直接导致临时表#或##不能正常使用(临时表的列默认继承tempdb的排序规则,而tempdb则继承了instances的排序规则)。

/*
注意:
 这里mydb的字符集是SQL_Latin1_General_CP1_CI_AS,
 而instance的字符集是Chinese_PRC_CI_AS
*/
USE mydb;
GO

--在mydb中创建一张表collation_test
CREATE TABLE collation_test (hyper varchar(10));
GO

--创建临时表collation_temp
CREATE TABLE #collation_temp (hyper varchar(10));
GO

连接查询上述临时表的内容时,将出现如下报错信息:

--查询报错
SELECT  *
FROM collation_test l
LEFT JOIN #collation_temp c
ON l.hyper = c.hyper;

Msg 468, Level 16, State 9, Line 4
无法解决 equal to 运算中 “Chinese_PRC_CI_AS” 和 “SQL_Latin1_General_CP1_CI_AS” 之间的排序规则冲突。

其根本原因是由于表collation_test与临时表#collation_temp中列的排序规则不一致。虽然可以通过以下2种方式继续使用临时表,但并不推荐。如下通过指定select表中列的排序规则,继续使用上述两张表。

--方式1:
--注意指定表collation_test使用排序规则COLLATE Chinese_PRC_CI_AS
SELECT  *
FROM collation_test l 
LEFT JOIN #collation_temp c 
ON l.hyper COLLATE Chinese_PRC_CI_AS = c.hyper


第二种解决方法则是在创建表时指定列的排序规则

--方式2
USE mydb;
GO

--注意指定了列的排序规则:COLLATE Chinese_PRC_CI_AS
CREATE TABLE collation_Wang
(hyper varchar(10) COLLATE Chinese_PRC_CI_AS);

--保持列的排序规则一致即可正常使用临时表#collation_temp
SELECT  *
FROM collation_wang w
LEFT JOIN #collation_temp c 
ON w.hyper = c.hyper

同时instances的排序规则设置会影响SQL Server数据的导入导出功能。

通常我们遇到的另一个问题是:通过SSMS(即SQL Server Management Studio)插入(insert)的中文,在查询时显示乱码(即问号?)。

--在上述表collation_test插入中文
INSERT INTO collation_test VALUES ('东') 

--查询表collation_test的记录
select * from collation_test

查询显示乱码:
这里写图片描述

这当然是由于表collation_test上hyper列的字符集设置不正确所导致的。但若你有幸在表上使用了nvarchar等类型,那么当出现上述乱码时,也许你还可以使用如下临时方式补救:

/* 
注意:
    数据库mydb依旧是使用错误的排序规则:SQL_Latin1_General_CP1_CI_AS,
    但是表collation_nvarchar使用了nvarchar类型,而非varchar
 */
USE mydb;
GO
CREATE TABLE collation_nvarchar (hyper nvarchar(10));
GO

--临时处理方式
INSERT INTO collation_nvarchar VALUES (N'东'); 

--错误插入方式
INSERT INTO collation_nvarchar VALUES ('东'); 

--查询表collation_nvarchar的记录
select * from  collation_nvarchar

这里写图片描述

综上述,我们应尽可能的正确设置SQL Server排序规则:
1. 正确的设置SQL Server排序规则 ,保持instances、databases、columns中3处排序规则一致,推荐使用Chinese_PRC_CI_AS
2. 尽可能使用nvarchar等Unicode类型,而非varchar类型

参考资料


Setting and Changing the Database Collation
https://msdn.microsoft.com/en-us/library/ms175835(v=sql.105).aspx

sys.columns (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms176106(v=sql.120).aspx

Collation and International Terminology
https://msdn.microsoft.com/en-us/library/ms143726(v=sql.105).aspx

版权声明:本文为博主HyperWang原创文章,使用”CC 署名-非商业性使用-禁止演绎 4.0 国际”进行分享。 https://blog.csdn.net/wstoneh01/article/details/50588720

WordPress网站使用和设置(学会网站应用)

-
  • 1970年01月01日 08:00

SQL中的Collation是个什么鬼?

一、说文解字,学习一下Collation的字面意思:排序。 (https://en.wikipedia.org/wiki/Collation) 二、SQL中的Collation,描述了如何对查询出...
  • maray
  • maray
  • 2015-06-15 17:31:41
  • 7317

修改sqlserver数据库的排序规则(collation)

ALTER   DATABASE   database_name   SET   SINGLE_USER   WITH   ROLLBACK   IMMEDIATE --WITH   ROLLB...
  • yincheng886337
  • yincheng886337
  • 2013-09-25 15:23:18
  • 249

修改MySql中数据表和字段的字符集和排序规则

由于数据库中的数据表和表字段的字符集和排序规则不统一,找了很多帖子,最后发现如下脚本很好用。 用法儿是:先执行如下脚本生成修改数据表和表字段的脚本,然后再执行这些生成的脚本。 1. 修改指...
  • li_xiao_ming
  • li_xiao_ming
  • 2016-08-08 15:34:48
  • 6331

SQL Server2016数据库 中文显示为??,修改排序方式为Chinese_PRC_CI_AS

之前在建立数据库时候,没有注意到排序方式的问题,导致后来insert记录的时候,中文全部显示为???(问号)。使用SMMS查看数据库属性,发现默认排序方式为SQL_Latin1_General_CP1...
  • mine_soul
  • mine_soul
  • 2016-12-24 10:04:30
  • 2852

[易飞]修改数据库的排序规则

SQL Server 2008安装好后,发现服务器排序规则不对,又不想重装SQL Server。  假设当前默认实例的服务器排序规则是:Chinese_PRC_CI_AS,要修改成Chinese_PR...
  • david_520042
  • david_520042
  • 2015-03-26 13:44:20
  • 1353

sqlserver中的数据库排序规则(Collation)指定错误使中文变成问号

安装sql2005后,遇到中文提交后在数据库中都变成?号(不是乱码)的问题经查证是数据库排序规则(Collation)指定错误造成的,要正确的将中文写入NVARCHAR字段,要么在SQL语句中文值前加...
  • Crazy_Dreamer
  • Crazy_Dreamer
  • 2006-09-12 11:33:00
  • 2409

MySql:charset和collation的设置

MySql:charset和collation的设置   charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和连接级    www.2cto.com   ...
  • haiross
  • haiross
  • 2016-04-28 13:30:25
  • 4457

SQL 数据库排序规则修改和 数据库表列排序规则修改

---数据库排序规则修改 ALTER DATABASE SRMain  SET SINGLE_USER WITH ROLLBACK IMMEDIATE Go ALTER DATABASE SRMain...
  • lsk_jd
  • lsk_jd
  • 2013-07-09 14:51:19
  • 1857

SQL Server Collation介绍

Question: What is Collation? How it will affect SQL Server database, and server? Answer: 字符的存储...
  • avon520
  • avon520
  • 2015-11-18 13:04:30
  • 1183
收藏助手
不良信息举报
您举报文章:正确设置与使用SQL Server的字符集(Collation,即排序规则)
举报原因:
原因补充:

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