当前位置:Gxlcms > 数据库问题 > SQL Server 字符集介绍及修改方法演示

SQL Server 字符集介绍及修改方法演示

时间:2021-07-01 10:21:17 帮助过:42人阅读

技术图片


正确认识SQL Server的字符集

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

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

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

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

--查询当前SQL Server服务器的排序规则

SELECT SERVERPROPERTY(N‘Collation‘)

(no column name)
--------------------------------------------------
Chinese_PRC_CI_AS

默认排序规则是 Chinese_PRC_CI_AS

当然你也可以使用 SSMS 查看服务器属性:
技术图片
技术图片
若在创建 Databases 时我们未指定排序规则,Databases 则会使用 Instances 的排序规则。如下所示是通过 sql 代码查询 SQL Server 中 Databases 的排序规则信息:

--通过目录视图 sys.databases 查询 Databases 的排序规则
SELECT name,collation_name FROM sys.databases WHERE name = N‘STUDR‘; //将STUDY替换为数据库名
name    collation_name
-------------------------------------------------
STUDY    Chinese_PRC_CI_AS

当然你也可以使用 SSMS 通过查看数据库属性得知数据库的字符集。

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

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

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

技术图片

选择合适的SQL Server字符集

在安装SQL Server时,你可能困惑应该选择哪种字符集,SQL Server或者Windows的。官方推荐使用SQL Server的字符集,而非Windows的字符集。原因是,SQL Server字符集是基于Windows衍生出来的,同时保证SQL Server版本间的兼容性。
可通过如下命令查看当前SQL Server支持的排序规则:

SELECT * from ::fn_helpcollations()

注意字符集的名字缩写与对应的的描述,如 CI 表示不区分大小写。
技术图片

错误使用SQL Server的字符集

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

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

所以,我们应尽可能的正确设置 SQL Server 排序规则:

  • 正确的设置 SQL Server 排序规则 ,保持 Instances、Databases、Columns 中 3 处排序规则一致,默认字符集是 Chinese_PRC_CI_AS 。
  • 尽可能使用 nvarchar 等 Unicode 类型,而非 varchar 类型 。

技术图片

修改字符集

安装 SQL Server 时,默认字符集是 Chinese_PRC_CI_AS ,如果安装完成后,我们想更改字符集,应该怎样操作呢?

注:再安装 SQL Server 时,可配置字符集。

修改数据库字符集

本例演示将数据库 STUDY 字符集从 Chinese_PRC_CI_AS 修改成 Latin1_General_100_CS_AS_SC。

修改方法一:

查看数据库属性,然后按照下图步骤进行修改:
技术图片

注意:需要断开其他链接,否则会报错如下:
技术图片

修改方法二:

在查询分析器中,输入如下命令:

ALTER DATABASE [STUDY] COLLATE Chinese_PRC_CI_AS

命令执行成功后,通过 sql 代码查询 SQL Server 中 Databases 的排序规则信息:

--通过目录视图 sys.databases 查询 Databases 的排序规则
SELECT name,collation_name FROM sys.databases WHERE name = N‘STUDR‘; //将STUDY替换为数据库名
name     collation_name
-------------------------------------------------
STUDY    Latin1_General_100_CS_AS_SC

修改成功!

修改服务器字符集

本例演示将服务器字符集从 Chinese_PRC_CI_AS 修改成 Latin1_General_100_CS_AS_SC。

服务器字符集的修改稍微复杂些

1)查看数据库安装路径

补充:(Windows Service) 各版本的默认安装路径
1 SQL Server 2008 C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
2 SQL Server 2012 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
3 SQL Server 2014 C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
4 SQL Server 2016 C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQLServer2016

本例使用的是SQL Server 2012,默认安装路径为 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

2)使用管理员权限打开DOS命令行
技术图片
3)切换进数据库安装文件目录

cd C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012 

技术图片
4)关闭SQL Server 后台服务

方式1:使用 Net stop 语句关闭 SQL Server 后台服务

Net stop STUDY

方式2:通过 Sql Server Configuration Manager 关闭 SQL Server 后台服务
技术图片
5)运行语句修改字符集

Setup /QUIET /ACTION=REBUILDDATABASE /instancename=STUDY /SQLSYSADMINACCOUNTS=administrator /sapwd=123456 /sqlcollation=Latin1_General_100_CS_AS_SC 

格式说明:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts / [ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName)
注意: accounts 用户使用电脑管理员用户,CollationName 为需要变更的排序格式 。

技术图片
6)待5)中命令执行完成后,执行如下命令检查服务器字符集是否修改成功

--查询当前SQL Server服务器的排序规则

SELECT SERVERPROPERTY(N‘Collation‘)

(no column name)
--------------------------------------------------
Latin1_General_100_CS_AS_SC

修改成功!

技术图片

 

参考文章:https://blog.csdn.net/wstoneh01/article/details/50588720

SQL Server 字符集介绍及修改方法演示

标签:记录   数据库安装   not   乱码   后台服务   t-sql   支持   uil   ons   

人气教程排行