Добавить список <int> в параметр mysql

Ответ 1

когда я передаю его в MySqlParameter, и это признается как строка, он помещает в sql-запрос, например "1 \, 2 \, 3 \, 4", и это не возвращает ожидаемые значения.

Я столкнулся с этим прошлой ночью. Я обнаружил, что FIND_IN_SET работает здесь:

SELECT * FROM table WHERE FIND_IN_SET(id, @parameter) != 0
...
intArray = new List<int>(){1,2,3,4};
conn.Command.Parameters.AddWithValue("parameter", string.Join(",", intArray));

По-видимому, это имеет некоторые ограничения по длине (я нашел ваше сообщение в поисках альтернативного решения), но это может сработать для вас.

Ответ 2

Параметры не работают с IN. Я всегда вставлял такие вещи, как строка в самом запросе. Хотя это обычно считается плохой формой, потому что SQL-инъекция, если вы строите запрос из строго типизированного числового списка, тогда не должно быть никакой возможности любого внешнего ввода, искажающего его значимым образом.

Ответ 3

вам придется перебирать массив и создавать список самостоятельно

// no parameters
var sb = new StringBuilder();
for(int i=0;i<intArray.Length;i++)
{
    sb.Append(intArray[i] + ",");// no SQL injection they are numbers
}
if (sb.Length>0) {sb.Length-=1;}
string sql = "SELECT * FROM table WHERE id IN (" + sb.ToString() + ")";

ОБНОВЛЕНИЕ: подумав об этом, я вернусь к своему первоначальному ответу (ниже), который должен использовать параметры. Оптимизация встроенных запросов и независимо от того, что может сделать механизм базы данных, зависит от вас.

// no parameters
var sb = new StringBuilder();
for(int i=0;i<intArray.Length;i++)
{
    sb.AppendFormat("p{0},", i);// no SQL injection they are numbers
    connection.Command.Parameters.AddWithValue("p"+i, intArray[i]);
}
if (sb.Length>0) {sb.Length-=1;}
string sql = "SELECT * FROM table WHERE id IN (" + sb.ToString() + ")";

Ответ 4

У вас есть несколько вариантов здесь (в порядке предпочтения):

1. Используйте базу данных, которая поддерживает табличные параметры. Это единственный способ получить нужный синтаксис.

2. Данные должны поступать откуда-то: из вашей базы данных, действий пользователя или сгенерированного компьютером источника.

   • Если данные уже есть в вашей базе данных, используйте вместо этого подзапрос.
   • Для других машинно-сгенерированных данных используйте BULK INSERT, SqlBulkCopy или предпочитаемые инструменты массового импорта базы данных.

   • Если он создан пользователем, добавьте его в отдельную таблицу для каждого отдельного действия пользователя, а затем используйте подзапрос.

     Примером этого является корзина для покупок. Пользователь может выбрать несколько предметов для покупки. Вместо того, чтобы хранить их в приложении, и нужно добавлять все элементы в заказ за один раз, когда они извлекаются, добавьте каждый элемент в таблицу в БД, когда пользователь выбирает или изменяет его.

3. Имейте пользовательскую функцию sql, которая распаковывает строковый параметр в таблицу и возвращает эту таблицу как набор, который вы можете использовать с выражением IN(). См. связанную статью ниже для получения более подробной информации о том, как это работает.
4. Построить список строк или список параметров динамически на клиенте (как показано в других ответах). Обратите внимание, что это мой наименее предпочтительный вариант, так как создаваемый им код имеет тенденцию быть безумно уязвимым к проблемам внедрения SQL.

Окончательная (и я имею в виду окончательная) работа по этому вопросу находится здесь:

http://www.sommarskog.se/arrays-in-sql.html

Статья длинная, но в хорошем смысле. Автор является экспертом по SQL Server, но в целом концепции применимы и к MySQL.

Ответ 5

Как я знаю, вы не можете предоставить какой-либо массив в качестве параметра для подготовленного оператора. IN() не поддерживает параметры в виде массива.

Ответ 6

Я не думаю, что вы можете добавить их таким образом, но, возможно, вы могли бы перебирать список и генерировать запрос динамически.

Например:

var intArray = new List<int>(){1,2,3,4};
if (intArray.Count > 0) {
    var query = "SELECT * FROM table WHERE id IN (";
    for (int i = 0; i < intArray.Count; i++) {
        //Append the parameter to the query
        //Note: I'm not sure if mysql uses "@" but you can replace this if needed
        query += "@num" + i + ",";
        //Add the value to the parameters collection
        ...connection.Command.Parameters.AddWithValue("num" + i, intArray[i]);
    }
    //Remove the last comma and add the closing bracket
    query = query.Substring(0, query.Length - 1) + ");";
    //Execute the query here
}

Таким образом, вы даже можете использовать список с различной типизацией и по-прежнему пользоваться преимуществами параметризованных запросов. Однако я не знаю, будут ли проблемы с производительностью с более крупными списками, но я подозреваю, что это будет так.

Ответ 7

Это не работает для огромных списков, но это только вещь, которую я нашел, которая работает, если вам нужно передать список в качестве параметра.

Вместо

SELECT * FROM table WHERE id IN (@parameter)

Вы должны сделать это:

SELECT *
FROM table
WHERE INSTR(','[email protected]+',', ','+CAST(the_column AS CHAR) + ',')

Затем вы можете перейти в свой список с помощью string.Join(",", intArray)

Это kludge, но он работает.

Ответ 8

Ответ от Mud работает только для первого int в списке параметров. Это означает, что "2,1,3,4" не будет работать, если, например, id равен 1.

Смотрите FIND_IN_SET() против IN().

Пока комментариев нет, но также см. ответ от Мэтта Эллена. Редактировал бы его ответ, но не смог. INSTR, похоже, не работает в случае WHERE с более чем одним идентификатором (возвращается только по результату).

Но замена INSTR на LOCATE делает его решение работать (с String.Join(",", intArray) в качестве добавленного параметра)... UP VOTE от меня:

LOCATE(CONCAT(',' , CAST(id AS CHAR) , ',') , CONCAT(',' , CAST(@paramter AS CHAR) , ',')) <> 0