通过排序规则解决 SQL Server 中文显示为问号或乱码

通过前两篇文章 ARM Win11 如何安装 SQL Server？ 和 DataGrip 如何添加 Azure SQL Edge 的 Data Source？ 我已经能成功的在 Mac 上管理 Azure SQL Edge 了。

今天发现导入到 Azure SQL Edge 中数据库的数据，中文显示成 ?，这个问题一看就是配置的字符集不是 Unicode 字符集，那该如何修改字符集呢？

标题用的 SQL Server，是因为 Azure SQL Edge 建立在与 SQL Server 和 Azure SQL 相同的引擎上。用 SQL Server 大家比较熟知。

排序规则

在 SQL Server 中，字符集被称为排序规则（即 Collation）。排序规则不仅影响记录行的排序顺序，还影响中文显示是否 乱码 等。

关于排序规则微软官方文档 排序规则和 Unicode 支持 - SQL Server | Microsoft Docs 已经讲的非常详细了。我这里只讲下解决本文问题需要的知识点。

SQL Server 中的排序规则可为数据提供排序规则、区分大小写属性和区分重音属性。 与诸如 char 和 varchar 等字符数据类型一起使用的排序规则规定可表示该数据类型的代码页和对应字符 。

SQL Server 实例的下列级别支持设置排序规则：

• 服务器级排序规则
• 数据库级排序规则
• 列级排序规则
• 表达式级排序规则

不合适的排序规则会有如下影响：

• JOIN、ORDER BY 和其他比较文本数据的运算符的排序和比较规则。
• 系统视图、系统函数和 TempDB 中的对象（例如临时表）中 CHAR、VARCHAR、NCHAR 和 NVARCHAR 列的排序规则。
• 变量、游标和 GOTO 标签的名称。 如果服务器级排序规则区分大小写，则变量 @pi 和 @PI 被视为不同变量，如果服务器级排序规则不区分大小写，则将这两个变量视为相同变量。

服务器排序规则

默认服务器排序规则是在安装 SQL Server 期间确定的，它是根据操作系统的区域设置来确认默认排序规则，并且它将成为系统数据库和所有用户数据库的默认排序规则。

为服务器分配排序规则后，只能通过导出所有数据库对象和数据来更改它，重新生成 master 数据库，并导入所有数据库对象和数据。 您可以在创建新的数据库或数据库列时指定所需的排序规则，而不是更改 SQL Server 实例的默认排序规则。

-- 查看当前 SQL Server 支持的排序规则。
-- CI 表示不区分大小写。
SELECT * from sys.fn_helpcollations();
SELECT * from sys.fn_helpcollations() where name like 'chinese%';

-- 查询当前 SQL Server 服务器的排序规则。
SELECT SERVERPROPERTY(N'Collation');
SELECT CONVERT(nvarchar(128), SERVERPROPERTY('collation'));

不能更改或设置 Azure SQL 数据库的实例级排序规则。 有关 SQL 托管实例和 SQL Server 的信息，请参阅：设置或更改服务器排序规则。

服务器排序规则如何设置

上面已经说了修改服务器排序规则是很麻烦的。因此在安装数据库的时候就要选择好。下面我只给出安装时配置服务器排序的方法，安装后还要修改的话，也是不建议的，所以直接忽略。

SQL Server

SQL Sever 的在安装的时候是可以修改排序规则的。

Azure SQL Edge

在微软官方文档中并没有给出通过 Docker 运行 Azure SQL Edge 实例配置排序规则的说明。

但是在 从 Azure 市场部署 SQL Edge 模块 的文档中有讲到 MSSQL_COLLATION 参数就是用来配置服务器排序规则的。

因此在运行容器时，添加 -e 'MSSQL_COLLATION=Chinese_PRC_CI_AS' 即可配置服务器容器。

sudo docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=Passw0rd' -e 'MSSQL_COLLATION=Chinese_PRC_CI_AS'  -p 1434:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge 

数据库排序规则

-- 查询数据库的排序规则
SELECT name,
       collation_name
FROM sys.databases;
SELECT CONVERT (nvarchar(128), DATABASEPROPERTYEX('database_name1', 'collation'));

创建或修改数据库时，可使用 CREATE DATABASE 或 ALTER DATABASE 语句的 COLLATE 子句指定默认数据库排序规则。 如果未指定排序规则，将为该数据库分配服务器排序规则。

create database database_name collate Chinese_PRC_CI_AS;
 
alter database database_name collate SQL_Latin1_General_CP1_CI_AS;

