这两个查询之间的最佳实践 [英] Best practice between these two queries

问题描述

我昨天参加了一个用户组会议，他们指出使用参数化查询比对查询进行硬编码更好.这让我开始思考，这有什么好处吗(显然比这大得多):

I was in a user group meeting yesterday and they pointed out that using parameterized queries is better than harcoding the query. That got me to thinking, does this do anything beneficial(obviously on a much bigger scale than this though):

DECLARE @Client1 UNIQUEIDENTIFIER,
@Client2 UNIQUEIDENTIFIER
SET @ClientId1 ='41234532-2342-3456-3456-123434543212';
SET @ClientId2 = '12323454-3432-3234-5334-265456787654';

SELECT ClientName
FROM dbo.tblclient
WHERE id IN (@Client1,@Client2)

相反:

SELECT ClientName
FROM dbo.tblclient
WHERE id IN ('41234532-2342-3456-3456-123434543212','12323454-3432-3234-5334-265456787654')

推荐答案

如果您的 IN 列表不时更改，则参数化查询和 IN 子句实际上并不是一起实现的时间.

Parametrized queries and IN clause are actually not trivially implemented together if your IN list changes from time to time.

阅读这个问题和答案:参数化 SQL IN 子句

参数，按照设计，只有一个值.除此之外的所有其他内容都必须手动实现，并考虑到安全问题，例如 SQL 注入.

Parameters, by design, are one value only. Everything else other than that must be manually implemented, having in mind security issues, such as SQL Injection.

从性能角度来看，参数化查询将获得更好的性能，特别是如果重复运行相同的查询，但具有不同的参数值.但是，如果您有一个动态 IN 列表(有时是 2 个项目，有时是 3 个)，您可能无法获得使用参数化查询的优势.

From a performance perspective, you will have better performance for parametrized queries, specially if a same query is ran repeatedly, but with different parameters values. However, if you have a dynamic IN list (sometimes 2 items, sometimes 3), you might not get the advantage of using parametrized queries.

不过不要失去希望.有些人已经能够实现(参数化查询和 IN 子句).不过，这也不是小事.

Do not lose hope, though. Some folks have been able to implement that (Parametrized queries and IN clause). It's, again, not trivial, though.

这篇关于这两个查询之间的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！