编写可在多种语言之间移植的 Transact-SQL 代码

项目
10/29/2007

Ken Henderson

摘要：Ken 讨论了在 SQL Server 2000 中得到增强的本地化功能，这些功能使得编写可以在多种语言之间移植的 Transact-SQL 代码变得更加容易。

本页内容

简介
日期
Unicode Unicode
排序规则
小结
相关书籍

简介

在本月的专栏中，我将讨论那些希望编写可以在多种语言之间移植的 Transact-SQL 代码的开发人员所面临的一些问题。在 SQL Server 7.0 中引入了 Unicode 数据类型、排序规则以及其他各种国际化和本地化功能，从而与以前的版本相比，使得编写可以在多种语言之间移植的 Transact-SQL 代码变得更加容易。SQL Server 2000 通过添加列级别的排序规则等内容增强了 SQL Server 7.0 中的国际化功能，因此可供开发人员使用的工具已经随着时间的推移而变得越来越好。

SQL Server 联机图书 (BOL) 包含一个简短的部分，内容是关于编写可以在多种语言之间移植的代码的，您应该首先阅读一下。如果您尚未阅读 BOL 主题“Writing International Transact-SQL Statements”（编写国际性 Transact-SQL 语句），我建议您首先阅读一下，然后再阅读本专栏。

日期

在开始讨论编写可移植代码时，让我们首先探讨一下以可移植的方式处理日期时所涉及到的问题。尽管人们通常将编写可移植的数据库代码与以不同方式处理常规字符串联系起来，但一开始编写可移植的日期处理代码是一种很好的方法，因为它基本上只需知道一些简单的规则和选项就可以了。

有关以可移植的方式在 Transact-SQL 中处理日期方面的内容，我想谈的第一点就是：避免就日期格式或一周的第一天进行假设。第一点似乎是很显然的 — 大多数人都知道全世界的日期格式是不一样的。然而，第二点可能不是那么明显。在不同的文化中，将哪一天视为一周的第一天是不同的。例如，请看以下代码：

set language us_english
select datepart (dw, '20060606')

set language british
select datepart (dw, '20060606')

它将返回：

Changed language setting to us_english.
            
----------- 
3

Changed language setting to British.
            
----------- 
2

这两个查询返回不同的结果，这是因为在美国和英国，将哪一天视为一周的第一天是不同的。您可以通过查询系统函数 @@datefirst 来核实这一点：

Changed language setting to us_english.
     
---- 
7

Changed language setting to British.
     
---- 
1

您可以通过 SET DATEFIRST 命令更改将哪一天视为一周的第一天（从而更改 @@datefirst 返回的值）。默认设置随文化的不同而异。

对于以日期不可知的方式编写使用 datepart(dw,...) 的代码，有两种简单方法。第一种方法是在任何过程的开始简单地调用 SET DATEFIRST，以便就一周的第一天进行假设。这将在相应过程中覆盖 DATEFIRST 的连接设置。第二种方法是规格化 @@datefirst 返回的值，以使其与语言无关。下面是一些说明如何做到这一点的代码。

declare @ndf_dw int

set language us_english
select @ndf_dw = (@@datefirst + datepart(dw, '20060606')) % 7
select @ndf_dw, datepart(dw, '20060606')

set language british
select @ndf_dw = (@@datefirst + datepart(dw, '20060606')) % 7
select @ndf_dw, datepart(dw, '20060606')

如果您运行上述代码，您将看到 datepart(dw,...) 返回的值随当前语言设置的不同而不同，但是为 @ndf_dw 返回的值是相同的。

日期名称

Transact-SQL 日期函数为月份和星期几返回的名称将随语言设置的不同而不同。正如联机图书中所指出的，这意味着您应该使用数值日期部分而不是名称字符串来进行月份和星期几的比较。例如：

select DATENAME(dw,'20060606')

如果默认语言被设置为美国英语，则返回：

------------------------------ 
Tuesday

但如果默认语言被设置为法语，则返回：

------------------------------ 
Mardi

很明显，如果您编写的代码依赖于返回的特定语言的日期字符串，则当默认语言被设置为其他某种语言时，返回的字符串将可能破坏您的代码。使用数值日期部分可以缓解这一问题。

显示日期值

当然，在提供数据以供用户使用或显示时，您将希望使用日期部分的名称，因为它们通常会更有意义。对于基本的两层应用程序，最简单的方法可能是让 SQL Server 为您返回这些名称，而不是通过客户端应用程序中的其他某些方式来转换这些名称。如果登录语言的设置不正确，这显然不能按预期方式工作。

对于涉及到中间层或连接池的更复杂环境，假设特定最终用户的登录语言设置将是正确的可能不切实际。在这些情况下，一种更好的方法是从 T-SQL 代码中返回明确的二进制日期/时间值，从而可以在客户端应用程序中将其转换为有意义的字符串。