数据库排序规则将应用于数据库中的所有元数据，并且是所有字符串列、临时对象、变量名称和数据库中使用的任何其他字符串的默认排序规则。 当更改用户数据库的排序规则时，如果在数据库访问临时表中进行查询，则可能出现排序规则冲突。 临时表始终存储在 tempdb 系统数据库中，该数据库使用实例的排序规则。 如果排序规则导致计算字符数据时出现冲突，则比较用户数据库和 tempdb 之间的字符数据的查询可能会失败。 可以通过在查询中指定 COLLATE 子句来解决此问题。 有关详细信息，请参阅排序规则 (Transact-SQL)。

例如用户数据库和临时表进行连接查询，如果它们的排序规则不一致，则会执行失败：

/*
注意：
 这里 database_name1 的字符集是 Chinese_PRC_CI_AS,
 而 instance 的字符集是 SQL_Latin1_General_CP1_CI_AS
*/
-- 创建一张表 collation_test
CREATE TABLE collation_test (hyper varchar(10));
GO
-- 创建临时表collation_temp
CREATE TABLE #collation_temp (hyper varchar(10));
GO
-- 查询报错
SELECT  *
FROM collation_test l
LEFT JOIN #collation_temp c
    ON l.hyper = c.hyper;
-- [S0009][468] Line 4: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AS" in the equal to operation.
------------------------------------------------------- 
-- 方式1：
--注意指定表collation_test使用排序规则COLLATE Chinese_PRC_CI_AS
SELECT  *
FROM collation_test l
LEFT JOIN #collation_temp c
    ON l.hyper = c.hyper COLLATE Chinese_PRC_CI_AS;
-------------------------------------------------------
-- 方式2
-- 注意指定了列的排序规则：COLLATE Chinese_PRC_CI_AS
CREATE TABLE #collation_temp1
(hyper varchar(10) COLLATE Chinese_PRC_CI_AS);
-- 保持列的排序规则一致即可正常使用临时表#collation_temp
SELECT  *
FROM collation_test l1
LEFT JOIN #collation_temp1 c
    ON l1.hyper = c.hyper;
-------------------------------------------------------

但是这两种方法都要指定排序规则，也是挺麻烦的。

列排序规则

当创建或更改表时，可使用 COLLATE 子句指定每个字符串列的排序规则。 如果不指定排序规则，将为列分配数据库的默认排序规则。

-- 修改排序规则
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI;

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

-- 查询表中列的排序规则。
SELECT name, collation_name
FROM sys.columns
where collation_name is NOT NULL order by name;

中文显示为 ?

通过上面的知识点，先查询下排序规则。

我这里查询到服务器和数据库的排序规则都是 SQL_Latin1_General_CP1_CI_AS。该排序规则对应的区域英语（美国）。在该排序规则下，数据默认不是以 Unicode 格式化存储，所以中文才会显示为 ?。

并且该排序规则下数据类型的长度就表示可以存几个字符或几个汉字，比如 varchar(4)，可以最多存 4 个汉字或字符。而 Chinese_PRC_CI_AS 排序规则下，varchar(4)，最多存 2 个汉字或 4 个字符。

解决方法一：不修改排序规则

1. 修改 varchar 为 nvarchar。

2. 添加或修改数据时，在字符串前面添加 N，N 代表 SQL-92 标准中的国际语言 (National Language)，表示存入数据库时以 Unicode 格式存储。
   N’string’ 表示string是个Unicode字符串。

   insert into table_name values (N'张三a1', N'张三李四');

解决方法二：修改排序规则（推荐）

将数据库排序规则修改为 Chinese_PRC_CI_AS ，这样字符串前面也不用加 N，因为它会根据排序规则自动转换为 Unicode 编码。

alter database database_name collate Chinese_PRC_CI_AS;

但是服务器排序规则还是 SQL_Latin1_General_CP1_CI_AS，如果用到临时表和用户数据库表进行连接查询时，不指定排序规则还是会失败。

总结

1. 只有保持服务器排序、数据库、列排序规则一致，才不会出现排序规则冲突。由于服务器分配排序规则后，只能通过导出所有数据库对象和数据来更改它，这个操作比较复杂，因此在安装 SQL Server 时，一定要选择合适的排序规则，比如数据库要用到中文，建议选择 Chinese_PRC_CI_AS 排序规则。

2. 如果在 SQL Server (SQL Server 2005 (1.x) 和更高版本中存储反映多种语言的字符数据，请使用 Unicode 数据类型) nchar、 nvarchar和ntext (，而不是) char、 varchar和text (的非 Unicode 数据类型。