sql server中字符集和排序规则到底什么关系

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

--SQLSERVER 中的排序规则 服务器》数据库》表列


--------------------------------- 排序规则简介 -------------------------------

-- 什么叫排序规则呢? MS是这样描述的: " 在Microsoft SQL Server 2000 中,

-- 字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存

-- 储和比较字符所使用的规则。 "

--   在查询分析器内执行下面语句,可以得到 SQL  SERVER 支持的所有排序规则。

--

--     select * from ::fn_helpcollations()

--

-- 排序规则名称由两部份构成,前半部份是指本排序规则所支持的字符集。

-- 如:

--   Chinese_PRC_CS_AI_WS

-- 前半部份:指 UNICODE字符集, Chinese_PRC_ 指针对大陆简体字 UNICODE的排序规则。

-- 排序规则的后半部份即后缀含义:

--   _BIN 二进制排序

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

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

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

--_WI(WS) 是否区分宽度 WI不区分, WS 区分 

--

-- 区分大小写 :如果想让比较将大写字母和小写字母视为不等,请选择该选项。

-- 区分重音: 如果想让比较将重音和非重音字母视为不等,请选择该选项。如果选择该选项,

-- 比较还将重音不同的字母视为不等。

-- 区分假名: 如果想让比较将片假名和平假名日语音节视为不等,请选择该选项。

-- 区分宽度: 如果想让比较将半角字符和全角字符视为不等,请选择该选项

-------------------------------------------------------------------------------------

sp_helpsort

SELECT SERVERPROPERTY ('Collation' )

--------------------------------------------

--2. 更改服务器排序规则

-- 更改SQL Server 2005 实例的默认排序规则的操作可能会比较复杂,包括以下步骤:

 

-- 确保具有重新创建用户数据库及这些数据库中的所有对象所需的全部信息或脚本。

-- 使用工具(例如大容量复制)导出所有数据。

-- 删除所有用户数据库。

-- 重新生成在 setup 命令的 SQLCOLLATION 属性中指定新的排序规则的 master 数据库。例如:

-- 复制代码

start /wait setup . exe / qb INSTANCENAME =MSSQLSERVER REINSTALL = SQL_Engine REBUILDDATABASE= 1 SAPWD= test SQLCOLLATION =SQL_Latin1_General_CP1_CI_AI

-- 有关重新生成 master 数据库的详细信息,请参阅如何重新生成 SQL Server 2005 的Master 数据库。

-- 创建所有数据库及这些数据库中的所有对象。

-- 导入所有数据。

-- 注意:

-- 可以为创建的每个新数据库指定默认排序规则,而不更改 SQL Server 2005 实例的默认排序规则。

-----------------------------------------------------------------------------------

--3. 设置和更改数据库排序规则

-- 创建新数据库时,可以使用下列内容之一指定排序规则:

--CREATE DATABASE 语句的COLLATE 子句。

--SQL Server Management Studio.

--SQL 管理对象(SMO) 中的 Database.Collation 属性。

-- 如果未指定排序规则,则使用服务器排序规则。

-- 可以使用ALTER DATABASE 语句的 COLLATE 子句来更改在用户数据库中创建的任何新对象的排序规则。使用此语句不能更改任何现有用户定义的表中列的排序规则。使用 ALTER TABLE 的COLLATE 子句可以更改这些列的排序规则。

-- 更改数据库排序规则时,需要更改下列内容:

-- 数据库的默认排序规则,这一新的默认排序规则将应用于数据库中后续创建的所有列、用户定义的数据类型、变量和参数。根据数据库中定义的对象解析 SQL 语句中指定的对象标识符时,也使用新的默认排序规则。

-- 将系统表中的任何 char、 varchar 、text 、 nchar、 nvarchar 或ntext 列更改为使用新的排序规则。

-- 将存储过程和用户定义函数的所有现有 char 、varchar 、 text、 nchar 、nvarchar 或 ntext 参数和标量返回值更改为使用新的排序规则。