比较和存储日期

当在 DML 或比较语句中指定日期时，请务必使用在所有语言中都具有相同含义的常量。在客户端应用程序中做到这一点的最简单且最安全的方法是，将此类语句作为 RPC 事件提交给服务器（例如，使用托管代码中的 SqlCommand 对象），并且使用显式参数传递日期。从客户端应用程序传递的并且以其内置格式编码的参数在本质上是明确的。

另一种选择是使用 ODBC 转义子句来表示在各种语言中一致的特定格式。应用程序不需要是 ODBC 应用程序，因为转义子句由服务器解释。前面提到过的联机图书主题“Writing International Transact-SQL Statements”（编写国际性 Transact-SQL 语句）详细介绍了受支持的转义子句。

应用程序还可以使用不带分隔符的、各个部分按重要性从高到低的顺序（即 yyyymmdd）排序的字符串。无论语言设置如何，SQL Server 都将正确地解释具有这种格式的日期。

另一种选择是使用 CONVERT() Transact-SQL 函数及其样式参数，以便显式指定转换日期的目标格式或源格式。这将消除日期/时间字符串的歧义，并且可以在各种语言之间移植。

Unicode

在编写可以在各种语言之间移植的 Transact-SQL 代码时，最简单最直接的方法是使用 Unicode 数据类型来表示和存储字符数据。这意味着您应该使用 nchar 来代替 char，使用 nvarchar 来代替 varchar，以及使用 ntext 来代替 text。尽管使用 Unicode 数据类型会在可存储的字符串的长度方面有所限制，并且可能比使用非 Unicode 类型稍微慢一些和麻烦一些，但它仍不失为处理语言可移植性问题的最简单方法，并且是唯一的、无须考虑其他编码注意事项就能行得通的解决方案。

在采用这一方法时，必须按照联机图书主题“Using Unicode Data”（使用 Unicode 数据）中所规定的那样，小心地在 Unicode 字符串常量前面加上一个大写字母 N 前缀。正如知识库文章 239530, INF:Unicode String Constants in SQL Server Require N Prefix 所指出的，不这样做将导致 SQL Server 在使用相应的字符串之前将其转换为当前数据库的非 Unicode 代码页。下面是一个示例：

-- Assumes the default code page is not Greek
create table #t1 (c1 nchar(1))

insert #t1 values(N'Ω')
insert #t1 values('Ω')

select * from #t1

这将返回：

c1   
---- 
Ω
O

两个 insert 语句都试图将单个字符（希腊字母 Ω）插入到表中。正如您所看到的，在第一行的 insert 中没有数据丢失。然而，在第二行中，Ω 已经被转换为不同的字符（一个大写的 O），这是因为在第二个 INSERT 语句中缺少 N 前缀，从而导致字符串被转换为默认的代码页，而在该代码页中不存在 Ω（大写的 O 是最近似的字符）。

尽管该表的列使用 Unicode 数据类型，仍然不能防止数据丢失。发生数据丢失的原因是在源数据中省略了大写 N 前缀，因而 SQL Server 在插入源数据之前将其转换为默认代码页。对于日期值，如果您将采用 Unicode 参数的查询作为 RPC 事件提交，则可以缓解通过 N 前缀来区分 Unicode 字符串和非 Unicode 字符串的需要 — RPC 参数本身就可以向服务器定义其数据类型。

排序规则

请观察下面的代码，看一下您是否能够确定它有什么错误：

create function hexnum(@hexstr varchar(10))
returns bigint
as
begin
if left(@hexstr,2) in ('0x','0X') set @hexstr=substring(@hexstr,3,10)  -- Lop off 0x prefix
declare @i int, @res bigint, @l int, @c char
select @i=1, @l=len(@hexstr), @res=0
if @hexstr is null OR @l=0 return null
while @i<=@l begin
   set @c=upper(substring(@hexstr,@i,1))
   if @c<'0' OR @c>'F' return(null)
set @res=@res+cast(1.0 as bigint)*case when isnumeric(@c)=1 then
cast(@c as int) else ascii(@c)-55 end*power(16,@l-@i)
   set @i=@i+1
end
return(@res)
end

hexnum() UDF 的明显目的是将传递给该函数的十六进制字符串转换为相应的整数值。您看出该函数有什么错误了吗？让我给您一点提示：它与排序规则依赖性有关。让我们仔细看一下以下的代码行：

if @c<'0' OR @c>'F' return(null)

SQL Server 2000 将一个字符视为小于还是大于另一个字符与排序规则相关：排序规则定义了用于表示字符串中各个字符的位模式，以及对字符的排序和比较方式进行控制的规则。

