SQL Server：设置字符集（不是排序规则） [英] SQL Server: set character set (not collation)

问题描述

在SQL Server中创建表时，如何为字段设置默认字符集？在MySQL中，这样做：

How does one set the default character set for fields when creating tables in SQL Server? In MySQL one does this:

CREATE TABLE tableName (
    name VARCHAR(128) CHARACTER SET utf8
) DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

注意，我在这里设置了两次字符集。这是多余的，我添加了两种方式只是为了演示。

Note that I set the character set twice here. It is redundant, I added both ways just to demonstrate.

我设置了排序规则，以证明排序规则不同。我不是要求设置排序规则。 最多 通过排序来回答，这不是同样的事情。

I set the collation also to demonstrate that collation is something different. I am not asking about setting the collation. Most questions asking about character sets and encodings in SQL Server are answered with collation, which is not the same thing.

推荐答案

As stated in BOL

每个SQL Server排序规则都指定三个属性：

Each SQL Server collation specifies three properties:

• 用于Unicode数据类型（nchar，nvarchar和ntext）的排序顺序。排序顺序定义字符为
  排序的顺序，以及在比较操作中计算字符的方式。


• 用于非Unicode字符数据类型的排序顺序char，varchar和text）。


• 用于存储非Unicode字符数据的代码页。

上面的引文来自2000文档。 另请参阅此2008年链接。

The quote above is from 2000 docs. See also this 2008 link. The below also demonstrates this.

DECLARE @T TABLE 
(
     code TINYINT PRIMARY KEY,
     Arabic_CS_AS CHAR(1) COLLATE Arabic_CS_AS NULL,
     Cyrillic_General_CS_AS CHAR(1) COLLATE Cyrillic_General_CS_AS NULL,
     Latin1_General_CS_AS CHAR(1) COLLATE Latin1_General_CS_AS NULL
);

INSERT INTO @T(code) VALUES (200),(201),(202),(203),(204),(205)

UPDATE @T 
  SET Arabic_CS_AS=CAST(code AS BINARY(1)),
      Cyrillic_General_CS_AS=CAST(code AS BINARY(1)),
      Latin1_General_CS_AS=CAST(code AS BINARY(1))

SELECT * 
FROM @T   

结果

code Arabic_CS_AS Cyrillic_General_CS_AS Latin1_General_CS_AS
---- ------------ ---------------------- --------------------
200  ب            И                      È
201  ة            Й                      É
202  ت            К                      Ê
203  ث            Л                      Ë
204  ج            М                      Ì
205  ح            Н                      Í

这篇关于SQL Server：设置字符集（不是排序规则）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！