参数化SQL IN子句

最新推荐文章于 2024-08-09 23:23:34 发布

asdfgh0077

最新推荐文章于 2024-08-09 23:23:34 发布

阅读量2.6k

点赞数

文章标签： sql sql-server parameters

原文链接：https://stackoom.com/question/1Pqq/参数化SQL-IN子句

版权

我该如何参数化包含一个带有可变数量参数的IN子句的查询，就像这样？

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在此查询中，参数的数量可以为1到5之间的任意值。

我不希望为此（或XML）使用专用的存储过程，但是如果有一些特定于SQL Server 2008的优雅方法，我可以接受。

#1楼

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

字符串可以用任何语言构建。在此示例中，我使用了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)

#2楼

使用以下存储过程。它使用自定义拆分功能，可在此处找到。

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

#3楼

如果您使用的是SQL Server 2008或更高版本，则可以使用表值参数。

如果您不幸遇到SQL Server 2005的问题，可以添加这样的CLR函数，

[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 Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

#4楼

如果我们在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 Tags
WHERE CharIndex(Name,@Param)>0

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

提琴手演示

编辑：使用以下脚本创建名为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 Tags
WHERE CharIndex(Name,@Param)>0

#5楼

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

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

和功能：

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
    declare @separator char(1)
    set @separator = ','
    declare @separator_position int
    declare @array_value varchar(MAX)

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0
    begin

        select @separator_position = patindex('%,%' , @array)
        select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as int))
        select @array = stuff(@array, 1, @separator_position, '')
    end
    return
end

#6楼

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

（新版本发布于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 parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

干杯。

#7楼

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

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

#8楼

我使用投票结果最简洁的答案：

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);

#9楼

我认为这是静态查询不是解决之道的情况。动态构建in子句的列表，转义单引号，并动态构建SQL。在这种情况下，由于列表太小，您可能看不到任何方法有什么不同，但是最有效的方法实际上是完全按照您的帖子中编写的方式发送SQL。我认为，以最有效的方式编写代码是一个好习惯，而不是去做最漂亮的代码，或者认为动态构建SQL是一种不好的做法。

我已经看到，在许多参数变大的情况下，拆分函数比查询本身执行所需的时间更长。我会考虑的唯一选择是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。

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

悬崖笔记：

对于您而言，您可以执行任何操作，无论是在列表中使用固定数量的项目进行参数化（如果未使用，则为null），动态构建带有或不带有参数的查询，或使用具有表值参数的存储过程都不会有太大的不同。但是，我的一般建议如下：

您的案例/简单查询，只需几个参数：

动态SQL，如果测试显示更好的性能，则可能带有参数。

具有可重复使用的执行计划的查询，只需更改参数或查询比较复杂即可多次调用：

具有动态参数的SQL。

清单较大的查询：

具有表值参数的存储过程。如果列表可能有很大差异，请在存储过程中使用WITH RECOMPILE，或者仅使用不带参数的动态SQL为每个查询生成新的执行计划。

#10楼

默认情况下，我会通过将表值函数（从字符串返回表）传递给IN条件来实现此目的。

这是UDF的代码（我是从Stack Overflow某处获得的，我现在找不到源）

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

一旦掌握了这一点，您的代码将像下面这样简单：

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

除非您有一个很长的字符串，否则它应该与表索引一起使用。

如果需要，您可以将其插入到临时表中，对其进行索引，然后运行联接...

#11楼

我认为，解决此问题的最佳来源是此网站上发布的内容：

Syscomments。迪纳卡·内蒂（Dinakar Nethi）

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @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 characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @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 string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

采用：

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

致谢：Dinakar Nethi

#12楼

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

这种方法是在OPENJSON中发布的，这是通过ID列表选择行的最佳方法之一。

下面是一个完整的示例

CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
                  FROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESC

GO

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags

#13楼

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

所以你有字符串

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where 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))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @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) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

#14楼

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

#15楼

对于像这样的可变数量的参数，我知道的唯一方法是显式生成SQL或执行某些操作，其中涉及使用所需项填充临时表并针对临时表进行联接。

#16楼

这很麻烦，但是如果您保证至少有一个，则可以执行以下操作：

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

使用SQL Server可以轻松优化拥有IN（'tag1'，'tag2'，'tag1'，'tag1'，'tag1'）的位置。另外，您可以获得直接索引搜索

#17楼

您可以参数化每个值，例如：

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"

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

用户输入的值仍然填充到参数中，因此那里没有漏洞。

编辑：

除了注入问题外，请注意构造命令文本以容纳可变数量的参数（如上所述）会阻碍SQL Server利用缓存查询的能力。最终结果是，您几乎肯定首先失去了使用参数的价值（与仅将谓词字符串插入SQL本身相反）。

