Question Paramétrer une clause SQL IN


Comment paramétrer une requête contenant un IN clause avec un nombre variable d'arguments, comme celui-ci?

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

Dans cette requête, le nombre d'arguments peut être compris entre 1 et 5.

Je préférerais ne pas utiliser une procédure stockée dédiée pour cela (ou XML), mais s'il y a un moyen élégant spécifique à SQL Server 2008, Je suis ouvert à ça.


950
2017-12-03 16:16


origine


Réponses:


Voici une technique rapide et sale que j'ai utilisée:

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

Alors, voici le code 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);
}

Deux mises en garde:

  • La performance est terrible. LIKE "%...%" les requêtes ne sont pas indexées.
  • Assurez-vous de ne pas en avoir |, vierges ou nuls ou cela ne fonctionnera pas

Il y a d'autres façons d'accomplir cela que certaines personnes peuvent considérer plus propres, alors continuez s'il vous plaît à lire.


288
2017-12-03 16:41



Vous pouvez paramétrer chaque valeur, donc quelque chose comme:

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

Ce qui vous donnera:

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"

Non, ce n'est pas ouvert à Injection SQL. Le seul texte injecté dans CommandText n'est pas basé sur l'entrée de l'utilisateur. Il repose uniquement sur le préfixe "@tag" codé en dur et sur l'index d'un tableau. L'indice sera toujours être un entier, n'est pas généré par l'utilisateur et est sûr.

Les valeurs entrées par l'utilisateur sont toujours intégrées dans des paramètres, il n'y a donc aucune vulnérabilité.

Modifier:

Attention aux problèmes d'injection, prenez soin de noter que la construction du texte de commande pour accommoder un nombre variable de paramètres (comme ci-dessus) empêche la capacité du serveur SQL à tirer parti des requêtes mises en cache. Le résultat net est que vous perdez presque certainement la valeur d'utiliser des paramètres en premier lieu (par opposition à simplement insérer les chaînes de prédicat dans le SQL lui-même).

Non que les plans de requête mis en cache ne sont pas utiles, mais cette requête n'est pas assez compliquée pour en voir les avantages. Alors que les coûts de compilation peuvent approcher (voire dépasser) les coûts d'exécution, vous parlez toujours en millisecondes.

Si vous avez suffisamment de RAM, je pense que SQL Server mettra probablement en cache un plan pour les comptes communs de paramètres. Je suppose que vous pouvez toujours ajouter cinq paramètres, et laisser les balises non spécifiées être NULL - le plan de requête devrait être le même, mais il me semble plutôt moche et je ne suis pas sûr que ça vaut la micro-optimisation (bien que, sur Stack Overflow - cela peut très bien en valoir la peine).

En outre, SQL Server 7 et versions ultérieures requêtes auto-paramétrerDonc, l'utilisation de paramètres n'est pas vraiment nécessaire du point de vue de la performance. critique du point de vue de la sécurité - en particulier avec les données entrées par l'utilisateur comme ceci.


675
2017-12-03 16:35



Pour SQL Server 2008, vous pouvez utiliser un paramètre de table évalué. C'est un peu de travail, mais il est sans doute plus propre que mon autre méthode.

D'abord, vous devez créer un type

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

Ensuite, votre code ADO.NET ressemble à ceci:

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

235
2017-12-03 16:53



La question initiale était "Comment paramétrer une requête ..."

Permettez-moi de dire ici que c'est pas une réponse à la question originale. Il y a déjà des démonstrations de cela dans d'autres bonnes réponses.

Cela dit, allez-y et signalez cette réponse, dépréciez-la, notez-la comme n'étant pas une réponse ... faites ce que vous croyez être juste.

Voir la réponse de Mark Brackett pour la réponse préférée que j'ai (et 231 autres) mise à jour. L'approche donnée dans sa réponse permet 1) pour une utilisation efficace des variables de liaison, et 2) pour les prédicats qui sont sargables.

Réponse sélectionnée

Ce que je veux aborder ici est l'approche donnée dans la réponse de Joel Spolsky, la réponse «choisie» comme bonne réponse.

L'approche de Joel Spolsky est intelligente. Et cela fonctionne raisonnablement, il va montrer un comportement prévisible et des performances prévisibles, étant donné des valeurs «normales», et avec les cas de bord normatifs, tels que NULL et la chaîne vide. Et cela peut suffire pour une application particulière.

Mais en termes de généralisation de cette approche, considérons également les cas de coin plus obscurs, comme lorsque le Name La colonne contient un caractère générique (tel que reconnu par le prédicat LIKE.) Le caractère générique que je vois le plus couramment utilisé est % (un signe de pourcentage.). Alors traitons-en ici maintenant et passons ensuite à d'autres affaires.

Quelques problèmes avec le caractère%

