create FUNCTION [dbo].[ConvertStringToList]


      (@str VARCHAR (MAX), @delimeter CHAR (1))
        RETURNS 
        @result TABLE (
            [ID] INT NULL)
    AS
    BEG

IN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

——你的查询

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

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

有一个很好的、简单的、经过测试的方法:

/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */

CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY  
AS   
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];

我已经开始使用这种方法来修复实体框架的问题(对于我们的应用程序来说不够健壮)。所以我们决定给Dapper(和Stack一样)一个机会。还指定您的字符串列表为表与PK列修复您的执行计划很多。 这是一篇关于如何将一个表转换为Dapper的好文章——都是快速而干净的。

(编辑:如果表值参数不可用) 最好的方法似乎是将大量的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: 参数化SQL -在/不在与固定数量的参数，查询计划缓存优化?

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

EDIT: I noted that IN queries with many values (like 250 or more) still tend to be slow, in the given case, on SQL Server. While I expected the DB to create a kind of temporary table internally and join against it, it seemed like it only repeated the single value SELECT expression n-times. Time was up to about 200ms per query - even worse than joining the original IDs retrieval SELECT against the other, related tables.. Also, there were some 10 to 15 CPU units in SQL Server Profiler, something unusual for repeated execution of the same parameterized queries, suggesting that new query plans were created on repeated calls. Maybe ad-hoc like individual queries are not worse at all. I had to compare these queries to non-split queries with changing sizes for a final conclusion, but for now, it seems like long IN clauses should be avoided anyway.

在SQL SERVER 2016或更高版本中，您可以使用STRING_SPLIT。

DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT 
    * 
FROM 
    Tags T
    INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY 
    Count DESC

我使用这个是因为在我的查询中，有时连接速度比Like操作符快。 此外，您还可以按您喜欢的任何单独格式输入无限数量的数据。 我喜欢这个。

我有一个不需要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 
(
    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);