参数化SQL IN子句 - 开卷题库

小开

可以将参数作为字符串传递

这是弦

DECLARE @tags
SET @tags = ‘ruby|rails|scruffy|rubyonrails’
select * from Tagswhere Name in (SELECT item from fnSplit(@tags, ‘|’))order by Count desc

然后你所要做的就是将字符串作为1参数传递。

这是我使用的分裂函数。

CREATE FUNCTION [dbo].[fnSplit](@sInputList VARCHAR(8000) -- List of delimited items, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items) RETURNS @List TABLE (item VARCHAR(8000))
BEGINDECLARE @sItem VARCHAR(8000)WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0BEGINSELECT@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0INSERT INTO @List SELECT @sItemEND
IF LEN(@sInputList) > 0INSERT INTO @List SELECT @sInputList -- Put the last item inRETURNEND

小开

我将传递一个表类型参数(因为它是SQL Server 2008)，并执行where exists，或内部连接。您也可以使用XML，使用sp_xml_preparedocument，然后甚至可以索引该临时表。

小开

对于这样数量可变的参数，我所知道的唯一方法是显式地生成SQL，或者做一些涉及用所需项填充临时表并与临时表连接的事情。

小开

这很恶心，但如果你保证至少有一个，你可以这样做:

SELECT ......WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

有IN('tag1'， 'tag2'， 'tag1'， 'tag1'， 'tag1')将很容易被SQL Server优化掉。另外，你可以直接搜索索引

小开

你可以参数化每一个值，像这样:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
string[] paramNames = tags.Select((s, i) => "@tag" + i.ToString()).ToArray();
string inClause = string.Join(", ", paramNames);using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {for(int i = 0; i < paramNames.Length; i++) {cmd.Parameters.AddWithValue(paramNames[i], tags[i]);}}

这将给你:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"cmd.Parameters["@tag0"] = "ruby"cmd.Parameters["@tag1"] = "rails"cmd.Parameters["@tag2"] = "scruffy"cmd.Parameters["@tag3"] = "rubyonrails"

不，这不是对0号开放的。唯一注入到CommandText中的文本不是基于用户输入的。它完全基于硬编码的“@tag”前缀和数组的索引。索引总是将是一个整数，不是用户生成的，并且是安全的。

用户输入的值仍然被填充到参数中，因此不存在漏洞。

编辑:

除了注入问题外，要注意构造命令文本以容纳可变数量的参数(如上所述)会阻碍SQL服务器利用缓存查询的能力。最终的结果是，您几乎肯定会在第一时间失去使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。

并不是说缓存的查询计划没有价值，但在我看来，这个查询还没有复杂到可以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本，但仍然是毫秒级的。

如果您有足够的RAM，我希望SQL Server可能也会缓存用于常见参数计数的计划。我认为你总是可以添加五个参数，并让未指定的标签为NULL -查询计划应该是相同的，但这对我来说似乎很难看，我不确定它是否值得进行微观优化(尽管，在Stack Overflow上-它可能非常值得)。

而且，Server 7及更高版本将auto-parameterize查询，因此从性能的角度来看，使用参数并不是真正必要的——然而，从安全性的角度来看，它是至关重要的——特别是对于像这样的用户输入数据。

小开

最佳答案

下面是我用过的一个快速而又复杂的技巧:

SELECT * FROM TagsWHERE '|ruby|rails|scruffy|rubyonrails|'LIKE '%|' + Name + '|%'

下面是c#代码:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";
using (SqlCommand cmd = new SqlCommand(cmdText)) {cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);}

两个问题:

演出糟透了。LIKE "%...%"查询没有索引。
确保没有任何|、空白或null标记，否则将无法工作

有些人可能认为还有其他更清洁的方法可以做到这一点，所以请继续阅读。

小开

对于SQL Server 2008，您可以使用表值参数。这有点麻烦，但可以说比第一条更干净。

首先，您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后，你的ADO。NET代码如下所示:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";
// value must be IEnumerable<SqlDataRecord>cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";
// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rowsvar firstRecord = values.First();var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Typereturn values.Select(v =>{var r = new SqlDataRecord(metadata);r.SetValues(v);return r;});}

<强>更新As Per @Doug

请尽量避免var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);

它设置了第一个值的长度，所以如果第一个值是3个字符，那么它设置的最大长度为3，如果超过3个字符，其他记录将被截断。