-- 将char 、 varchar、 text 、nchar 、 nvarchar 或 ntext 系统数据类型和基于这些系统数据类型的所有用户定义的数据类型更改为使用新的默认排序规则。

 

--SQL code :

-------------------------- 数据库 ---------------------------------------

--1. 将数据库的字符集修改为:

ALTER DATABASE [pratice] COLLATE Chinese_PRC_CI_AS

 

--2. 为数据库指定排序规则

CREATE DATABASE db COLLATE Chinese_PRC_CI_AS

GO

 

ALTER DATABASE db COLLATE Chinese_PRC_BIN

GO

ALTER    DATABASE [pratice]  COLLATE   Chinese_PRC_CS_AS  -- 区分大小写

 

ALTER    DATABASE    [pratice] COLLATE  Chinese_PRC_CI_AS -- 不区分大小写

--------------------------- 表中的列 --------------------------------------

-- 为表中的列指定排序规则

CREATE TABLE tb

    (

      col1 VARCHAR (10 ) ,

      col2 VARCHAR (10 ) COLLATE Chinese_PRC_CI_AS

    )

GO

ALTER TABLE tb ADD col3 VARCHAR (10 ) COLLATE Chinese_PRC_BIN

GO

ALTER TABLE tb ALTER COLUMN col2 VARCHAR ( 10) COLLATE Latin1_General_CS_AS_KS_WS

GO

ALTER    TABLE    tb  ALTER   COLUMN    colname   NVARCHAR ( 100)    COLLATE   Chinese_PRC_CI_AS  -- 不区分大小写

ALTER    TABLE    tb  ALTER   COLUMN    colname   NVARCHAR ( 100)    COLLATE   Chinese_PRC_CS_AS  -- 区分大小写

---------------------------------------------------------------------

--3. 为字符变量和参数应用排序规则

DECLARE @a VARCHAR ( 10) ,

    @b VARCHAR (10 )

SELECT   @a = 'a' ,

        @b = 'A'

 

-- 使用排序规则 Chinese_PRC_CI_AS

SELECT   CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' --Chinese_PRC_CI_AS 不区分大小写

             ELSE '@a<>@b'

        END

-- 结果:@a=@b

 

-- 使用排序规则 Chinese_PRC_BIN

SELECT   CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b'   --区分大小写

             ELSE '@a<>@b'

        END

-- 结果:@a<>@b

---------------------------SQL2000 适用 -----------------------------------------------------

-- 方法一安装 SQL2000时选择区分大小写或安装完以后重建 mastar ,选择区分大小

--C:\Program   Files\Microsoft   SQL   Server\80\Tools\Binn\rebuildm.exe

 

-- 方法二sql   server   8.0 以上的版本才可以,及其以下不支持

ALTER    DATABASE   [pratice]   COLLATE   Chinese_PRC_CS_AS

-- 修改排序规则,改成大小写敏感的排序规则

-- 如果只修改一个表,用 alter   table语句

-- 如果修改一个库的默认排序规则,用 alter   datebase 语句

-- 如果修改整个服务器的默认排序规则,用 Rebuildm.exe 重建master 库

-- 指定排序规则就可以了

-------------------------------------------------------------------

-- 注意需要使用 NVARCHAR()数据类型 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

-- 因为排序规则而出错的例子

--USE [tempdb]

--GO

--DROP TABLE #t1

--DROP TABLE #t2

CREATE TABLE #t1

    (

      value INT ,

      name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS

    )

CREATE TABLE #t2

    (

      value INT ,

      name NVARCHAR ( 20) COLLATE Chinese_PRC_CI_AI_WS

    )

INSERT   #t1

        SELECT   1 ,

                ' 中'

        UNION ALL

        SELECT   2 ,

                ' 国'

        UNION ALL

        SELECT   3 ,

                ' 人'

        UNION ALL

        SELECT   4 ,

                ' 阿'

INSERT   #t2

        SELECT   1 ,

                ' 亲'

        UNION ALL

        SELECT   2 ,

                ' 国'

        UNION ALL

        SELECT   3 ,

                ' 好'

        UNION ALL

        SELECT   4 ,

                ' 阿'

