FIND_IN_SET() vs IN()

Question

J'ai deux tables dans ma base de données. L'une pour les commandes et l'autre pour les entreprises.

Les ordres ont cette structure :

OrderID     |     attachedCompanyIDs
------------------------------------
   1                     1,2,3
   2                     2,4

Et la société a cette structure :

CompanyID      |        name
--------------------------------------
    1                 Company 1
    2                 Another Company
    3                 StackOverflow
    4                 Nothing

Pour obtenir les noms des entreprises d'une commande, je peux effectuer une requête comme suit :

SELECT name FROM orders,company
WHERE orderID = 1 AND FIND_IN_SET(companyID, attachedCompanyIDs)

Cette requête fonctionne bien, mais la requête suivante ne fonctionne pas.

SELECT name FROM orders,company
WHERE orderID = 1 AND companyID IN (attachedCompanyIDs)

Pourquoi la première requête fonctionne-t-elle mais pas la seconde ?

La première requête renvoie :

name
---------------
Company 1
Another Company
StackOverflow

La deuxième requête ne donne que des résultats :

name
---------------
Company 1

Pourquoi la première requête renvoie-t-elle toutes les entreprises, alors que la seconde ne renvoie que la première ?

Answer 1

SELECT  name
FROM    orders,company
WHERE   orderID = 1
        AND companyID IN (attachedCompanyIDs)

attachedCompanyIDs est une valeur scalaire qui est intégrée dans INT (type de companyID ).

La distribution ne renvoie que les nombres jusqu'au premier chiffre qui n'est pas un chiffre (une virgule dans votre cas).

Ainsi,

companyID IN ('1,2,3') ≡ companyID IN (CAST('1,2,3' AS INT)) ≡ companyID IN (1)

Sur PostgreSQL vous pouvez convertir la chaîne en tableau (ou la stocker sous forme de tableau en premier lieu) :

SELECT  name
FROM    orders
JOIN    company
ON      companyID = ANY (('{' | attachedCompanyIDs | '}')::INT[])
WHERE   orderID = 1

et cela utiliserait même un index sur companyID .

Malheureusement, cela ne fonctionne pas dans MySQL puisque ce dernier ne prend pas en charge les tableaux.

Vous trouverez peut-être cet article intéressant (voir #2 ) :

• 10 choses dans MySQL (qui ne fonctionneront pas comme prévu)

Mise à jour :

S'il y a une limite raisonnable au nombre de valeurs dans les listes séparées par des virgules (disons, pas plus que 5 ), vous pouvez donc essayer d'utiliser cette requête :

SELECT  name
FROM    orders
CROSS JOIN
        (
        SELECT  1 AS pos
        UNION ALL
        SELECT  2 AS pos
        UNION ALL
        SELECT  3 AS pos
        UNION ALL
        SELECT  4 AS pos
        UNION ALL
        SELECT  5 AS pos
        ) q
JOIN    company
ON      companyID = CAST(NULLIF(SUBSTRING_INDEX(attachedCompanyIDs, ',', -pos), SUBSTRING_INDEX(attachedCompanyIDs, ',', 1 - pos)) AS UNSIGNED)

Answer 2

AttachedCompanyIDs est une grande chaîne de caractères, donc mysql essaie de trouver la société dans cette chaîne et la convertit en nombre entier

lorsque vous utilisez where dans

donc si comapnyid = 1 :

companyID IN ('1,2,3')

ceci est un retour à la réalité

mais si le numéro 1 n'est pas en première place

 companyID IN ('2,3,1')

son retour est faux

Answer 3

Parce que la deuxième requête recherche des lignes avec les identifiants 1 OU 2 OU 3, la première requête cherche une des valeurs délimitées par des virgules qui existe dans companyID,

Un autre problème est que vous ne joignez pas les tables sur une clé commune dans votre where. Vous allez donc obtenir une mutation des lignes qui = count(table1) * count(table2) ;

Votre problème existe réellement avec la partie 2 de ma réponse. (avec votre deuxième question)

Answer 4

Si a array(), on compare arr1 et les entreprises :

 $where = "WHERE 0 ";
    foreach($arr1 as $val)
    {
      $where .= " OR FIND_IN_SET($val, companies)";
    }