并不是说缓存的查询计划不是很有价值，但是IMO这个查询还不够复杂，无法从中受益良多。尽管编译成本可能接近（甚至超过）执行成本，但您仍在谈论毫秒。

如果您有足够的RAM，我希望SQL Server可能也会为通用的参数计数缓存一个计划。我想您总是可以添加五个参数，并且将未指定的标签设为NULL-查询计划应该相同，但是对我来说似乎很难看，而且我不确定是否值得进行微优化（不过，堆栈溢出-可能非常值得）。

而且，SQL Server 7和更高版本将自动对查询进行参数化，因此从性能的角度来看，使用参数并不是真正必要的，但是从安全的角度来看，使用参数至关重要 （尤其是对于用户输入的数据而言）。

#18楼

这是我使用的一种快捷方法：

SELECT * FROM Tags
WHERE '|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 "%...%"查询未编制索引。
确保您没有任何| ，空白或空标签，否则将无法使用

还有一些其他方法可以使某些人认为它更干净，因此请继续阅读。

#19楼

对于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 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

#20楼

我使用过一次，这可能是一种半讨厌的方式，它相当有效。

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

用一列创建一个临时表 。
INSERT每个查找值插入该列。
除了使用IN ，您还可以使用标准的JOIN规则。（灵活性++）

这在您可以执行的操作中增加了一点灵活性，但是它更适合于您要查询的表很大，索引很好并且您希望多次使用参数化列表的情况。省去了执行两次并手动完成所有清洁的麻烦。

我从来没有完全了解它的速度，但是在我的情况下是必需的。

#21楼

我们有创建表变量的函数，您可以将其加入其中：

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT 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) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END

所以：

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

#22楼

在ColdFusion中，我们只做：

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

#23楼

在SQL Server 2016+您可以使用STRING_SPLIT函数：

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;

要么：

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
  ON t.Name = [value]
ORDER BY Count DESC;

LiveDemo

公认的答案当然会起作用，这是解决问题的方法之一，但这是反模式的。

E.按值列表查找行

这可以替代常见的反模式，例如在应用程序层或Transact-SQL中创建动态SQL字符串，或者使用LIKE运算符：
 SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%'; 

^{原始问题有要求SQL Server 2008 。} ^{因为这个问题经常被重复使用，所以我添加了这个答案作为参考。}

#24楼

我今天在播客上听到Jeff / Joel谈到这个问题（ 第34集 ，2008-12-16（MP3，31 MB），1小时03分38秒-1小时06分45秒），我以为我想起了Stack Overflow在使用LINQ to SQL ，但可能已被放弃。这在LINQ to SQL中也是一样。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

而已。而且，是的，LINQ已经足够向后看，但是Contains子句对我而言似乎是额外向后的。当我必须对工作中的项目进行类似的查询时，我自然会尝试通过在本地数组和SQL Server表之间进行联接来以错误的方式执行此操作，从而弄清楚LINQ to SQL转换器将足够聪明来处理翻译莫名其妙。它没有，但是确实提供了描述性的错误消息，并向我指出了使用Contains 。

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

#25楼

IMHO的正确方法是将列表存储在一个字符串中（长度受DBMS支持）。唯一的技巧是（为了简化处理）我在字符串的开头和结尾都有一个分隔符（在我的示例中为逗号）。这个想法是“动态标准化”，将列表变成一个单列表，每个值包含一行。这可以让你转

在（ct1，ct2，ct3 ... ctn）中

变成一个

在（选择...）

或（我可能更喜欢的解决方案）常规联接，如果您只是添加一个“区别”以避免列表中重复值出现问题。

不幸的是，切片字符串的技术是特定于产品的。这是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 names
        from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
        union all
        select (n - 1) as n,
               substring(names, 1 + charindex(',', names), len(names)) as names
        from qry
        where n > 1)
 select n, substring(names, 1, charindex(',', names) - 1) dwarf
 from qry;

Oracle版本：

 select n, substr(name, 1, instr(name, ',') - 1) dwarf
 from (select n,
             substr(val, 1 + instr(val, ',', 1, n)) name
      from (select rownum as n,
                   list.val
            from  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
                   from dual) list
            connect 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 n
     union all
     select 2 as n
     union all
     select 3 as n
     union all
     select 4 as n
     union all
     select 5 as n
     union all
     select 6 as n
     union all
     select 7 as n
     union all
     select 8 as n
     union all
     select 9 as n
     union all
     select 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, ',', ''));

（当然，“数据透视表”必须返回的行数与我们在列表中可以找到的最大项目数一样多）

#26楼

唯一的制胜法宝是不参加比赛。

您没有无限的可变性。只有有限的可变性。

