日期
在开始讨论编写可移植代码时,让我们首先探讨一下以可移植的方式处理日期时所涉及到的问题。尽管人们通常将编写可移植的数据库代码与以不同方式处理常规字符串联系起来,但一开始编写可移植的日期处理代码是一种很好的方法,因为它基本上只需知道一些简单的规则和选项就可以了。
有关以可移植的方式在 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
显示日期值
比较和存储日期
当在 DML 或比较语句中指定日期时,请务必使用在所有语言中都具有相同含义的常量。在客户端应用程序中做到这一点的最简单且最安全的方法是,将此类语句作为 RPC 事件提交给服务器(例如,使用托管代码中的 SqlCommand 对象),并且使用显式参数传递日期。从客户端应用程序传递的并且以其内置格式编码的参数在本质上是明确的。
另一种选择是使用 ODBC 转义子句来表示在各种语言中一致的特定格式。应用程序不需要是 ODBC 应用程序,因为转义子句由服务器解释。前面提到过的联机图书主题“Writing International Transact-SQL Statements”(编写国际性 Transact-SQL 语句)详细介绍了受支持的转义子句。
另一种选择是使用 CONVERT() Transact-SQL 函数及其样式参数,以便显式指定转换日期的目标格式或源格式。这将消除日期/时间字符串的歧义,并且可以在各种语言之间移植。
Unicode
在编写可以在各种语言之间移植的 Transact-SQL 代码时,最简单最直接的方法是使用 Unicode 数据类型来表示和存储字符数据。这意味着您应该使用 nchar 来代替 char,使用 nvarchar 来代替 varchar,以及使用 ntext 来代替 text。尽管使用 Unicode 数据类型会在可存储的字符串的长度方面有所限制,并且可能比使用非 Unicode 类型稍微慢一些和麻烦一些,但它仍不失为处理语言可移植性问题的最简单方法,并且是唯一的、无须考虑其他编码注意事项就能行得通的解决方案。
在采用这一方法时,必须按照联机图书主题“Using Unicode Data”(使用 Unicode 数据)中所规定的那样,小心地在 Unicode 字符串常量前面加上一个大写字母 N 前缀。正如知识库文章 239530, aspx?scid=kb;[LN];239530" target="_blank">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
排序规则
请观察下面的代码,看一下您是否能够确定它有什么错误:
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)
上述代码行的目的是筛选掉函数的无效输入。如果一个字符不在 0 和 9 之间或者 A 和 F 之间,则它不可能是有效的十六进制数字,从而将导致函数失败。该代码行试图利用以下事实:在默认的 ASCII 字符集中,字符 0 到 9 恰好位于 A 的前面。其编写方式的问题在于检查是与排序规则相关的,对于某些输入将无法正确工作。例如,请看以下输入:
select dbo.hexnum('0x')
它将返回:
-------------------- 145
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 可为您提供需要的工具。
相关书籍
aspx?pcId=4380&prodId=1712164&ptnrid=141&ptnrdata=0" target="_blank">The Guru's Guide to SQL Server Architecture and Internals
aspx?pcId=4380&prodId=306674&ptnrid=141&ptnrdata=0" target="_blank">The Guru's Guide to SQL Server Stored Procedures, XML, and HTML
面向开发人员的 SQL Server
Ken Henderson 居住在美国德克萨斯州达拉斯市的郊区,身兼丈夫和父亲之职。他著有八部涉及各种与技术有关的主题的著作,包括最近出版的 The Guru's Guide to SQL Server Architecture and Internals (Addison-Wesley, 2003)。作为达拉斯小牛队的一名热心球迷,Ken 在业余时间照料自己的孩子,参加体育锻炼,并从事园艺活动