《SQLServer修改服务器、数据库的排序规则》

目录

《SQLServer修改服务器、数据库的排序规则》

一、修改服务器的排序规则

（一）数据库服务器排序规则介绍

（二）数据库服务器排序规则的设置

2.1：查看数据库服务器排序规则

2.2：修改数据库服务器排序规则

二、修改数据库的排序规则

（一）界面操作方法

（二）报依赖错误解决办法

2.1：方法一（官方）

2.1：方法二（个人）

三、结束语言

---

一、修改服务器的排序规则

（一）数据库服务器排序规则介绍

1、注意：修改数据库服务器的排序规则相对于修改数据库的排序规则来说是非常麻烦的，因为数据库服务器的排序规则用作与 SQL Server的实例一起安装的所有系统数据库以及任何新创建的用户数据库的默认排序规则（简单的来说就是，数据库服务器排序规则是你在安装数据库的时候设置的）。

2、应仔细选择服务器级排序规则，因为它会影响：

（1）JOIN、ORDER BY 和其他比较文本数据的运算符的排序和比较规则。

（2）系统视图、系统函数和 TempDB 中的对象（例如临时表）中 CHAR、VARCHAR、NCHAR 和 NVARCHAR 列的排序规则。

（3）变量、游标和 GOTO 标签的名称。 如果服务器级排序规则区分大小写，则变量 @pi 和 @PI 被视为不同变量，如果服务器级排序规则不区分大小写，则将这两个变量视为相同变量。

（二）数据库服务器排序规则的设置

2.1：查看数据库服务器排序规则

1、步骤一：首先选择服务器，右键点击链接数据库服务器，点击属性，在服务器属性—>常规—>服务器排序规则。

2、查看服务器的排序规则。

2.2：修改数据库服务器排序规则

1、在查询分析器中，输入SELECT SERVERPROPERTY(‘Collation’)语句 ,获取服务器排序规则、以及当前服务器数据库安装版本。

--获取排序规则 SELECT SERVERPROPERTY('Collation') --查看安装版本  SELECT @@VERSION

1. 不同版本的数据库，安装路径不一样，请确认当前数据库默认安装版本及默认安装文件目录,请根据自己版本记录数据库默认安装目录。 
    

3、使用dos命令，进入当前数据库安装路径：

1. 然后关闭SQL Server 服务、运行语句

Setup/QUIET/ACTION=REBUILDDATABASE/instancename=MSSQLSERVER/SQLSYSADMINACCOUNTS=administrator/

sapwd=bwi@1234 /sqlcollation=Chinese_PRC_CS_AS

（3.1）格式说明:

（1）/QUIET或者/Q：指定在没有任何用户界面的情况下运行安装程序﹔

（2）/ACTION=REBUILDDATABASE：指定安装程序重新创建系统数据库﹔

（3）/instancename=MSSQLSERVER：实例名称默认用MSSQLSERVER;

（4）SQLSYSADMINACCOUNTS=administrator：指定要添加sysadmin固定服务器角色中的

windows组或单个账户，一般单个账户用Administrator

（5）sapwd=bwi@1234 ：指定SQL Server sa账户密码，混合模式的必选项

（6）sqlcollation=Chinese_PRC_CS_AS：指定服务器排序规则,未指定则为服务器当前排序规

（3.2）注意: accounts 用户使用电脑管理员用户，CollationName 为需要变更的排序格式  

（3.3）修改完成之后，重启数据库，进行2.1步骤，进行查看数据库服务器的排序规则是否修改完成（具体修改命令，参考下截图操作，因为这个是作者本人我自己修改成功过的案例。）

二、修改数据库的排序规则

（一）界面操作方法

以下实验将修改数据库db01的排序规则，将SQL_Latin1_General_CP1_CI_AS修改成Chinese_PRC_CI_AS。

1. 打开SSMS（SQL Server Management Studio）图形界面、选中数据库“右键-》属性”

2、找到“选项”然后选择需要的排序规则即可（注意：这可能存在依赖性、非单个锁定等问题、表的排序规则依赖于数据库的排序规则。不能修改）

3、如果您正在创建一个新数据库，则右键单击“数据库”，然后单击“新建数据库”。如果您不希望使用默认排序规则，则单击“选项”页，然后从“排序规则”下拉列表中选择某一排序规则。

或者，如果数据库已经存在，则右键单击所需数据库，然后单击“属性”。单击“选项”页，然后从“排序规则”下拉列表中选择某一排序规则。 

4、在完成后，单击 “确定”。

（二）报依赖错误解决办法

2.1：方法一（官方）

设置或更改数据库排序规则 - SQL Server | Microsoft Docs

1、【官方回答】修改完成之后情况

(1)对于已经存在的数据，此操作并不会导致立即重新排序。

(2)如果已经有对象依赖于数据库排序规则，则更改不成功。

2、【官方回答】修改列的排序规则（表的排序规则依赖于数据库的排序规则。不能修改。）

建议：将这个表的数据保存到另一个临时表，重建这个表，然后从临时表将数据导入到新表。