Considérez une valeur Nom de 'pe%ter'. (Pour les exemples ici, j'utilise une valeur de chaîne littérale à la place du nom de colonne.) Une ligne avec une valeur Name de '' pe% ter '' serait retournée par une requête du formulaire:

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

Mais cette même rangée sera ne pas être renvoyé si l'ordre des termes de recherche est inversé:

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

Le comportement que nous observons est plutôt étrange. La modification de l'ordre des termes de recherche dans la liste modifie le jeu de résultats.

Il va presque sans dire que nous pourrions ne pas vouloir pe%ter pour correspondre au beurre de cacahuète, peu importe combien il l'aime.

Obscure coin affaire

(Oui, je suis d'accord pour dire que c'est un cas obscur.) Probablement un qui n'est pas susceptible d'être testé Nous ne nous attendons pas à un caractère générique dans une valeur de colonne Nous pouvons supposer que l'application empêche une telle valeur d'être stockée. dans mon expérience, j'ai rarement vu une contrainte de base de données qui interdit spécifiquement les caractères ou les modèles qui seraient considérés comme des caractères génériques sur le côté droit d'un LIKE opérateur de comparaison.

Patcher un trou

Une approche pour patcher ce trou est d'échapper à la % caractère générique. (Pour ceux qui ne connaissent pas la clause d'échappement de l'opérateur, voici un lien vers Documentation SQL Server.

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

Maintenant, nous pouvons faire correspondre le% littéral. Bien sûr, quand nous avons un nom de colonne, nous allons devoir échapper dynamiquement le caractère générique. Nous pouvons utiliser le REPLACE fonction pour trouver des occurrences de la %caractère et insérer un caractère barre oblique inverse en face de chacun, comme ceci:

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

Cela résout le problème avec le caractère générique%. Presque.

Échapper à l'évasion

Nous reconnaissons que notre solution a introduit un autre problème. Le caractère d'échappement. Nous voyons que nous allons aussi avoir besoin d'échapper à toutes les occurrences du caractère d'échappement lui-même. Cette fois, nous utilisons le! comme le caractère d'échappement:

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

Le trait de soulignement aussi

Maintenant que nous sommes sur un rouleau, nous pouvons ajouter un autre REPLACE gérer le caractère générique de soulignement. Et juste pour le fun, cette fois, nous utiliserons $ comme caractère d'échappement.

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

Je préfère cette approche pour échapper car il fonctionne dans Oracle et MySQL ainsi que SQL Server. (J'utilise habituellement le \ backslash comme caractère d'échappement, puisque c'est le caractère que nous utilisons dans les expressions régulières, mais pourquoi être contraint par la convention!

Ces supports embêtants

SQL Server permet également de traiter les caractères génériques comme des littéraux en les encadrant entre parenthèses []. Nous n'avons donc pas encore terminé la réparation, au moins pour SQL Server. Puisque les paires de parenthèses ont une signification particulière, nous devrons également nous échapper. Si nous parvenons à échapper correctement les crochets, alors au moins nous n'aurons pas à nous occuper du trait d'union - et le carat ^ entre les crochets. Et nous pouvons laisser tout %et _ les caractères à l'intérieur des parenthèses ont échappé, puisque nous aurons fondamentalement désactivé la signification spéciale des parenthèses.

Trouver des paires de parenthèses ne devrait pas être si difficile. C'est un peu plus difficile que de gérer les occurrences de singleton% et _. (Notez qu'il n'est pas suffisant d'échapper à toutes les occurrences de parenthèses, car une parenthèse singleton est considérée comme littérale et n'a pas besoin d'être échappée.) La logique devient un peu plus floue que je peux gérer sans exécuter d'autres cas de test. .)

L'expression en ligne devient salissante

Cette expression en ligne dans le SQL devient plus longue et plus laide. Nous pouvons probablement le faire fonctionner, mais le ciel aide la pauvre âme qui vient derrière et doit déchiffrer. En tant que fan je suis pour les expressions en ligne, je suis enclin à ne pas en utiliser un ici, principalement parce que je ne veux pas avoir à laisser un commentaire expliquant la raison du désordre, et m'excuser pour cela.

Une fonction où?

Bon, alors, si nous ne traitons pas cela comme une expression inline dans le SQL, l'alternative la plus proche que nous avons est une fonction définie par l'utilisateur. Et nous savons que cela n'accélèrera rien (sauf si nous pouvons définir un index dessus, comme nous le pourrions avec Oracle.) Si nous devons créer une fonction, nous ferions mieux de le faire dans le code qui appelle le SQL. déclaration.

Et cette fonction peut avoir des différences de comportement, en fonction du SGBD et de la version. (Un cri à tous les développeurs Java si désireux d'être en mesure d'utiliser n'importe quel moteur de base de données de façon interchangeable.)

Connaissance du domaine

Nous pouvons avoir une connaissance spécialisée du domaine pour la colonne (c'est-à-dire l'ensemble des valeurs autorisées appliquées à la colonne). a priori que les valeurs stockées dans la colonne ne contiendront jamais un signe de pourcentage, un trait de soulignement ou des paires de parenthèses. Dans ce cas, nous incluons simplement un commentaire rapide indiquant que ces cas sont couverts.

Les valeurs stockées dans la colonne peuvent autoriser des caractères% ou _, mais une contrainte peut exiger que ces valeurs soient échappées, en utilisant peut-être un caractère défini, de sorte que les valeurs soient LIKE comparaison "safe". Encore une fois, un petit commentaire sur l'ensemble de valeurs autorisé, et en particulier quel caractère est utilisé comme caractère d'échappement, et va avec l'approche de Joel Spolsky.

Mais, en l'absence des connaissances spécialisées et d'une garantie, il est important pour nous d'envisager au moins de traiter ces cas d'angle obscurs, et d'examiner si le comportement est raisonnable et «conforme aux spécifications».


Autres questions récapitulées

Je crois que d'autres ont déjà suffisamment souligné certains des autres sujets de préoccupation communément considérés:

  • Injection SQL (en prenant ce qui semble être des informations fournies par l'utilisateur, et en les incluant dans le texte SQL plutôt que de les fournir via des variables de liaison.) L'utilisation de variables de liaison n'est pas requise, c'est une approche pratique pour contrecarrer l'injection SQL. Faites avec:

  • plan d'optimisation utilisant l'analyse d'index plutôt que des recherches d'index, besoin possible d'une expression ou d'une fonction pour échapper les caractères génériques (index possible sur l'expression ou la fonction)

  • l'utilisation de valeurs littérales à la place des variables de liaison affecte l'évolutivité


Conclusion

J'aime l'approche de Joel Spolsky. C'est intelligent. Et il fonctionne.

Mais dès que je l'ai vu, j'ai immédiatement vu un problème potentiel, et ce n'est pas ma nature de le laisser glisser. Je ne veux pas critiquer les efforts des autres. Je sais que de nombreux développeurs prennent leur travail très personnellement, parce qu'ils investissent tellement dans ce travail et qu'ils en tiennent tellement compte. Alors, comprenez, ce n'est pas une attaque personnelle. Ce que j'identifie ici, c'est le type de problème qui surgit dans la production plutôt que dans les tests.

Oui, je suis allé loin de la question originale. Mais où d'autre laisser cette note concernant ce que je considère être un problème important avec la réponse «choisie» pour une question?


176
2018-05-29 23:18



Vous pouvez passer le paramètre en tant que chaîne

Donc vous avez la chaîne

DECLARE @tags

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

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

Ensuite, tout ce que vous avez à faire est de passer la chaîne en 1 paramètre.

Voici la fonction split que j'utilise.

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

123
2017-12-03 16:27



J'ai entendu Jeff / Joel en parler aujourd'hui sur le podcast (épisode 34, 2008-12-16 (MP3, 31 Mo), 1 h 03 min 38 secs - 1 h 06 min 45 secs), et je pensais me rappeler que Stack Overflow utilisait LINQ à SQL, mais peut-être que c'était abandonné. Voici la même chose dans LINQ to SQL.

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

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

C'est tout. Et, oui, LINQ regarde déjà assez en arrière, mais le Contains la clause me semble plus à l'envers. Lorsque j'ai dû faire une requête similaire pour un projet au travail, j'ai naturellement essayé de le faire de la mauvaise façon en faisant une jointure entre le tableau local et la table SQL Server, en supposant que le traducteur LINQ to SQL serait assez intelligent pour gérer le traduction en quelque sorte. Il n'a pas, mais il a fourni un message d'erreur qui était descriptif et m'a indiqué vers l'aide Contient.

De toute façon, si vous exécutez ceci dans le hautement recommandé LINQPadet exécutez cette requête, vous pouvez afficher le SQL réel généré par le fournisseur SQL LINQ. Il va vous montrer chacune des valeurs se paramétré dans un IN clause.


63
2017-12-19 05:40



Si vous appelez depuis .NET, vous pouvez utiliser 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});

Ici, Dapper fait la réflexion, donc vous n'avez pas à le faire. Quelque chose de similaire est possible avec LINQ à SQL, bien sûr:

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;

43
2018-06-15 11:04



C'est peut-être une façon à moitié méchante de le faire, je l'ai utilisé une fois, était plutôt efficace.

Selon vos objectifs, il pourrait être utile.

  1. Créer un table de temp avec une colonne.
  2. INSERT chaque valeur de recherche dans cette colonne.
  3. Au lieu d'utiliser un IN, vous pouvez ensuite utiliser votre standard JOIN règles. (Flexibilité ++)

Cela a un peu de flexibilité supplémentaire dans ce que vous pouvez faire, mais il est plus adapté aux situations où vous avez une grande table à interroger, avec une bonne indexation, et vous voulez utiliser la liste paramétrée plus d'une fois. Enregistre devoir l'exécuter deux fois et faire tous les travaux d'assainissement manuellement.

Je n'ai jamais eu à profiler exactement comment vite c'était, mais dans ma situation c'était nécessaire.


25
2017-12-03 17:04