FIND_IN_SET () kontra IN ()

Mam 2 tabele w mojej bazie danych. Jeden do zamówień, a drugi do firm.

Zamówienia mają następującą strukturę:

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

Firma ma taką strukturę:

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

Aby uzyskać nazwy firm z zamówienia, mogę wykonać takie zapytanie:

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

To zapytanie działa dobrze, ale następujące zapytanie nie.

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

Dlaczego pierwsze zapytanie działa, a drugie nie?

Pierwsze zapytanie zwraca:

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

Drugie zapytanie zwraca tylko:

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

Dlaczego tak jest, dlaczego pierwsze zapytanie zwraca wszystkie firmy, a drugie zapytanie zwraca tylko pierwszą?

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

attachedCompanyIDsjest wartością skalarną, która jest rzutowana na INT(typ companyID).

Rzutowanie zwraca tylko liczby do pierwszej niebędącej cyfrą (w twoim przypadku przecinek).

A zatem,

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

W programie PostgreSQLmożesz rzutować ciąg na tablicę (lub przechowywać go jako tablicę w pierwszej kolejności):

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

a to nawet użyłoby indeksu na companyID.

Niestety to nie działa, MySQLponieważ ta ostatnia nie obsługuje tablic.

Ten artykuł może Cię zainteresować (zobacz #2):

• 10 rzeczy w MySQL (które nie będą działać zgodnie z oczekiwaniami)

Aktualizacja:

Jeśli istnieje rozsądny limit liczby wartości na listach oddzielonych przecinkami (powiedzmy nie więcej niż 5), możesz spróbować użyć tego zapytania:

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)

attachCompanyIDs to jeden duży ciąg, więc mysql próbuje znaleźć firmę w tym rzutowaniu na liczbę całkowitą

kiedy używasz gdzie w

więc jeśli comapnyid = 1:

companyID IN ('1,2,3')

to jest powrót prawda

ale jeśli numer 1 nie jest na pierwszym miejscu

 companyID IN ('2,3,1')

jego powrót jest fałszywy

Aby uzyskać nazwy wszystkich powiązanych firm, nie na podstawie konkretnego identyfikatora.

SELECT 
    (SELECT GROUP_CONCAT(cmp.cmpny_name) 
    FROM company cmp 
    WHERE FIND_IN_SET(cmp.CompanyID, odr.attachedCompanyIDs)
    ) AS COMPANIES
FROM orders odr

ponieważ drugie zapytanie szuka wierszy o identyfikatorze 1 LUB 2 LUB 3, pierwsze zapytanie szuka jednej z wartości rozdzielanych przecinkami, która ma istnieć w CompanyID,

a innym problemem jest to, że nie dołączasz do tabel na wspólnym kluczu w swoim Where, więc otrzymasz mutację wierszy, która = count (table1) * count (table2);

Twój problem naprawdę istnieje z częścią 2 mojej odpowiedzi. (z drugim zapytaniem)

Pozwól, że wyjaśnię, kiedy używać FIND_IN_SET i kiedy używać IN.

Weźmy tabelę A, która ma kolumny o nazwach „pomoc”, „nazwa”. Weźmy tabelę B, która zawiera kolumny o nazwach „stawka”, „bname”, „aids”.

Teraz w Tabeli A i Tabeli B są wartości zastępcze, jak poniżej.

Tabela A

pomoc aname

1 jabłko

2 Banan

3 Mango

Tabela B.

licytuj pomoce bname

1 jabłko 1,2

2 Banan 2,1

3 Mango 3,1,2

enter code here

Przypadek 1: jeśli chcesz pobrać te rekordy z tabeli b, która ma 1 wartość w kolumnach pomocy, musisz użyć FIND_IN_SET.

Zapytanie: wybierz * z A JOIN B ON FIND_IN_SET (A.aid, b.aids), gdzie A.aid = 1;

Przypadek 2: jeśli chcesz pobrać te rekordy z tabeli a, która ma wartość 1 LUB 2 LUB 3 obecną w kolumnach pomocy, musisz użyć IN.

Zapytanie: wybierz * z A JOIN B ON A.aid IN (b.aids);

A teraz do Ciebie, czego potrzebujesz poprzez zapytanie mysql.

SELECT o.*, GROUP_CONCAT(c.name) FROM Orders AS o , Company.c
    WHERE FIND_IN_SET(c.CompanyID , o.attachedCompanyIDs) GROUP BY o.attachedCompanyIDs