【SQL】小心字符串拼接导致长度爆表

原文:【SQL】小心字符串拼接导致长度爆表

请看代码:

DECLARE @max VARCHAR(max)
SET @max=aaa... --这里有8000个a
         +bb --连接一个varchar常量或变量

SELECT LEN(@max)

别想当然以为它会返回8002,而是8000,select @max也只会得到8000个a,后面两个b没了。我们知道,varchar(max)类型不受字符数限制,但为什么会这样?

这其实与@max的数据类型无关,而是与字符串拼接后得到的数据类型有关,或者说,与字符串常量的数据类型推断有关。在SQL 2005和SQL 2008(R2)中,敲一个‘a‘,系统会把它作为varchar(1),‘aa‘则是varchar(2),N‘a‘则是nvarchar(1),而‘a...‘(超过8000个a)呢,05中会当它是text,08则当它是varchar(max),常量或变量的数据类型可以通过系统函数SQL_VARIANT_PROPERTY获取,使用示例:

SELECT SQL_VARIANT_PROPERTY(a,BaseType)

有关该函数的更多信息请参看SSMS帮助或http://technet.microsoft.com/zh-cn/library/ms178550(v=sql.105).aspx

那么问题来了,既然超过8000个字符的常量系统会自动识别为大数据类型,不会出现截断,为什么拼接一下就歇菜了,这是因为varchar(n)+varchar(n)还是=varchar(n),拼接时系统会自动拓展数据长度,但不会更改数据类型(varchar(n)与varchar(max)应视为不同数据类型),又因为varchar(n)中的n最大取值为8000,所以varchar(x)+varchar(y)最大只会得到varchar(8000),当x+y>8000时,便会出现截断。

回到文章开头的例子,就很明了了,‘aaa...‘和‘bb‘都是varchar(n),拼接后得到varchar(8000),也就是截断了的8000个‘aaa...‘,所以即便把它赋值给varchar(max)也无济于事。如果‘aaa...‘再多个a,情况又不同了,这时就是text或varchar(max)+varchar(n),对于05,会报text与varchar不能拼接,对于08,会正确得到无截断的‘aaa...bb‘,因为varchar(max)+varchar(n)=varchar(max)。

OK就到这里,希望猿友在拼接SQL字串时留意这个问题。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。