具有错误 GUID 的 Transact-SQL Select 语句结果 [英] Transact-SQL Select statement results with bad GUID

问题描述

我们有一个带有 GUID 主键的表.当我搜索特定键时，我可以使用:

We have a table with GUID primary keys. When I search for a specific key, I can use either:

SELECT * FROM products WHERE productID='34594289-16B9-4EEF-9A1E-B35066531DE6'
SELECT * FROM products WHERE productID LIKE '34594289-16B9-4EEF-9A1E-B35066531DE6'

结果(对于两者):

product_ID                           Prd_Model
------------------------------------ --------------------------------------------------
34594289-16B9-4EEF-9A1E-B35066531DE6 LW-100

(1 row affected)

我们有一位客户使用我们的 ID，但向其添加了更多文本以在他们自己的系统中创建某种复合字段.他们给我发送了其中一个值来查找，我得到了意想不到的结果.我想修剪后缀但忘记了，所以我运行了这个:

We have a customer who uses our ID but adds more text to it to create some kind of compound field in their own system. They sent me one of these values to look up and I had an unexpected result. I meant to trim the suffix but forgot, so I ran this:

SELECT * FROM products WHERE productID='34594289-16B9-4EEF-9A1E-B35066531DE6_GBR_USD'

当我运行它时，出乎意料地得到了相同的结果:

When I ran it, I unexpectedly got the same result:

product_ID                           Prd_Model
------------------------------------ --------------------------------------------------
34594289-16B9-4EEF-9A1E-B35066531DE6 LW-062

(1 row affected)

现在，如果我在搜索时从 GUID 的末尾修剪一个值，我将一无所获(GUID 为 1 位数):

Now if I trim a value off the end of the GUID when searching I get nothing (GUID is 1 digit short):

SELECT * FROM products WHERE productID='34594289-16B9-4EEF-9A1E-B35066531DE'

结果:

product_ID                           Prd_Model
------------------------------------ --------------------------------------------------  

(0 rows affected)

当使用 LIKE 命令而不是 '=' 并且如果我在末尾添加后缀时，该语句返回零结果.这是我所期望的.

When using the LIKE command instead of '=' and if I add the suffix to the end, the statement returns zero results. This is what I would expect.

那么为什么在语句中使用'='时，末尾添加后缀的较长字符串会返回结果?它显然忽略了超过 36 个字符的 GUID 长度的任何内容，但我不知道为什么.

So why does the longer string with the suffix added to the end return a result when using '=' in the statement? It's obviously ignoring anything beyond the 36-character GUID length but I'm not sure why.

推荐答案

这种行为是 记录:

uniqueidentifier 类型被认为是一种字符类型，用于从字符表达式转换，因此受制于转换为字符类型的截断规则.也就是说，当字符表达式转换为不同大小的字符数据类型时，对于新数据类型来说太长的值将被截断.请参阅 示例 部分.

Converting uniqueidentifier Data

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. See the Examples section.

因此，字符串值 '34594289-16B9-4EEF-9A1E-B35066531DE6_GBR_USD' 被截断为 '34594289-16B9-4EEF-9A1E-B3506653>隐式转换(由于 Data Type Precedence) 到 uniqueidentifier，不出所料，'34594289-16B9-4EEF-9A1E-B35066531DE6' 等于本身因此返回该行.

So, the string value '34594289-16B9-4EEF-9A1E-B35066531DE6_GBR_USD' is truncated to '34594289-16B9-4EEF-9A1E-B35066531DE6' when it is implicitly cast (due to Data Type Precedence) to a uniqueidentifier and, unsurprisingly, '34594289-16B9-4EEF-9A1E-B35066531DE6' equals itself so the row is returned.

而且文档确实给出了一个例子:

And the documentation does indeed give an example:

以下示例演示了当值对于要转换为的数据类型来说太长时截断数据.由于 uniqueidentifier 类型限制为 36 个字符，超过该长度的字符将被截断.

The following example demonstrates the truncation of data when the value is too long for the data type being converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.

DECLARE @ID NVARCHAR(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';  
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;  

这是结果集.

String                                       TruncatedValue  
-------------------------------------------- ------------------------------------  
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong    0E984725-C51C-4BF4-9960-E1C80E27ABA0  

然而，我觉得奇怪的是你说下面的语句没有返回任何行:

I, however, find it odd that you say that the statement below returns no rows:

SELECT * 
FROM products
WHERE productID='34594289-16B9-4EEF-9A1E-B35066531DE'

虽然是真的，但它不会返回行，它也会出错:

Though true, it won't return rows, it will also error:

从字符串转换为 uniqueidentifier 时转换失败.

Conversion failed when converting from a character string to uniqueidentifier.

事实并不意味着您的列不是一个uniqueidentifier，这意味着您的第一个陈述不正确；因为较长的字符串不会被截断.这意味着问题中的陈述之一可能是错误的；要么你的列是一个uniqueidentifier，因此你得到结果但在后者中得到一个错误，或者它不是，两个语句都不会返回结果集.正如您在此演示中看到的:

The fact it doesn't implies your column isn't a uniqueidentifier which would mean that your first statement isn't true; as the longer string would not be truncated. This means that one of the statements in the question is likely wrong; either your column is a uniqueidentifier and thus you get results but get an error in the latter, or it isn't and neither statement would return a result set. As you can see in this demonstration:

CREATE TABLE dbo.YourTable (UID uniqueidentifier, String varchar(36));

INSERT INTO dbo.YourTable (UID,String)
VALUES('34594289-16B9-4EEF-9A1E-B35066531DE6','34594289-16B9-4EEF-9A1E-B35066531DE6');
GO
--Returns data
SELECT *
FROM dbo.YourTable
WHERE UID = '34594289-16B9-4EEF-9A1E-B35066531DE6_GBR_USD'
GO
--Errors
SELECT *
FROM dbo.YourTable
WHERE UID = '34594289-16B9-4EEF-9A1E-B35066531DE';
GO
--Returns no data
SELECT *
FROM dbo.YourTable
WHERE String = '34594289-16B9-4EEF-9A1E-B35066531DE6_GBR_USD'
GO
--Returns no data
SELECT *
FROM dbo.YourTable
WHERE String = '34594289-16B9-4EEF-9A1E-B35066531DE'

GO
DROP TABLE dbo.YourTable;

db<>fiddle

这篇关于具有错误 GUID 的 Transact-SQL Select 语句结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！