SQL Server 字符集介绍及修改方法演示
正确认识SQL Server的字符集SQL Server 作为一个国际化产品,支持多语言环境。在 SQL Server 中,字符集被称为排序规则(即Collation)。排序规则不仅影响记录行的 sort 顺序,还影响中文显示是否乱码等。在 SQL Server 中,排序规则可在 3 处地方设置:服务器级别 =>instancesdb库级别 =>databases表列级别 =>c
正确认识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
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)