所以，请尝试使用:var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, maxLen);

注:-1为最大长度。

小开

这可能是一种有点讨厌的方法，我用过一次，相当有效。

根据你的目标，它可能会有用。

创建一个临时表列。
INSERT每个查找值到该列。
您可以使用标准的JOIN规则，而不是使用IN。(灵活性++)

这为您所能做的事情提供了一些额外的灵活性，但它更适合这样的情况:需要查询一个大型表，有良好的索引，并且希望多次使用参数化列表。节省了执行两次，所有的卫生工作都是手动完成的。

我从来没有仔细分析过快到底是怎样的，但在我的情况下，它是需要的。

小开

我们有一个函数，创建一个表变量，你可以加入:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),@delim AS VARCHAR(10))RETURNS @listTable TABLE(Position INT,Value    VARCHAR(8000))ASBEGINDECLARE @myPos INT
SET @myPos = 1
WHILE Charindex(@delim, @list) > 0BEGININSERT INTO @listTable(Position,Value)VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))
SET @myPos = @myPos + 1
IF Charindex(@delim, @list) = Len(@list)INSERT INTO @listTable(Position,Value)VALUES     (@myPos,'')
SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))END
IF Len(@list) > 0INSERT INTO @listTable(Position,Value)VALUES     (@myPos,@list)
RETURNEND

所以:

@Name varchar(8000) = null // parameter for search values
select * from Tagswhere Name in (SELECT value From fn_sqllist_to_table(@Name,',')))order by Count desc

小开

在ColdFusion中，我们只做:

<cfset myvalues = "ruby|rails|scruffy|rubyonrails"><cfquery name="q">select * from sometable where values in <cfqueryparam value="#myvalues#" list="true"></cfquery>

小开