可以通过SSMS修改某一列的排序规则，但是，这项操作实际上还是删除这个表以及相关的约束、触发器，然后重建这个表、约束、索引、触发器。

2.1：方法二（个人）

1、【个人解决办法已实现】注意：在正常修改的情况下，无法修改成功，存在依赖性、非单个用户访问限制的情况，按照一下操作进行：首先第一步设置访问限制（第二种情况使用T-SQL语句下面第一行）

--(1)第一个删除所有用户锁、第二个开启用户锁 ALTER DATABASE ORAPS_SCSS_COMPANY_YiPu SET SINGLE_USER; ALTER DATABASE ORAPS_SCSS_COMPANY_YiPu SET MULTI_USER;

2、第二步：则将存在依赖的表、试图、函数先解决掉（怎么查看是否存在依赖关系、执行下图SQL语句，存在依赖关系的话，会直接报错，并提示表还是试图等）
3、怎么操作勒？最好的办法是先将（这里以表为例），先将表生成一个脚本，然后删除掉，这样就解决了依赖关系的问题，当修改完排序规则之后，然后再将删除的表补回来就行，执行创建表的SQL语句（如下图生成脚本）。

4、点三步：最后以上两边解决了依赖关系、访问限制的情况下，接下来就是使用T-SQL语句修改排序规则的操作了，具体操作如下图：

--(1)第一个删除所有用户锁、第二个开启用户锁 ALTER DATABASE ORAPS_SCSS_COMPANY_YiPu SET SINGLE_USER; ALTER DATABASE ORAPS_SCSS_COMPANY_YiPu SET MULTI_USER; --(2)修改数据库排序规则 USE ORAPS_SCSS_COMPANY_YiPu;   GO   ALTER DATABASE ORAPS_SCSS_COMPANY_YiPu   COLLATE Chinese_PRC_CI_AS ;   GO   --(3)查询所有数据库排序规则   SELECT name, collation_name   FROM sys.databases   WHERE name = N'Chinese_PRC_CI_Al';   GO

三、结束语言

        最后如果在线的读者你喜欢的话可以留下一个小小的“笔芯”，同时在本文中有什么问题，或者有错误的地方，也可以留言告诉【大大大钢琴（作者）】，我会第一时间纠正。

 学习和成长本就是这样，痛并快乐着，而又是一个漫长的过程，因为有你们的分享和大家的互相监督学习，才能够不断的进步和成长。

目录

1 数据库字符集选择

1.1 utf8和utfmb4区别

2 排序说明

3 大小写报错

3.1 报错现象

3.2 处理过程

---

1 数据库字符集选择

1.1 utf8和utfmb4区别

一般选择utf8.下面介绍一下utf8与utfmb4的区别

utf8mb4兼容utf8，且比utf8能表示更多的字符。至于什么时候用，看你的做什么项目了，unicode编码区从1 ～ 126就属于传统utf8区，当然utf8mb4也兼容这个区，126行以下就是utf8mb4扩充区，什么时候需要存储那些字符，才用utf8mb4,否则会浪费空间。

2 排序说明

排序一般分为两种：utf_bin和utf_general_ci

bin 是二进制, a 和 A 会别区别对待.

例如你运行:

select * from table where a = 'a'；

那么在utf8_bin中你就找不到 a = 'A' 的那一行, 而 utf8_general_ci 则可以.

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果

utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

utf8_general_ci校对速度快，但准确度稍差。（准确度够用，一般建库选择这个）

utf8_unicode_ci准确度高，但校对速度稍慢。

所以一般选择如下：

3 大小写报错

3.1 报错现象

有时候发现在自己本机Windows上开发和测试过程中一直没有问题，临近项目交付的时候，部署到Linux服务器上后，发现有报错，日志信息大概是：

MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist

3.2 处理过程

在本机Window和Linux分布查看如下：

mysql> show variables like ‘%case%’;

windows下如图所示 

linux下如图 

在Linux上数据库中显示的student，不过s是小写；查看代码发现代码中还真把表名写成Student,就一个s写成大写S了。
问题找到了，原来是不小心写SQL的时候没有写对表名，改一下表名就搞定了，功能也一切正常了

从上面的结果已经可以看出不同了
当 lower_case_table_names 为 0 时表示区分大小写，为 1 时表示不区分大小写
在Windows上，默认值为1；在macOS上，默认值为2；在Linux上不支持值2;服务器强制该值为0

• 0 --大小写敏感。（Unix，Linux默认）
• 1–大小写不敏感。（Windows默认）
• 2 --大小写不敏感（macOS默认）

并且官网也提示说：如果在数据目录驻留在不区分大小写的文件系统（例如Windows或macOS）上的系统上运行MySQL，则不应将lower_case_table_names设置为0。

在window10环境尝试设置lower_case_table_names为0的时候，MySQL的服务怎么也启动不能，启动服务报错，因为windows系统对大小写不敏感

而Linux则是区分大小写的，因此，建议在开发测试环境下就严格控制代码大小写敏感，
提高代码的兼容和严谨

注意：如果报错Variable 'lower_case_table_names' is a read only variable，那么需要在配置文件中修改，然后重启mysql服务