SELECT   * FROM    #t1 A INNER JOIN #t2 B ON A . name = B .name

-- 解决方法

SELECT   * FROM    #t1 A INNER JOIN #t2 B ON A . name = B .name COLLATE Chinese_PRC_CI_AI_WS

---------------------------------------------------------------------------------------

-- 例: 让表 NAME列的内容按拼音排序:

CREATE TABLE #t1

    (

      value INT ,

      name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS

    )

   

    INSERT   #t1

        SELECT   1 ,

                ' 中'

        UNION ALL

        SELECT   2 ,

                ' 国'

        UNION ALL

        SELECT   3 ,

                ' 人'

        UNION ALL

        SELECT   4 ,

                ' 阿'

SELECT   * FROM    #t1 ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS

/* 结果:

id name

----------- --------------------

4 阿

2 国

3 人

1 中

*/

-------------------------------------------------------------------------

-- 例:让表NAME 列的内容按姓氏笔划排序:

create table #t (id int ,name NVARCHAR( 20 ))

insert #t select 1 ,' 三 '

union all select 2 ,' 乙 '

union all select 3 ,' 二 '

union all select 4 ,' 一 '

union all select 5 ,' 十 '

select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS

drop table #t

/* 结果:

id name

----------- --------------------

4 一

2 乙

3 二

5 十

1 三

*/

--------------------------- 计算汉字的笔划 --------------------------------------------

--   上面所有准备过程,只是为了写下面这个函数,这个函数撇开上面建的所有临时表和固

-- 定表,为了通用和代码转移方便,把表 tab_hzbh 的内容写在语句内,然后计算用户输入一串

-- 汉字的总笔划:

USE [tempdb]

GO

create function fun_getbh (@str nvarchar (4000 ))

returns int

as

begin

declare @word nchar ( 1), @n int

set @n= 0

while len ( @str)> 0

begin

set @word=left( @str ,1 )

-- 如果非汉字,笔划当计

set @n= @n +(case when unicode (@word ) between 19968 and 19968 + 20901

then ( select top 1 id from (

select 1 as id ,N ' 亅' as word

union all select 2 ,N ' 阝'

union all select 3 ,N ' 马'

union all select 4 ,N ' 风'

union all select 5 ,N ' 龙'

union all select 6 ,N ' 齐'

union all select 7 ,N ' 龟'

union all select 8 ,N ' 齿'

union all select 9 ,N ' 鸩'

union all select 10 ,N ' 龀'

union all select 11 ,N ' 龛'

union all select 12 ,N ' 龂'

union all select 13 ,N ' 龆'

union all select 14 ,N ' 龈'

union all select 15 ,N ' 龊'

union all select 16 ,N ' 龍'

union all select 17 ,N ' 龠'

union all select 18 ,N ' 龎'

union all select 19 ,N ' 龐'

union all select 20 ,N ' 龑'

union all select 21 ,N ' 龡'

union all select 22 ,N ' 龢'

union all select 23 ,N ' 龝'

union all select 24 ,N ' 齹'

union all select 25 ,N ' 龣'

union all select 26 ,N ' 龥'

union all select 27 ,N ' 齈'

union all select 28 ,N ' 龞'

union all select 29 ,N ' 麷'

union all select 30 ,N ' 鸞'

union all select 31 ,N ' 麣'

union all select 32 ,N ' 龖'

union all select 33 ,N ' 龗'

union all select 35 ,N ' 齾'

union all select 36 ,N ' 齉'

union all select 39 ,N ' 靐'

union all select 64 ,N ' 龘'

) T

where word>= @word collate Chinese_PRC_Stroke_CS_AS_KS_WS

order by id ASC) else 0 end )

set @str=right( @str ,len ( @str)- 1 )

end

return @n

END

select dbo. fun_getbh (' 中华 '), dbo .fun_getbh ( '中華人民共和國 ' )

-- 简繁体都行