我听到Jeff/Joel今天在播客上谈论这个(# 0,2008-12-16 (MP3, 31 MB)， 1小时03分38秒-1小时06分45秒)，我想我记得Stack Overflow使用LINQ 用SQL，但可能它被抛弃了。在LINQ to SQL中也是如此。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };
var results = from tag in Tagswhere inValues.Contains(tag.Name)select tag;

就是这样。而且，是的，LINQ已经足够向后看了，但是Contains子句对我来说似乎太向后看了。当我在工作中不得不为一个项目做类似的查询时，我自然地试图用错误的方式来做这个，在本地数组和SQL Server表之间做一个连接，认为LINQ to SQL翻译器将足够聪明，以某种方式处理翻译。它没有，但它确实提供了一个描述性的错误消息，并指示我使用包含。

无论如何，如果您在强烈推荐的LINQPad中运行此命令，并运行此查询，您可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个值被参数化为IN子句。

小开

在我看来，正确的方法是将列表存储在字符串中(长度受DBMS支持的限制);唯一的技巧是(为了简化处理)我在字符串的开头和结尾都有一个分隔符(在我的例子中是一个逗号)。其思想是“动态规范化”，将列表转换为单列表，每个值包含一行。这让你可以转弯

In (ct1,ct2, ct3…卡通)

成一个

在(选择…)

或者(我可能更喜欢的解决方案)常规连接，如果你只是添加一个“distinct”来避免列表中重复值的问题。

不幸的是，切片字符串的技术是相当特定于产品的。下面是SQL Server版本:

 with qry(n, names) as(select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,substring(list.names, 2, len(list.names)) as namesfrom (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as listunion allselect (n - 1) as n,substring(names, 1 + charindex(',', names), len(names)) as namesfrom qrywhere n > 1)select n, substring(names, 1, charindex(',', names) - 1) dwarffrom qry;

Oracle版本:

 select n, substr(name, 1, instr(name, ',') - 1) dwarffrom (select n,substr(val, 1 + instr(val, ',', 1, n)) namefrom (select rownum as n,list.valfrom  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' valfrom dual) listconnect by level < length(list.val) -length(replace(list.val, ',', ''))));

MySQL版本:

select pivot.n,substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as nunion allselect 2 as nunion allselect 3 as nunion allselect 4 as nunion allselect 5 as nunion allselect 6 as nunion allselect 7 as nunion allselect 8 as nunion allselect 9 as nunion allselect 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -length(replace(list.val, ',', ''));

(当然，“pivot”必须返回与最大行数相同的行数我们可以在列表中找到的项)

小开

最初的问题是“我如何参数化一个查询”;

这是最初问题的第0题。在其他答案中有一些很好的演示。

请参阅Mark Brackett的第一个答案(第一个以“你可以参数化每个值”开头的答案)和马克·布兰克特的第二个回答，这是我(和其他231人)投票赞成的首选答案。在他的回答中给出的方法允许1)有效地使用绑定变量，2)谓词是sargable。

选择答案

我在这里谈到的是乔尔·斯波尔斯基的回答中给出的方法，答案是“选择”;作为正确答案。

Joel Spolsky的方法很聪明。它的工作是合理的，它将表现出可预测的行为和可预测的性能，假设“正常”;值，并使用规范的边缘情况，如NULL和空字符串。对于特定的应用，它可能是足够的。

但是在概括这种方法方面，让我们还考虑更模糊的情况，比如Name列包含通配符(由like谓词识别)。我看到最常用的通配符是%(百分号)。我们先来解决这个问题，然后再讨论其他情况。

%字符有一些问题

考虑Name值为'pe%ter'。(对于这里的示例，我使用一个字面值字符串值来代替列名。)Name值为" pe%ter'的行将由以下形式的查询返回:

select ...where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但是，如果搜索词的顺序颠倒了，同样的行将返回不:

select ...where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我们观察到的行为有点奇怪。更改列表中搜索词的顺序将更改结果集。

不用说，我们可能不希望pe%ter和花生酱搭配，不管他有多喜欢。

晦涩的角落案例

(是的，我同意这是一个模糊的案例。可能是一个不太可能被测试的。我们不期望列值中有通配符。我们可以假设应用程序阻止存储这样的值。但根据我的经验，我很少看到有数据库约束明确禁止在LIKE比较运算符的右侧出现被认为是通配符的字符或模式。

修补洞

修补此漏洞的一种方法是转义%通配符。(对于不熟悉操作符的转义子句的人，这里有一个到SQL Server文档的链接。

select ...where '|peanut|butter|'like '%|' + 'pe\%ter' + '|%' escape '\'

现在我们可以匹配字面%了。当然，当我们有一个列名时，我们需要动态转义通配符。我们可以使用REPLACE函数来查找% 字符的出现情况，并在每个字符前面插入一个反斜杠字符，如下所示:

select ...where '|pe%ter|'like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

这样就解决了%通配符的问题。几乎。

逃离逃离

我们认识到我们的解决方案引入了另一个问题。转义字符。我们还需要对任何出现的转义字符本身进行转义。这一次，我们使用!作为转义字符:

select ...where '|pe%t!r|'like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

还有下划线

现在，我们可以添加另一个REPLACE处理下划线通配符。只是为了好玩，这次我们将使用$作为转义字符。

select ...where '|p_%t!r|'like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我更喜欢这种方法而不是转义，因为它可以在Oracle和MySQL以及SQL Server中工作。(我通常使用\反斜杠作为转义字符，因为这是正则表达式中使用的字符。但为什么要被传统束缚呢!

这些讨厌的括号

通过将通配符括在括号[]中，SQL Server还允许将通配符视为文字。所以我们还没有完成修复，至少对SQL Server是这样。由于括号对具有特殊含义，我们还需要转义它们。如果我们设法正确地转义括号，那么至少我们不必为括号内的连字符-和克拉^而烦恼。我们可以保留括号内的% 和_字符转义，因为我们基本上已经禁用了括号的特殊含义。

找到匹配的括号对应该没有那么难。这比处理单例%和_的出现要困难一些。(注意，仅仅转义所有出现的方括号是不够的，因为单例方括号被认为是一个文字，不需要转义。逻辑变得有点模糊，如果不运行更多的测试用例，我就无法处理。)

内联表达式变得混乱

SQL中的内联表达式越来越长，越来越难看。我们也许可以让它工作，但上帝保佑那些可怜的灵魂回来，必须破译它。作为内联表达式的粉丝，我在这里倾向于不使用它，主要是因为我不想留下评论解释混乱的原因，并为此道歉。

函数在哪里?

如果我们不把它作为SQL中的内联表达式来处理，我们拥有的最接近的替代方法是用户定义函数。而且我们知道这不会加快任何速度(除非我们可以在上面定义一个索引，就像我们在Oracle中所做的那样)。如果我们必须创建一个函数，最好在调用SQL语句的代码中执行。

该函数可能在行为上有一些差异，这取决于DBMS和版本。(这是对所有热衷于可互换使用任何数据库引擎的Java开发人员的一种呼吁。)

领域知识

我们可能对列的域有专门的知识(也就是说，对列强制执行的允许值集。我们可能知道先天的表示存储在列中的值永远不会包含百分号、下划线或括号对。在这种情况下，我们只包含一个简短的注释，说明这些情况都被涵盖了。

存储在列中的值可能允许使用%或_字符，但约束可能要求转义这些值，可能使用已定义的字符，这样这些值就像compare "safe"。再次，快速评论一下允许的值集，特别是哪个字符被用作转义字符，并遵循Joel Spolsky的方法。

但是，在没有专业知识和保证的情况下，我们至少要考虑处理那些模糊的极端情况，并考虑行为是否合理，是否“符合规范”。

其他问题概述

我相信其他人已经充分指出了其他一些普遍考虑的关切领域:

SQL注入(获取似乎是用户提供的信息，并将其包含在SQL文本中，而不是通过绑定变量提供。使用绑定变量并不是必需的，这只是一种阻止SQL注入的方便方法。还有其他方法可以解决这个问题:
优化器计划使用索引扫描而不是索引查找，可能需要一个表达式或函数来转义通配符(表达式或函数上可能的索引)
使用文字值代替绑定变量会影响可伸缩性

结论

我喜欢Joel Spolsky的方法。这是聪明的。这很有效。

但当我看到它的时候，我立刻发现了潜在的问题，而我的天性不是让它顺其自然。我并不是要批评别人的努力。我知道许多开发者都非常注重自己的工作，因为他们在其中投入了大量精力，并且非常关心自己的工作。所以请理解，这不是人身攻击。我在这里确定的是在生产而不是测试中出现的问题类型。

小开

另一种可能的解决方案是，不向存储过程传递可变数量的参数，而是传递一个包含您要查找的名称的字符串，但通过将它们围绕在'<>'使它们唯一。然后使用PATINDEX查找名称:

SELECT *FROM TagsWHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

小开

我认为在这种情况下，静态查询不是正确的方法。动态构建in子句的列表，转义单引号，并动态构建SQL。在这种情况下，由于列表很小，您可能不会看到任何方法有太大区别，但最有效的方法实际上是发送与文章中所写的完全相同的SQL。我认为以最有效的方式编写SQL是一个好习惯，而不是按照最漂亮的代码来编写，或者认为动态构建SQL是一种糟糕的做法。

在许多参数变大的情况下，我看到split函数的执行时间比查询本身要长。在SQL 2008中带有表值参数的存储过程是我考虑的唯一其他选项，尽管在您的情况下这可能会更慢。只有在搜索TVP的主键时，TVP对于大型列表才可能更快，因为SQL无论如何都会为列表构建一个临时表(如果列表很大)。除非进行测试，否则你无法确定。

我还见过有500个默认值为null的参数的存储过程，并且有WHERE Column1 IN (@Param1， @Param2， @Param3，…@Param500)。这导致SQL构建一个临时表，执行排序/区分，然后执行表扫描而不是索引查找。这实际上就是通过参数化该查询所要做的事情，尽管在足够小的范围内，它不会产生明显的差异。我强烈建议不要在你的in列表中有NULL，因为如果它被更改为NOT in，它将不会像预期的那样发挥作用。您可以动态地构建参数列表，但是您将获得的唯一明显的东西是对象将为您转义单引号。这种方法在应用程序端也稍微慢一些，因为对象必须解析查询才能找到参数。它在SQL上可能更快，也可能更快，因为参数化查询调用sp_prepare、sp_execute的次数取决于您执行查询的次数，然后是sp_unprepare。

重用存储过程或参数化查询的执行计划可能会提高性能，但它会将您锁定在由执行的第一个查询决定的执行计划中。在许多情况下，这对于后续查询可能不太理想。在您的情况下，重用执行计划可能是一个加分项，但它可能根本没有任何区别，因为示例是一个非常简单的查询。

悬崖笔记:

对于您的情况，您所做的任何事情，无论是使用列表中固定数量的项进行参数化(如果不使用则为空)，动态地构建带有或不带有参数的查询，还是使用带有表值参数的存储过程，都不会产生太大的区别。不过，我的一般建议如下:

你的case/简单查询很少参数:

动态SQL，如果测试显示更好的性能，可能会使用参数。

具有可重用执行计划的查询，通过简单地更改参数或如果查询很复杂则调用多次:

带有动态参数的SQL。

带有大列表的查询:

具有表值参数的存储过程。如果列表变化很大，则在存储过程上使用WITH RECOMPILE，或者简单地使用不带参数的动态SQL为每个查询生成新的执行计划。

小开

在我看来，解决这个问题的最佳来源是这个网站上发布的内容:

Syscomments。Dinakar Nethi < / >

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))RETURNS @T Table (col1 varchar(50))ASBEGIN--DECLARE @T Table (col1 varchar(50))-- @Array is the array we wish to parse-- @Separator is the separator charactor such as a commaDECLARE @separator_position INT -- This is used to locate each separator characterDECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned-- For my loop to work I need an extra separator at the end. I always look to the-- left of the separator character for each array value
SET @array = @array + @separator
-- Loop through the string searching for separtor charactersWHILE PATINDEX('%' + @separator + '%', @array) <> 0BEGIN-- patindex matches the a pattern against a stringSELECT @separator_position = PATINDEX('%' + @separator + '%',@array)SELECT @array_value = LEFT(@array, @separator_position - 1)-- This is where you process the values passed.INSERT into @T VALUES (@array_value)-- Replace this select statement with your processing-- @array_value holds the value of this element of the array-- This replaces what we just processed with and empty stringSELECT @array = STUFF(@array, 1, @separator_position, '')ENDRETURNEND

使用:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

致谢:Dinakar Nethi

小开

唯一的制胜方法就是不玩。

对你来说没有无限的可变性。只有有限的可变性。

在SQL中，你有这样一个子句:

and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )

在c#代码中，你可以这样做:

  int origCount = idList.Count;if (origCount > 5) {throw new Exception("You may only specify up to five originators to filter on.");}while (idList.Count < 5) { idList.Add(-1); }  // -1 is an impossible valuereturn ExecuteQuery<PublishDate>(getValuesInListSQL,origCount,idList[0], idList[1], idList[2], idList[3], idList[4]);

基本上，如果count为0，那么就没有过滤器，所有东西都会通过。如果计数高于0，则该值必须在列表中，但列表已被填充为5个不可能的值(因此SQL仍然有意义)

有时蹩脚的解决方案是唯一真正有效的。

小开

我有一个答案，不需要UDF, XML因为IN接受一个选择语句例:SELECT * FROM Test where Data IN (SELECT Value FROM TABLE)

您实际上只需要一种将字符串转换为表的方法。

这可以通过递归CTE或使用数字表(或Master..spt_value)的查询来完成。

这是CTE的版本。

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'
SELECT @InputString = @InputString + ','
;WITH RecursiveCSV(x,y)AS(SELECTx = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))UNION ALLSELECTx = SUBSTRING(y,0,CHARINDEX(',',y,0)),y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))FROMRecursiveCSVWHERESUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' ORSUBSTRING(y,0,CHARINDEX(',',y,0)) <> '')SELECT*FROMTagsWHEREName IN (select x FROM RecursiveCSV)OPTION (MAXRECURSION 32767);

小开

如果你从。net调用，你可以使用花斑网:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};var tags = dataContext.Query<Tags>(@"select * from Tagswhere Name in @namesorder by Count desc", new {names});

