提问人:Kurt 提问时间:11/13/2023 最后编辑:Gord ThompsonKurt 更新时间:11/13/2023 访问量:28
将 None 作为参数值传递给 SQL Server 时出现意外行为
Unexpected behaviour when passing None as a parameter value to SQL Server
问:
给定以下 test3 表
/****** Object: Table [dbo].[test3] Script Date: 11/12/2023 9:30:17 AM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test3]') AND type in (N'U'))
DROP TABLE [dbo].[test3]
GO
/****** Object: Table [dbo].[test3] Script Date: 11/12/2023 9:30:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test3](
[id] [int] IDENTITY(1,1) NOT NULL,
[column1] [varchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test3] ON
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (1, N'aaa')
GO
INSERT [dbo].[test3] ([id], [column1]) VALUES (2, N'bbb')
GO
SET IDENTITY_INSERT [dbo].[test3] OFF
GO
问题:
sqlstatement1 返回表的所有两行。 sqlstatement2 返回表的零行。
import pyodbc
connectionString = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=7D3QJR3;DATABASE=mint2;Trusted_Connection=yes'
currentConnection = pyodbc.connect(connectionString)
sqlStatement1 = '''
SELECT
id,
column1
FROM test3
WHERE
ISNULL(?, id) = id
ORDER BY
ID
'''
sqlStatement2 = '''
SELECT
id,
column1
FROM test3
WHERE
ISNULL(?, column1) = column1
ORDER BY
ID
'''
#Process sqlStatement1
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement1,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH ID=NULL:' + str(len(rows)))
cursor.close()
#Process sqlStatement2
sqlArgs = []
sqlArgs.append(None)
cursor = currentConnection.cursor()
cursor.execute(sqlStatement2,sqlArgs)
rows = cursor.fetchall()
print('ROWS WITH COLUMN1=NULL:' + str(len(rows)))
cursor.close()
那么,为什么它适用于 int 数据类型而不是字符串数据类型呢?
我的直觉是,当语句比较时,sp_prepexec 语句出于某种原因将位置参数 P1 创建为 varchar(1) ?添加到 varchar 列并将 P1 设置为 和 int,当语句 comapres ?到 int 列:
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int',N'
SELECT
id,
column1
FROM test3
WHERE
ISNULL(@P1, id) = id
ORDER BY
ID
',NULL
select @p1
与
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 varchar(1)',N'
SELECT
id,
column1
FROM test3
WHERE
ISNULL(@P1, column1) = column1
ORDER BY
ID
',NULL
select @p1
答:
3赞
Charlieface
11/13/2023
#1
我的直觉是,当语句比较时,sp_prepexec 语句出于某种原因将位置参数 P1 创建为 varchar(1) ?添加到 varchar 列并将 P1 设置为 和 int,当语句 comapres ?添加到 int 列。
是的,这正是它的作用。它不知道参数有多大,因为你没有告诉它。GitHub 上已经注意到了这一点。
因为您在 的左侧使用它,所以右侧被强制转换为左侧,因此查询没有给出正确的结果。ISNULL
您有多种解决方案:
可用于设置字符串参数的类型和大小:
setinputsizes
cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 10, 0)])
进行显式强制转换。这是一个更好的选择,因为它永远不会失败,您可以单独设置每个值。
WHERE ISNULL(CAST(? AS varchar(10)), column1) = column1
您也可以通过先将其设置为正确大小的变量来执行此操作。
将查询重写为不使用 ,无论如何都应该这样做,因为它会阻止使用索引。
ISNULL
- 要么使用需要传递两次参数的注释,要么将其分配给 SQL 中的变量。
OR
sqlStatement2 = ''' SELECT id, column1 FROM test3 WHERE (? = column1 OR ? IS NULL) ORDER BY ID; '''
- 或者在我看来,最好的选择是使查询动态化,以便您预先决定是否按该列进行筛选。
sqlStatement2 = ''' SELECT id, column1 FROM test3 ''' if someValue is not None: sqlStatement2 = sqlStatement2 + '''WHERE ? = column1 ''' sqlStatement2 = sqlStatement2 + '''ORDER BY ID; '''
- 要么使用需要传递两次参数的注释,要么将其分配给 SQL 中的变量。
请注意,在最后两个选项中,设置变量大小可能仍然是理想的选择。
评论
0赞
Kurt
11/13/2023
如果可能的话,我希望尽量远离动态字符串,并让左侧的isnull使用否定任何索引。我不喜欢两次传递相同的参数,甚至尝试了cursor.execute(sqlStatement1,[sqlArgs]*2)语法,但无法使其工作,老实说,我不喜欢它。我知道我可以使用匿名 t-sql 块来做到这一点,但这似乎有点矫枉过正和混乱。我想我会尝试你的选角。感谢您的详细回复。
0赞
Charlieface
11/13/2023
sqlArgs.append(None)
sqlArgs.append(None)
是你会如何做到这一点
0赞
Gord Thompson
11/13/2023
像往常一样,很好的答案。不过,一个可能的澄清:有些人可能会(错误地)解释“[setinputsizes] 为所有字符串变量设置它”意味着整个查询的所有字符串参数都将作为 发送,而实际上它只是该列的字符串参数(基于它在参数列表中的位置)。varchar(10)
1赞
Charlieface
11/13/2023
啊,对不起,我实际上以为这就是它的作用。现已修复。
0赞
Kurt
11/13/2023
比我的OP好多了。 谢谢大家!
评论
WHERE ISNULL(@P1, id) = id
WHERE @P1 = id OR @P1 IS NULL
RECOMPILE
OPTION
WHERE