-------------------------------------------------------------------------

-- 用排序规则的特性得到汉字拼音首字母

-- 用得到笔划总数相同的方法,我们也可以写出求汉字拼音首字母的函数。如下:

USE [tempdb]

GO

create function fun_getPY (@str nvarchar (4000 ))

returns nvarchar ( 4000)

as

begin

declare @word nchar ( 1), @PY nvarchar( 4000 )

set @PY= ''

while len ( @str)> 0

begin

set @word=left( @str ,1 )

-- 如果非汉字字符,返回原字符

set @PY= @PY +(case when unicode (@word ) between 19968 and 19968 + 20901

then ( select top 1 PY from (

select 'A' as PY , N' 驁 ' as word

union all select 'B', N '簿 '

union all select 'C', N '錯 '

union all select 'D', N '鵽 '

union all select 'E', N '樲 '

union all select 'F', N '鰒 '

union all select 'G', N '腂 '

union all select 'H', N '夻 '

union all select 'J', N '攈 '

union all select 'K', N '穒 '

union all select 'L', N '鱳 '

union all select 'M', N '旀 '

union all select 'N', N '桛 '

union all select 'O', N '漚 '

union all select 'P', N '曝 '

union all select 'Q', N '囕 '

union all select 'R', N '鶸 '

union all select 'S', N '蜶 '

union all select 'T', N '籜 '

union all select 'W', N '鶩 '

union all select 'X', N '鑂 '

union all select 'Y', N '韻 '

union all select 'Z', N '咗 '

) T

where word>= @word collate Chinese_PRC_CS_AS_KS_WS

order by PY ASC) else @word end )

set @str=right( @str ,len ( @str)- 1 )

end

return @PY

end

select dbo. fun_getPY (' 中华 '), dbo .fun_getPY ( '中華人民共和國 ' )

-- 结果都为: ZHRMGHG

-------------------------------------------------------------------------

-- 先用SQLSERVER 方法得到所有汉字,不用字典,我们简单利用 SQL 语句就可以得到:

select top 20902 code =identity ( int, 19968 ,1 ) into #t from syscolumns a, syscolumns b

select code, nchar (code ) as CNWord from #t

-- 然后,我们用 Select语句,让它按笔划排序。

select code, nchar (code ) as CNWord

from #t

order by nchar( code ) collate Chinese_PRC_Stroke_CS_AS_KS_WS, code 



本文转自pizibaidu 51CTO博客,原文链接:http://blog.51cto.com/pizibaidu/1627605,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
5月前
|
SQL 数据库管理
第二章:基础查询与排序---SQL学习笔记
第二章:基础查询与排序---SQL学习笔记
76 0
|
5月前
|
SQL 存储
SQL组内排序
为什么要用ORDER BY来做,因为是这样的,由于采用的多线程,各个线程触发时间十分相近,但是我们需要对每一个项目进行分组,所以在此处,我们做了一个唯一标识IDENTIFICATION,每个项目每次执行时记录的6条日志里都会存储这个唯一标识。
|
4月前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
4月前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
51 0
|
5月前
|
SQL 机器学习/深度学习 算法
SQL SERVER ANALYSIS SERVICES决策树、聚类、关联规则挖掘分析电商购物网站的用户行为数据
SQL SERVER ANALYSIS SERVICES决策树、聚类、关联规则挖掘分析电商购物网站的用户行为数据
|
5月前
|
SQL
SQL语句两个字段或多个字段同时order by 排序
SQL语句两个字段或多个字段同时order by 排序
832 0
|
5月前
|
SQL 关系型数据库 MySQL
[MySQL]SQL优化之索引的使用规则
[MySQL]SQL优化之索引的使用规则
|
5月前
|
Oracle Java 关系型数据库
【问题】Cause: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
【问题】Cause: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
|
5月前
|
SQL 数据库管理
SQL基础题----基本的SELECT语句、order by排序
SQL基础题----基本的SELECT语句 ambiguous 模糊
208 1
|
5月前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
48 0
下一篇
无影云桌面