这里是达普在思考，所以你不用思考。当然，LINQ 用SQL也有类似的情况:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};var tags = from tag in dataContext.Tagswhere names.Contains(tag.Name)orderby tag.Count descendingselect tag;

小开

也许我们可以在这里使用XML:

    declare @x xmlset @x='<items><item myvalue="29790" /><item myvalue="31250" /></items>';With CTE AS (SELECTx.item.value('@myvalue[1]', 'decimal') AS myvalueFROM @x.nodes('//items/item') AS x(item) )
select * from YourTable where tableColumnName in (select myvalue from cte)

小开

下面是一种技术，用于重新创建查询字符串中使用的本地表。这样做可以消除所有解析问题。

字符串可以用任何语言构建。在本例中，我使用SQL，因为这是我试图解决的原始问题。我需要一种干净的方法来在一个字符串中传递表数据，以便稍后执行。

使用用户定义的类型是可选的。创建类型只创建一次，并且可以提前完成。否则，只需在字符串中的声明中添加一个完整的表类型。

通用模式易于扩展，可用于传递更复杂的表。

-- Create a user defined type for the list.CREATE TYPE [dbo].[StringList] AS TABLE([StringValue] [nvarchar](max) NOT NULL)
-- Create a sample list using the list table type.DECLARE @list [dbo].[StringList];INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')
-- Build a string in which we recreate the list so we can pass it to exec-- This can be done in any language since we're just building a string.DECLARE @str nvarchar(max);SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '
-- Add all the values we want to the string. This would be a loop in C++.SELECT @str = @str + '(''' + StringValue + '''),' FROM @list
-- Remove the trailing comma so the query is valid sql.SET @str = substring(@str, 1, len(@str)-1)
-- Add a select to test the string.SET @str = @str + '; SELECT * FROM @list;'
-- Execute the string and see we've pass the table correctly.EXEC(@str)

小开

请使用以下存储过程。它使用了一个自定义拆分函数，可以在在这里中找到。

 create stored procedure GetSearchMachingTagNames@PipeDelimitedTagNames varchar(max),@delimiter char(1)asbeginselect * from Tagswhere Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter)end

小开

这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组，并理解null和空数组。

c# &T-SQL string[]打包/解包实用函数

然后可以连接到表值函数。

小开

如果你有SQL Server 2008或更晚，我会使用表取值参数。

如果你不幸被困在0号上，你可以添加一个1号函数，像这样，

[SqlFunction(DataAccessKind.None,IsDeterministic = true,SystemDataAccess = SystemDataAccessKind.None,IsPrecise = true,FillRowMethodName = "SplitFillRow",TableDefinintion = "s NVARCHAR(MAX)"]public static IEnumerable Split(SqlChars seperator, SqlString s){if (s.IsNull)return new string[0];
return s.ToString().Split(seperator.Buffer);}
public static void SplitFillRow(object row, out SqlString s){s = new SqlString(row.ToString());}

你可以这样用，

declare @desiredTags nvarchar(MAX);set @desiredTags = 'ruby,rails,scruffy,rubyonrails';
select * from Tagswhere Name in [dbo].[Split] (',', @desiredTags)order by Count desc

小开

如果在IN子句中存储了以逗号(，)分隔的字符串，则可以使用charindex函数获取值。如果使用. net，则可以使用SqlParameters进行映射。

DDL脚本:

CREATE TABLE Tags([ID] int, [Name] varchar(20));
INSERT INTO Tags([ID], [Name])VALUES(1, 'ruby'),(2, 'rails'),(3, 'scruffy'),(4, 'rubyonrails');

t - sql:

DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM TagsWHERE CharIndex(Name,@Param)>0

您可以在. net代码中使用上述语句并将参数映射为SqlParameter。

< a href = " http://sqlfiddle.com/ !3/31994/3" rel="noreferrer">小提琴手演示 .

<强>编辑:使用下面的脚本创建名为SelectedTags的表

DDL脚本:

Create table SelectedTags(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')

t - sql:

DECLARE @list nvarchar(max)SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM TagsWHERE CharIndex(Name,@Param)>0

小开

这里有另一种选择。只需将一个以逗号分隔的列表作为字符串参数传递给存储过程，然后:

CREATE PROCEDURE [dbo].[sp_myproc]@UnitList varchar(MAX) = '1,2,3'ASselect column from tablewhere ph.UnitID in (select * from CsvToInt(@UnitList))

函数:

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))returns @IntTable table(IntValue int)ASbegindeclare @separator char(1)set @separator = ','declare @separator_position intdeclare @array_value varchar(MAX)
set @array = @array + ','
while patindex('%,%' , @array) <> 0begin
select @separator_position = patindex('%,%' , @array)select @array_value = left(@array, @separator_position - 1)
Insert @IntTableValues (Cast(@array_value as int))select @array = stuff(@array, 1, @separator_position, '')endreturnend

小开

这是这个问题的另一个答案。

(新版本发布于6/4/13)。

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars){var ds = new DataSet();using (var sqlConn = new SqlConnection(scsb.ConnectionString)){var sqlParameters = new List<SqlParameter>();var replacementStrings = new Dictionary<string, string>();if (pars != null){for (int i = 0; i < pars.Length; i++){if (pars[i] is IEnumerable<object>){List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());}else{sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));}}}strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));using (var sqlCommand = new SqlCommand(strSql, sqlConn)){if (pars != null){sqlCommand.Parameters.AddRange(sqlParameters.ToArray());}else{//Fail-safe, just in case a user intends to pass a single null parametersqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));}using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand)){sqlDataAdapter.Fill(ds);}}}return ds;}

欢呼。

小开

使用动态查询。前端只生成所需的格式:

DECLARE @invalue VARCHAR(100)SELECT @invalue = '''Bishnu'',''Gautam'''
DECLARE @dynamicSQL VARCHAR(MAX)SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'EXEC (@dynamicSQL)

< a href = " http://sqlfiddle.com/ !3/d41d8/27475" rel="nofollow">SQL小提琴 .

小开

(编辑:如果表值参数不可用)最好的方法似乎是将大量的IN参数分割为多个固定长度的查询，这样您就有了许多具有固定参数计数的已知SQL语句，并且没有虚值/重复值，也没有对字符串、XML等进行解析

下面是我用c#写的一些关于这个主题的代码:

public static T[][] SplitSqlValues<T>(IEnumerable<T> values){var sizes = new int[] { 1000, 500, 250, 125, 63, 32, 16, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1 };int processed = 0;int currSizeIdx = sizes.Length - 1; /* start with last (smallest) */var splitLists = new List<T[]>();
var valuesDistSort = values.Distinct().ToList(); /* remove redundant */valuesDistSort.Sort();int totalValues = valuesDistSort.Count;
while (totalValues > sizes[currSizeIdx] && currSizeIdx > 0)currSizeIdx--; /* bigger size, by array pos. */
while (processed < totalValues){while (totalValues - processed < sizes[currSizeIdx])currSizeIdx++; /* smaller size, by array pos. */var partList = new T[sizes[currSizeIdx]];valuesDistSort.CopyTo(processed, partList, 0, sizes[currSizeIdx]);splitLists.Add(partList);processed += sizes[currSizeIdx];}return splitLists.ToArray();}

(你可能有进一步的想法，省略排序，使用valuesDistSort.Skip(processed). take (size[…])而不是list/array CopyTo)。

当插入参数变量时，您可以创建如下内容:

foreach(int[] partList in splitLists){/* here: question mark for param variable, use named/numbered params if required */string sql = "select * from Items where Id in("+ string.Join(",", partList.Select(p => "?"))+ ")"; /* comma separated ?, one for each partList entry */
/* create command with sql string, set parameters, execute, merge results */}

我观察过NHibernate对象关系映射器生成的SQL(当查询数据并从中创建对象时)，它在多个查询下看起来最好。在NHibernate中，可以指定批处理大小;如果需要获取许多对象数据行，它将尝试检索与批处理大小相等的行数

SELECT * FROM MyTable WHERE Id IN (@p1, @p2, @p3, ... , @p[batch-size])

，而不是发送数百或数千

SELECT * FROM MyTable WHERE Id=@id

当剩余的id小于批处理大小，但仍然大于一个时，它会分割成更小的语句，但仍然具有一定的长度。

如果批处理大小为100，查询有118个参数，它将创建3个查询:

一个有100个参数(批量大小)，
然后是12个
另一个是6，

但没有一个是118或18。通过这种方式，它将可能的SQL语句限制为可能的已知语句，防止太多不同的查询计划，从而填充缓存，并且大部分永远不会被重用。上面的代码做了同样的事情，但是长度为1000、500、250、125、63、32、16、10到1。超过1000个元素的参数列表也会被分割，以防止由于大小限制而导致的数据库错误。

无论如何，最好有一个直接发送参数化SQL的数据库接口，而不需要单独的Prepare语句和句柄来调用。像SQL Server和Oracle这样的数据库通过字符串相等来记住SQL(值会改变，绑定SQL中的参数不会!)并重用查询计划(如果可用的话)。不需要单独的prepare语句，也不需要在代码中维护查询句柄!ADO。NET是这样工作的，但是Java似乎仍然使用prepare/execute by句柄(不确定)。

关于这个话题，我有我自己的问题，最初建议用重复的IN子句填充，但后来更喜欢NHibernate风格语句split:# 0 < / p >

这个问题仍然很有趣，即使在被问了5年多之后……

编辑:我注意到，在给定的情况下，在SQL Server上，有很多值的IN查询(比如250或更多)仍然倾向于很慢。虽然我希望DB在内部创建一种临时表并对其进行连接，但它似乎只重复了n次单个值SELECT表达式。每次查询所需的时间大约为200ms——甚至比将原始id检索SELECT与其他相关表连接还要糟糕。此外，在SQL Server Profiler中有大约10到15个CPU单元，这对于重复执行相同的参数化查询来说是不寻常的，这表明在重复调用中创建了新的查询计划。也许像个别查询这样的特别查询一点也不差。为了得出最终结论，我不得不将这些查询与大小变化的非分割查询进行比较，但目前看来，无论如何都应该避免长IN子句。

小开

我使用更简洁的版本投票最多的答案:

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();
var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

它循环两次标记参数;但大多数时候这并不重要(它不会成为你的瓶颈;如果是，展开循环)。

如果你真的对性能感兴趣，不想重复循环两次，这里有一个不太漂亮的版本:

var parameters = new List<SqlParameter>();var paramNames = new List<string>();for (var i = 0; i < tags.Length; i++){var paramName = "@tag" + i;
//Include size and set value explicitly (not AddWithValue)//Because SQL Server may use an implicit conversion if it doesn't know//the actual size.var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; }paramNames.Add(paramName);parameters.Add(p);}
var inClause = string.Join(",", paramNames);

小开

    create FUNCTION [dbo].[ConvertStringToList]

(@str VARCHAR (MAX), @delimeter CHAR (1))RETURNS@result TABLE ([ID] INT NULL)ASBEG
IN
DECLARE @x XMLSET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
INSERT INTO @resultSELECT DISTINCT x.i.value('.', 'int') AS tokenFROM @x.nodes('//t') x(i)ORDER BY 1
RETURNEND

——你的查询

select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])

小开

在默认情况下，我将通过向IN条件传递一个表值函数(从字符串返回一个表)来实现这一点。

下面是udf# 0的代码

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))RETURNS tableASRETURN (WITH Pieces(pn, start, stop) AS (SELECT 1, 1, CHARINDEX(@sep, @s)UNION ALLSELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)FROM PiecesWHERE stop > 0)SELECTSUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS sFROM Pieces)

一旦你得到了这个，你的代码就会像这样简单:

select * from Tagswhere Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))order by Count desc

除非你有一个长得离谱的字符串，否则这应该与表索引一起工作得很好。

如果需要，你可以把它插入一个临时表，索引它，然后运行一个连接…

小开

在SQL Server 2016+中，另一种可能性是使用# 0函数。

这种方法在博客OPENJSON—按id列表选择行的最佳方法之一中有介绍。

下面是一个完整的示例

CREATE TABLE dbo.Tags(Name  VARCHAR(50),Count INT)
INSERT INTO dbo.TagsVALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)
GO
CREATE PROC dbo.SomeProc@Tags VARCHAR(MAX)ASSELECT T.*FROM   dbo.Tags TWHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_ASFROM   OPENJSON(CONCAT('[', @Tags, ']')) J)ORDER  BY T.Count DESC
GO
EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'
DROP TABLE dbo.Tags

小开

您可以通过以下方法以可重用的方式完成此任务-

public static class SqlWhereInParamBuilder{public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters){string[] parameterNames = parameters.Select((paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()).ToArray();
string inClause = string.Join(",", parameterNames);string whereInClause = string.Format(partialClause.Trim(), inClause);
return whereInClause;}
public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters){string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
string[] parameterNames = parameterValues.Select((paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()).ToArray();
for (int i = 0; i < parameterNames.Length; i++){cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);}}}

要了解更多细节，请查看这篇博客文章参数化SQL WHERE IN子句c#

小开

在SQL Server 2016+中，你可以使用# 1函数:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT *FROM TagsWHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))ORDER BY [Count] DESC;

或者:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT t.*FROM Tags tJOIN STRING_SPLIT(@names,',')ON t.Name = [value]ORDER BY [Count] DESC;

LiveDemo

接受的答案当然可以工作，这是一种方法，但它是反模式的。

E.按值列表查找行

这是对常见反模式的替代，例如在应用层或Transact-SQL中创建动态SQL字符串，或者使用LIKE操作符:
SELECT ProductId, Name, TagsFROM ProductWHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

# 0:

为了改进STRING_SPLIT表函数行估计，将分离的值实体化为临时表/表变量是一个好主意:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';
CREATE TABLE #t(val NVARCHAR(120));INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;
SELECT *FROM Tags tgJOIN #t tON t.val = tg.TagNameORDER BY [Count] DESC;

SEDE -现场演示