在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 value
  return ExecuteQuery<PublishDate>(getValuesInListSQL, 
               origCount,   
               idList[0], idList[1], idList[2], idList[3], idList[4]);

因此，基本上，如果计数为0，则没有过滤器，一切都会通过。如果计数大于0，则该值必须在列表中，但是该列表已用不可能的值填充为五个（这样，SQL仍然有意义）

有时，me脚的解决方案是唯一有效的解决方案。

#27楼

我有一个不需要UDF，XML的答案，因为IN接受一条select语句，例如SELECT * FROM Test where Data IN（从表中选择SELECT值）

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

这可以通过递归CTE或带有数字表（或Master..spt_value）的查询来完成

这是CTE版本。

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'

SELECT @InputString = @InputString + ','

;WITH RecursiveCSV(x,y) 
AS 
(
    SELECT 
        x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),
        y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))
    UNION ALL
    SELECT 
        x = SUBSTRING(y,0,CHARINDEX(',',y,0)),
        y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))
    FROM 
        RecursiveCSV 
    WHERE
        SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR 
        SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''
)
SELECT
    * 
FROM 
    Tags
WHERE 
    Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);

#28楼

如果从.NET调用，则可以使用Dapper点网：

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

Dapper在这里进行思考，因此您不必这样做。当然，使用LINQ to SQL可能会发生类似的事情：

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

#29楼

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

    declare @x xml
    set @x='<items>
    <item myvalue="29790" />
    <item myvalue="31250" />
    </items>
    ';
    With CTE AS (
         SELECT 
            x.item.value('@myvalue[1]', 'decimal') AS myvalue
        FROM @x.nodes('//items/item') AS x(item) )

    select * from YourTable where tableColumnName in (select myvalue from cte)

#30楼

最初的问题是“如何参数化查询...”

让我在这里声明，这不是对原始问题的答案 。其他好的答案中已经有一些示范。

话虽如此，继续并标记此答案，对其进行否决，将其标记为不是答案...做您认为正确的事情。

请参阅Mark Brackett的答案，以获取我（及其他231个）支持的首选答案。在他的答案中给出的方法允许1）有效使用绑定变量，以及2）可处理谓词。

选择的答案

我想在这里解决的是乔尔·斯波斯基（Joel Spolsky）的答案中给出的方法，即“选择”答案为正确答案。

乔尔·斯波斯基（Joel Spolsky）的做法很聪明。而且它合理地工作，在给定“正常”值的情况下，并且具有规范的边缘情况（例如NULL和空字符串），它将表现出可预测的行为和可预测的性能。对于特定的应用可能就足够了。

但是从广义上来说，我们还要考虑更晦涩的特殊情况，例如当“ Name列包含通配符（由LIKE谓词识别）时，我看到的最常用的通配符是% （百分号）。。因此，让我们现在在这里进行处理，然后再处理其他情况。

％字符的一些问题

考虑名称值'pe%ter' 。（对于此处的示例，我使用文字字符串值代替列名。）查询形式将返回名称值为''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开发人员大喊大叫。）

领域知识

我们可能对列的域有专门的知识（即，为列强制使用的一组允许值。我们可能会先验地知道，存储在列中的值将永远不会包含百分号，下划线或方括号在这种情况下，我们只需要简要评论一下这些案例就可以了。

列中存储的值可能允许使用％或_字符，但是约束可能要求使用定义的字符对这些值进行转义，以使这些值像是比较“安全”的。同样，快速评论一下允许的值集，尤其是将哪个字符用作转义字符，然后采用Joel Spolsky的方法。

但是，由于缺乏专业知识和保证，对我们来说至少要考虑处理那些不起眼的极端情况，并考虑行为是否合理和“符合规范”，这一点很重要。

总结其他问题

我相信其他人已经充分指出了其他一些普遍认为的关注领域：

SQL注入（采用看似由用户提供的信息，而不是通过绑定变量来提供，而是包含在SQL文本中。不需要使用绑定变量，这只是挫败SQL注入的一种便捷方法。还有其他方法处理方法：
使用索引扫描而不是索引查找的优化程序计划，可能需要使用表达式或函数来转义通配符（可能在表达式或函数上进行索引）
使用文字值代替绑定变量会影响可伸缩性

结论

我喜欢Joel Spolsky的方法。很聪明而且有效。

但是，一看到它，我就立即看到它的潜在问题，让它滑动并不是我的本性。我并不是要批评别人的努力。我知道许多开发人员非常重视自己的工作，因为他们投入了大量资金，并且非常在乎它。因此，请理解，这不是人身攻击。我在这里确定的是在生产而不是测试中出现的问题类型。

是的，我已经离开了最初的问题。但是，关于“我认为是对某个问题的“选定”答案很重要的问题”，还有什么地方需要留下此注释？

asdfgh0077

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