Как мне параметризовать запрос, содержащий предложение IN с переменным числом аргументов, вроде этого?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
В этом запросе количество аргументов может быть от 1 до 5.
Я бы предпочел не использовать выделенную хранимую процедуру для этого (или XML), но если есть какой-то элегантный способ, характерный для SQL Server 2008, Я открыт для этого.
Здесь используется быстрый и грязный метод:
SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'
Итак, здесь код С#:
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 "%...%" запросы не индексируются.|, blank или null, иначе это не будет работать.Есть и другие способы сделать это, чтобы некоторые люди могли считать более чистыми, поэтому, пожалуйста, продолжайте читать.
Вы можете параметризовать каждое значение, например:
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 injection. Единственный введенный текст в CommandText не основан на пользовательском вводе. Он основан исключительно на жестко закодированном префиксе "@tag" и индексе массива. Индекс всегда будет целым числом, не генерируется пользователем и безопасен.
Пользовательские введенные значения по-прежнему вставляются в параметры, поэтому там нет уязвимости.
Edit:
Отказ от инъекций, обратите внимание на то, что построение текста команды для размещения переменного количества параметров (как указано выше) препятствует возможности SQL-сервера использовать кешированные запросы. Конечным результатом является то, что вы почти наверняка потеряете значение использования параметров в первую очередь (в отличие от просто вставки предикатных строк в сам SQL).
Не то, что кэшированные планы запросов не являются ценными, но IMO этот запрос не является достаточно сложным, чтобы увидеть большую выгоду от него. Хотя затраты на компиляцию могут приближаться (или даже превышать) затраты на выполнение, вы все еще говорите миллисекунды.
Если у вас достаточно ОЗУ, я бы ожидал, что SQL Server, вероятно, будет кэшировать план для общего количества параметров. Я полагаю, вы всегда можете добавить пять параметров, и пусть неопределенные теги будут NULL - план запросов должен быть одним и тем же, но для меня это кажется довольно уродливым, и я не уверен, что это стоило бы микро-оптимизации (хотя, на Qaru - это может быть очень полезно).
Кроме того, SQL Server 7 и более поздние версии будут автоматически задавать параметры, поэтому использование параметров не обязательно необходимо с точки зрения производительности - это однако имеет решающее значение с точки зрения безопасности - особенно с данными пользователя, введенными таким образом.
Для SQL Server 2008 вы можете использовать параметр table value value. Это небольшая работа, но она, возможно, более чистая, чем мой другой метод.
Сначала вы должны создать тип
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;
});
}
Первоначальный вопрос: "Как мне параметризовать запрос..."
Позвольте мне сказать здесь, что это не ответ на исходный вопрос. Уже есть некоторые примеры этого в других хороших ответах.
Сказав это, продолжайте и отметьте этот ответ, понизите его, отметьте как не ответ... делайте то, что считаете правильным.
См. ответ от Марка Брэкетта за предпочтительный ответ, который я (и 231 других) поддержал. Подход, приведенный в его ответе, позволяет 1) эффективно использовать переменные связывания и 2) для предикатов, которые могут быть защищены.
Выбранный ответ
То, что я хочу здесь затронуть, - это подход, приведенный в ответе Джоэла Спольского, ответ "выбран" как правильный ответ.
Подход Джоэла Спольского умный. И он работает разумно, он будет демонстрировать предсказуемое поведение и прогнозируемую производительность, учитывая "нормальные" значения, и с нормативными случаями краев, такими как NULL и пустая строка. И этого может быть достаточно для конкретного приложения.
Но в терминах, обобщающих этот подход, давайте также рассмотрим более неясные угловые случаи, например, когда столбец Name содержит подстановочный знак (как признано предикатом LIKE). Символом подстановки, который я вижу наиболее часто используемым, является % (знак процента). Поэтому давайте поговорим об этом здесь, а затем перейдем к другим случаям.
Некоторые проблемы с символом%
Рассмотрим значение имени 'pe%ter'. (Для примеров здесь я использую буквальное строковое значение вместо имени столбца.) Строка с именем Name `'pe% ter' будет возвращена запросом формы:
select ...
where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'
Но эта же строка будет не возвращена, если порядок поисковых терминов будет отменен:
select ...
where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'
Поведение, которое мы наблюдаем, является нечетным. Изменение порядка поисковых запросов в списке изменяет набор результатов.
Понятно, что мы не хотим, чтобы pe%ter соответствовало арахисовому маслам, независимо от того, насколько он ему нравится.
Неисправный угловой случай
(Да, я соглашусь с тем, что это неясный случай. Вероятно, тот, который вряд ли будет проверен. Мы не ожидаем подстановки в значении столбца. Мы можем предположить, что приложение предотвращает такое значение но по моему опыту я редко видел ограничение базы данных, которое специально запрещало символы или шаблоны, которые считались бы подстановочными знаками в правой части оператора сравнения LIKE.
Исправление дыры
Один из подходов к исправлению этого отверстия состоит в том, чтобы избежать символа подстановки %. (Для тех, кто не знаком с предложением escape на операторе, здесь ссылка на документацию по SQL Server.
select ...
where '|peanut|butter|'
like '%|' + 'pe\%ter' + '|%' escape '\'
Теперь мы можем сопоставить литерал%. Конечно, когда у нас есть имя столбца, нам нужно будет динамически избежать шаблона. Мы можем использовать функцию REPLACE для поиска вхождений символа % и вставки символа обратной косой черты перед каждым из них, например:
select ...
where '|pe%ter|'
like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'
Итак, это решает проблему с помощью шаблона%. Почти.
Побег из побега
Мы понимаем, что наше решение ввело другую проблему. Эквивалентный символ. Мы видим, что нам также нужно избегать любых проявлений самого escape-символа. На этот раз мы используем! как escape-символ:
select ...
where '|pe%t!r|'
like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'
Символ подчеркивания
Теперь, когда мы в рулоне, мы можем добавить еще один REPLACE дескриптор подчёркивания подчеркивания. И просто для удовольствия, на этот раз мы будем использовать $в качестве escape-символа.
select ...
where '|p_%t!r|'
like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'
Я предпочитаю этот подход к экранированию, потому что он работает в Oracle и MySQL, а также в SQL Server. (Обычно я использую \backslash как escape-символ, так как этот символ мы используем в регулярных выражениях. Но зачем ограничиваться конвенцией!
Эти пристыдные скобки
SQL Server также позволяет использовать подстановочные символы как литералы, заключая их в скобки []. Таким образом, мы еще не закончили исправление, по крайней мере, для SQL Server. Поскольку пары скобок имеют особый смысл, нам также нужно избегать их. Если нам удастся сбежать из скобок, то по крайней мере нам не придется беспокоиться о дефисах - и карате ^ в скобках. И мы можем оставить любые символы % и _ внутри скобок, экранированных, так как мы в основном отключили особое значение скобок.
Поиск совпадающих пар скобок не должен быть таким сложным. Это немного сложнее, чем обработка появления singleton% и _. (Обратите внимание, что этого недостаточно, чтобы просто избежать всех вхождений скобок, потому что одноэлементная скобка считается литералом и не нуждается в экранировании. Логика становится немного более сложной, чем я могу справиться, не запуская больше тестовых примеров.)
Встроенное выражение становится беспорядочным
Это встроенное выражение в SQL становится длиннее и уродливее. Мы можем, возможно, заставить его работать, но небеса помогают бедному духу, который приходит и должен его расшифровать. Поскольку я поклонник встроенных выражений, я склонен не использовать его здесь, главным образом потому, что я не хочу оставлять комментарий, объясняющий причину беспорядка, и извиняясь за него.
Функция, где?
Хорошо, поэтому, если мы не будем обрабатывать это как встроенное выражение в SQL, ближайшая альтернатива у нас есть пользовательская функция. И мы знаем, что это не ускорит работу (если мы не сможем определить индекс на нем, как мы могли бы с Oracle.) Если нам нужно создать функцию, мы можем сделать это в коде, который вызывает SQL утверждение.
И эта функция может иметь некоторые отличия в поведении, зависящие от СУБД и версии. (Выкрикните всем, что вы, разработчики Java, очень заинтересованы в возможности использовать любой движок базы данных взаимозаменяемо.)
Знание домена
Мы можем иметь специализированное знание домена для столбца (т.е. набор допустимых значений, применяемых для столбца. Мы можем знать априори, что значения, хранящиеся в столбце, никогда не будут содержать знак процента, символ подчеркивания, или пары скобок. В этом случае мы просто включаем быстрый комментарий о том, что эти случаи охватываются.
Значения, хранящиеся в столбце, могут содержать символы% или _, но ограничение может потребовать, чтобы эти значения были экранированы, возможно, с использованием определенного символа, так что значения LIKE сравнения "безопасны". Опять же, быстрый комментарий о разрешенном наборе значений, и в частности, какой символ используется в качестве escape-символа, и идти с подходом Джоэла Спольского.
Но, не имея специализированных знаний и гарантии, важно, чтобы мы, по крайней мере, рассмотрели возможность обработки этих неясных краевых дел и рассмотрели, разумно ли поведение и "в спецификации".
Другие проблемы, переформулированные
Я считаю, что другие уже достаточно подробно указали на некоторые из наиболее часто встречающихся проблемных вопросов:
SQL-инъекция (с учетом того, что, по-видимому, представляет собой предоставленную пользователем информацию, и в том числе в тексте SQL, а не в предоставлении их через связывание переменных. Использование переменных привязки не требуется, это всего лишь один удобный подход для предотвращения SQL-инъекций. Существуют и другие способы борьбы с ним:
оптимизационный план с использованием сканирования индекса, а не поиска индекса, возможная потребность в выражении или функции для экранирования подстановочных знаков (возможный индекс для выражения или функции)
используя литеральные значения вместо переменных связывания, влияет на масштабируемость
Заключение
Мне нравится подход Джоэла Спольского. Это умно. И это работает.
Но как только я увидел это, я сразу же увидел потенциальную проблему с этим, и это не моя натура, чтобы он скользил. Я не хочу критиковать усилия других. Я знаю, что многие разработчики берут свою работу очень лично, потому что они вкладывают в нее столько денег, и они так много заботятся об этом. Поэтому, пожалуйста, поймите, это не личное нападение. То, что я идентифицирую здесь, - это тип проблемы, возникающей в процессе производства, а не тестирования.
Да, я ушел далеко от исходного вопроса. Но где еще оставить эту заметку относительно того, что я считаю важной проблемой с "выбранным" ответом на вопрос?
Вы можете передать параметр в виде строки
Итак, у вас есть строка
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
Я слышал об этом Джеффе/Джоеле сегодня в подкасте (эпизод 34, 2008-12-16 (MP3, 31 MB), 1 ч. 03 мин. 38 сек. - 1 ч. 06 мин. 45 сек.), И я подумал, что вспомнил, что Qaru использовал 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-переводчик был бы достаточно умен, чтобы обрабатывайте перевод как-то. Это не так, но оно предоставило сообщение об ошибке, которое было описательным, и указало мне на использование Содержит.
В любом случае, если вы запустите это в рекомендуемом LINQPad и запустите этот запрос, вы можете просмотреть фактический SQL, что SQL Создан поставщик LINQ. Он покажет вам каждое значение, которое будет параметризовано в предложение IN.
Если вы звоните из.NET, вы можете использовать Dapper dot net:
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});
Здесь Даппер думает, так что вам не нужно. Что-то подобное возможно с 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;
Это, возможно, наполовину неприятный способ сделать это, я использовал его один раз, был довольно эффективным.
В зависимости от ваших целей это может пригодиться.
INSERT каждое значение поиска в этом столбце.IN вы можете просто использовать стандартные JOIN правила. (Гибкость ++)Это немного повышает гибкость в том, что вы можете сделать, но больше подходит для ситуаций, когда у вас есть большая таблица для запроса, с хорошей индексацией, и вы хотите использовать параметризованный список более одного раза. Сохраняет необходимость выполнить его дважды и выполнить всю санитарию вручную.
Я никогда не добирался до профилирования точно, как быстро это было, но в моей ситуации это было необходимо.
У нас есть функция, которая создает переменную таблицы, к которой вы можете присоединиться:
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
Это грубо, но если у вас есть хотя бы один, вы можете сделать:
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
Наличие IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') будет легко оптимизировано SQL Server. Кроме того, вы получаете прямой поиск индекса
Я бы передал параметр типа таблицы (так как SQL Server 2008) и выполните where exists или внутреннее соединение. Вы также можете использовать XML, используя sp_xml_preparedocument, а затем даже индексировать эту временную таблицу.
На мой взгляд, лучший источник для решения этой проблемы - это то, что было опубликовано на этом сайте:
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', ',')
КРЕДИТЫ ДЛЯ: Динакар Нети
В 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;
Принятый ответ, конечно, сработает, и это один из способов, но это анти-шаблон.
E. Найти строки по списку значений
Это замена для обычного анти-паттерна, такого как создание динамической строки SQL на уровне приложения или Transact-SQL, или с помощью оператора LIKE:
SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';
SQL Server 2008.Поскольку этот вопрос часто используется как дубликат, я добавил этот ответ в качестве ссылки. Правильный способ ИМХО состоит в том, чтобы сохранить список в символьной строке (ограниченной по длине тем, что поддерживает СУБД); Единственный трюк в том, что (для упрощения обработки) у меня есть разделитель (запятая в моем примере) в начале и в конце строки. Идея состоит в том, чтобы "нормализовать" на лету ", превратив список в таблицу с одним столбцом, содержащую одну строку на каждое значение. Это позволяет вам поворачивать
in (ct1, ct2, ct3... ctn)
в
in (select...)
или (решение, которое я, вероятно, предпочтет), регулярное соединение, если вы просто добавите "отдельный", чтобы избежать проблем с повторяющимися значениями в списке.
К сожалению, методы среза строки являются довольно специфичными для продукта. Вот версия 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, ',', ''));
(Конечно, "поворот" должен возвращать столько строк, сколько максимальное число элементы, которые мы можем найти в списке)
Если у вас есть SQL Server 2008 или более поздняя версия, я бы использовал таблицу Table Valued Параметр.
Если вам не повезло застрять на 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
Я думаю, что это случай, когда статический запрос просто не подходит. Динамически создайте список для вашего предложения, избегайте одиночных кавычек и динамически создавайте SQL. В этом случае вы, вероятно, не увидите большой разницы с каким-либо методом из-за небольшого списка, но наиболее эффективным методом является отправка SQL точно так, как написано в вашем сообщении. Я думаю, что это хорошая привычка писать его наиболее эффективным способом, а не делать то, что делает самый красивый код, или рассматривать его плохую практику для динамического создания SQL.
Я видел, что функции split выполняют больше времени, чем сам запрос, во многих случаях, когда параметры становятся большими. Хранимая процедура с параметрами таблицы в SQL 2008 является единственным другим вариантом, который я бы рассмотрел, хотя это, вероятно, будет медленнее в вашем случае. TVP, вероятно, будет быстрее только для больших списков, если вы ищете первичный ключ TVP, потому что SQL будет строить временную таблицу для списка в любом случае (если список большой). Вы не будете знать наверняка, если не проверите его.
Я также видел хранимые процедуры, в которых было 500 параметров со значениями по умолчанию, равными нулю, и имея WHERE Column1 IN (@Param1, @Param2, @Param3,..., @Param500). Это заставило SQL построить временную таблицу, выполнить сортировку/отчет, а затем выполнить сканирование таблицы вместо поиска индекса. Это, по сути, то, что вы делаете, параметризируя этот запрос, хотя и в достаточно малых масштабах, что он не будет иметь заметной разницы. Я настоятельно рекомендую не иметь NULL в ваших списках IN, как если бы он был изменен на NOT IN, он не будет действовать так, как предполагалось. Вы можете динамически строить список параметров, но единственная очевидная вещь, которую вы получите, заключается в том, что объекты будут избегать одиночных кавычек для вас. Этот подход также немного медленнее в конце приложения, так как объекты должны анализировать запрос, чтобы найти параметры. Это может быть или не быть быстрее на SQL, поскольку параметризованные запросы вызывают sp_prepare, sp_execute за столько раз, сколько вы выполняете запрос, за которым следует sp_unprepare.
Повторное использование планов выполнения хранимых процедур или параметризованных запросов может дать вам прирост производительности, но он заблокирует вас до одного плана выполнения, определенного первым выполняемым запросом. Во многих случаях это может быть менее идеальным для последующих запросов. В вашем случае повторное использование планов выполнения, вероятно, будет плюсом, но это может не иметь никакого значения, поскольку пример является действительно простым запросом.
Примечания к скалам:
Для вашего дела все, что вы делаете, будь то параметризация с фиксированным количеством элементов в списке (null, если не используется), динамическое построение запроса с параметрами или без него или использование хранимых процедур с параметрами таблицы, не будет сильно разницы. Однако мои общие рекомендации заключаются в следующем:
Ваши случайные/простые запросы с несколькими параметрами:
Динамический SQL, возможно, с параметрами, если тестирование показывает лучшую производительность.
Запросы с многократными планами выполнения, называемые несколько раз простым изменением параметров или сложностью запроса:
SQL с динамическими параметрами.
Запросы с большими списками:
Сохраненная процедура с параметрами таблицы. Если список может варьироваться в зависимости от большого количества использования WITH RECOMPILE в хранимой процедуре или просто использовать динамический SQL без параметров для создания нового плана выполнения для каждого запроса.
Возможно, мы можем использовать 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)
Я подойду к этому по умолчанию с передачей функции с табличной оценкой (которая возвращает таблицу из строки) в условие IN.
Вот код для UDF (я получил его из Qaru где-то, я не могу найти источник прямо сейчас)
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
Если у вас есть нелепо длинная строка, это должно хорошо работать с индексом таблицы.
При необходимости вы можете вставить его в временную таблицу, индексировать ее, а затем запустить соединение...
Другим возможным решением является передача переменной количества аргументов в хранимую процедуру, передачу одной строки, содержащей имена, которые вы после, но сделать их уникальными, окружив их "< > ". Затем используйте PATINDEX, чтобы найти имена:
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0
Используйте следующую хранимую процедуру. Он использует пользовательскую функцию разделения, которую можно найти здесь.
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
Для переменного количества аргументов, подобных этому, единственный способ, который я знаю, - это либо явно генерировать SQL, либо делать что-то, что включает в себя заполнение временной таблицы элементами, которые вы хотите, и присоединение к таблице temp.
В ColdFusion мы просто делаем:
<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
<cfquery name="q">
select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
</cfquery>
Здесь используется метод, который воссоздает локальную таблицу, которая будет использоваться в строке запроса. Выполнение этого способа устраняет все проблемы синтаксического анализа.
Строка может быть построена на любом языке. В этом примере я использовал 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)
Если у нас есть строки, хранящиеся внутри предложения IN с запятой (,), мы можем использовать функцию charindex для получения значений. Если вы используете .NET, то вы можете сопоставить его с SqlParameters.
DDL Script:
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.
EDIT: Создайте таблицу под названием SelectedTags, используя следующий script.
DDL Script:
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
Вот еще одна альтернатива. Просто передайте список с разделителями-запятыми в качестве строкового параметра хранимой процедуры и:
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
У меня есть ответ, который не требует UDF, XML Поскольку IN принимает оператор select например SELECT * FROM Test, где 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);
Я использую более сжатую версию верхнего голосованного ответа:
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);
В SQL Server 2016+ другой возможностью является использование функции OPENJSON.
Этот подход описан в OPENJSON - одном из лучших способов выбора строк по списку идентификаторов.
Полный проработанный пример ниже
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
Вот еще один ответ на эту проблему.
(новая версия опубликована 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;
}
Приветствия.
Единственный выигрышный ход - не играть.
Нет бесконечной изменчивости для вас. Только конечная изменчивость.
В SQL у вас есть такое предложение:
and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )
В коде С# вы делаете что-то вроде этого:
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 все еще имеет смысл)
Иногда только хромающее решение действительно работает.