SQL Server，其中子句与不同类型和默认转换行为进行比较

Right... this one had me baffled for a while today so maybe one of you SQL Server bright sparks can shed some light on this behaviour.

是的...这个让我困惑了一段时间,所以也许你们其中一个SQL Server明亮的火花可以解释这种行为。

We have a table Phones. In it, the phone numbers are stored as nvarchars and it contains numbers in International format, in only numeric format... so a US number +1-(212)-999-9999 is stored as 12129999999

我们有一个表电话。在其中,电话号码存储为nvarchars,它包含国际格式的数字,仅以数字格式...所以美国数字+ 1-(212)-999-9999存储为12129999999

For reasons that are beyond, me someone had written a SPROC, that took the phone number as a bigint, did no casting, did a simple where clause = comparison, and this worked absolutely fine, until some junk data got into the nvarchar column on the table which caused it to break. Consider the following test script.

由于超出原因,我有人编写了一个SPROC,它将电话号码作为bigint,没有进行转换,做了一个简单的where子句=比较,这非常好用,直到一些垃圾数据进入nvarchar列导致它破裂的桌子。请考虑以下测试脚本。

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Phones')
BEGIN
    DROP TABLE Phones
END
GO

CREATE TABLE [dbo].[Phones]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Mobile] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED 
    ( [ID] ASC )
    WITH (
      PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE @biMobile_1 bigint
DECLARE @biMobile_2 bigint
SET @biMobile_1 = 12121111111
SET @biMobile_2 = 12129999999

Print 'Inserting Phone Number'
INSERT INTO Phones (Mobile) VALUES ('12121111111')

Print 'Selecting Phone Number'
SELECT * FROM Phones WHERE Mobile = @biMobile_1 --Select #1

Print 'Inserting Junk Data'
INSERT INTO Phones (Mobile) VALUES ('JUNK DATA')
INSERT INTO Phones (Mobile) VALUES ('12129999999')

Print 'Selecting From Table Containing Junk'
SELECT * FROM Phones WHERE Mobile = @biMobile_1 -- Select #2
SELECT * FROM Phones WHERE Mobile = @biMobile_2 -- Select #3

The first select (marked #1) will work The second select (marked #2) will work but will give an error immediately after The third select (marked #3) returns nothing.

第一个选择(标记为#1)将起作用第二个选择(标记为#2)将起作用但在第三个选择(标记为#3)之后不会立即返回错误。

The error returned is

返回的错误是

Error converting data type nvarchar to bigint.

Now this seems completely bonkers behaviour. What I thought would happen is

现在这似乎是完全疯狂的行为。我认为会发生的是

SQL realises its comparing 2 different data types in the WHERE clause

SQL实现了它在WHERE子句中比较2种不同的数据类型

It would attempt to convert the @variable to the datatype of the column

它会尝试将@variable转换为列的数据类型

If it fails, throw an error, it it works, GREAT!!!

如果它失败了,抛出一个错误,它就可以了,太棒了!

What actually seems to be happening is

实际上似乎正在发生的是

SQL realises its comparing 2 different data types in the WHERE clause

SQL实现了它在WHERE子句中比较2种不同的数据类型

On a row by row basis, it converts the value in the column to the datatype of the @variable

在逐行的基础上,它将列中的值转换为@variable的数据类型

For each successful conversion, it does the comparison, and will return that row.

对于每次成功转换,它都会进行比较,并返回该行。

If it hits a value in the column, that it can't convert, it bombs, returns whatever data it has found so far, and doesn't continue on through the table.

如果它遇到列中的值,它无法转换,则会发生炸弹,返回到目前为止找到的任何数据,并且不会继续通过表。

Can anyone clarify what the reasoning is behind this logic, and if theres any particular order of precedence that SQL Server gives to Data Types when it's deciding what to compare/cast

任何人都可以澄清这个逻辑背后的原因,以及SQL Server在决定比较/转换时给予数据类型的任何特定优先顺序

Note. I did this test in SQL 2005 but it's replicable behaviour in SQL2K also.

注意。我在SQL 2005中进行了这个测试,但它在SQL2K中也是可复制的行为。

6 个解决方案

#1

Data type precedence is well defined - http://msdn.microsoft.com/en-us/library/ms190309.aspx

数据类型优先级定义明确 - http://msdn.microsoft.com/en-us/library/ms190309.aspx

Edit - to clarify, it is not that sql always converts the column type to the param type. It just follows the type precedence in the link I gave. This could mean the param gets converted to the column type, if the type precedence dictates so.

编辑 - 澄清一下,并不是说sql总是将列类型转换为param类型。它只是遵循我给出的链接中的类型优先级。这可能意味着如果类型优先级指示,则param将转换为列类型。

#2

I don't see what the problem is. Why would SQL server know that record #232 out of #1000 would bomb? It doesn't until it gets to that record.

我不知道问题是什么。为什么SQL服务器会知道#1000中的记录#232会炸弹?直到它达到该记录为止。

In the meantime, it's streaming the results back to the client as they are generated. This is to help with performance.

与此同时,它会在生成结果时将结果传回客户端。这有助于提高性能。

What else would you expect?

你还期待什么?

#3

Personally rather than converting the parameter to the correct type, I would declare it as that type to begin with.

我个人而不是将参数转换为正确的类型,我会将其声明为该类型。

#4

You describe what's happening perfectly, so I don't understand what you'd like to know that you don't know yet?

你描述了发生了什么,所以我不明白你还不知道你还不知道什么?

Fixing the query is easy enough, just ask Sql Server to cast your parameter to varchar, instead of casting the varchar column to bigint:

修复查询很简单,只需要让Sql Server将参数转换为varchar,而不是将varchar列转换为bigint:

SELECT * FROM Phones WHERE Mobile = cast(@biMobile_1 as varchar(50))

#5

The problem is that implicit conversion from nvarchar to bigint is only invalid when the contents of the nvarchar contain non-numeric characters. The database engine is not going to execute the query once just to check every value that would be returned to see if the query you submitted is valid for every row, only to then turn around and execute the query again to return the results.

问题是,当nvarchar的内容包含非数字字符时,从nvarchar到bigint的隐式转换才有效。数据库引擎不会执行查询只是为了检查将返回的每个值,以查看您提交的查询是否对每一行都有效,然后再转向并再次执行查询以返回结果。

Query execution and returning data commences until it does find a comparison that proves to be invalid.

查询执行和返回数据开始,直到找到证明无效的比较。

#6

Your logic process description is indeed correct.

您的逻辑过程描述确实是正确的。

SQL Server is implicity applying a Cast/Convert function to the mobile column (in this case an implicit conversion from nvarchar to bigint). Functions are of course applied on a row by row basis and hence the behaviour your are seeing, whereby the select statement does not bomb out until the cast function fails.

SQL Server无意中将Cast / Convert函数应用于移动列(在本例中是从nvarchar到bigint的隐式转换)。函数当然是逐行应用的,因此也是你所看到的行为,因此select语句在cast函数失败之前不会弹出。

The issue can be avoided by converting the single numeric variable that is being searched for (@biMobile_2) to an nvarchar, rather than implicitly having the Mobile column converted for all rows.

通过将要搜索的单个数字变量(@ biMobile_2)转换为nvarchar,而不是隐式地为所有行转换Mobile列,可以避免此问题。

For example:

SELECT * FROM Phones WHERE Mobile = convert(nvarchar,@biMobile_2)

Hope this helps.

希望这可以帮助。

#1