上述代码行的目的是筛选掉函数的无效输入。如果一个字符不在 0 和 9 之间或者 A 和 F 之间，则它不可能是有效的十六进制数字，从而将导致函数失败。该代码行试图利用以下事实：在默认的 ASCII 字符集中，字符 0 到 9 恰好位于 A 的前面。其编写方式的问题在于检查是与排序规则相关的，对于某些输入将无法正确工作。例如，请看以下输入：

select dbo.hexnum('0x')

它将返回：

-------------------- 
145

这显然是不正确的。原因在于字符比较与排序规则相关。在默认的排序规则中，示例字符恰好位于 0 和 F 之间，但它仍然不是有效的十六进制数字。初学者可能将该代码行重写为：

if @c NOT LIKE '[0-9ABCDEF]' return(null)

然而，LIKE 在这里不是最佳的解决方案。它不需要使用，并且没有更为简单的技术有效。那么，我们如何编写检查以使其不会受到排序规则细微差别的影响呢？请看下面的代码：

if not ascii(@c) between @ascii0 and @asciiF return(null)

当然，这些代码需要使用几个新的变量：@ascii0 和 @asciiF。我们可以在函数顶部的 SELECT 语句中相当简单地对其进行赋值：

select @i=1, @l=len(@hexstr), @res=0, @ascii0=ascii('0'), @asciiF=ascii('F')

下面是修改后的完整函数：

create function hexnum(@hexstr varchar(10))
returns bigint
as
begin
if left(@hexstr,2) in ('0x','0X') set @hexstr=substring(@hexstr,3,10)  -- Lop off 0x prefix
declare @i int, @res bigint, @l int, @c char, @ascii0 int, @asciiF int
select @i=1, @l=len(@hexstr), @res=0, @ascii0=ascii('0'), @asciiF=ascii('F')
if @hexstr is null OR @l=0 return null
while @i<=@l begin
   set @c=upper(substring(@hexstr,@i,1))
   if not ascii(@c) between @ascii0 and @asciiF return(null)
   set @res=@res+cast(1.0 as bigint)*case when isnumeric(@c)=1 then 
cast(@c as int) else ascii(@c)-55 end*power(16,@l-@i)
   set @i=@i+1
end
return(@res)
end

让我们用测试字符串再次对其进行测试，看一下它是否能够正确工作：

SELECT dbo.hexnum('0x')

这将返回：

-------------------- 
NULL

这正是我们所需要的。用其他输入测试时，看起来可以确认该函数能够按预期方式工作，并正确地筛选掉无效值。

让我们看一下另一个与排序规则相关的编码问题。假设您要使用默认的数据库排序规则 Latin1_General_BIN（以便确保区分大小写），并且具有一项用不区分大小写的谓词筛选针对特定表进行的查询的业务需要。您可能喜欢编写与下面类似的代码：

select au_id FROM authors WHERE LOWER(au_lname) = LOWER(@au_lname)

但是，您不应该假设在每种排序规则和语言中，术语的大写形式和小写形式都具有完全相同的含义。在土耳其语中，大写的“i”不是“I”，而是“?”（请注意圆点）。即使对于完全相同的输入，LOWER(@au_lname) 也可能在不同的服务器上具有不同的输出。

该技术也有性能问题。通过将列包装到函数中，代码妨碍了优化器识别索引以便为查询提供服务的能力。要解决该问题，您可能需要创建一个计算列，将您的列转换为特定的字符大小写，并且在该计算列上创建索引，如下所示：

alter table authors
add au_lname_lower as LOWER(au_lname)

create index foo on authors (au_lname_lower)

select au_id from authors where au_lname_lower=LOWER(@au_lname)

但是，添加新列和索引不是最佳的 SQL Server 2000 解决方案。它不需要使用，并且没有更为简单的技术有效。那么，我们可以进行哪些更改，以便使查询不会受到排序规则细微差别的影响呢？这里，最佳的解决方案是用不区分大小写的排序规则定义相应的列，如下所示：

create table authors 
(...
au_lname varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
...)

任何其他解决方案都相当于试图用 Transact-SQL 编写排序规则，这没有必要，而且充其量在 SQL Server 2000 中是靠不住的。

上述事实的教训是，对于 Transact-SQL 之类支持排序规则的语言，自编程语言问世以来便存在的有关字符比较的传统观念未必适用。在与排序规则相关的环境中，您必须采用不同的编码方式，但结果将是您的代码可以在各种语言之间移植。

小结

如果您能够使用 Unicode 数据类型并遵循几个简单的规则，则编写可移植的 Transact-SQL 代码并不困难。应用程序越来越需要满足可以在各种语言之间移植的要求。如果您的业务需要要求您编写可以在多种语言环境中运行的 Transact-SQL 代码，则 SQL Server 2000 可为您提供需要的工具。