FIND_IN_SET() vs IN()

У меня есть две таблицы в моей базе данных. Один для заказов, и один для компаний.

Заказы имеют следующую структуру:

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

И компания имеет эту структуру:

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

Чтобы получить имена компаний-заказчиков, я могу выполнить запрос как таковой:

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

Этот запрос работает нормально, но следующий запрос этого не делает.

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

Почему первый запрос работает, но не второй?

Первый запрос возвращает:

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

Второй запрос возвращает только:

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

Почему это, почему первый запрос возвращает все компании, но второй запрос возвращает только первый?

Ответы

Ответ 1

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

attachedCompanyIDs - это скалярное значение, которое преобразуется в INT (тип companyID).

Листинг возвращает числа до первой нецифровой (запятая в вашем случае).

Таким образом,

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

В PostgreSQL вы можете записать строку в массив (или сохранить ее как массив в первую очередь):

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

и это даже будет использовать индекс на companyID.

К сожалению, это не работает в MySQL, поскольку последний не поддерживает массивы.

Вы можете найти эту статью интересной (см. #2):

Update:

Если существует некоторое разумное ограничение на количество значений в списках, разделенных запятыми (скажем, не более 5), поэтому вы можете попробовать использовать этот запрос:

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)

Ответ 2

attachCompanyID - это одна большая строка, поэтому mysql пытается найти компанию в этом ее отличном от целого числа

когда вы используете в

так что если comapnyid = 1:

companyID IN ('1,2,3')

это true true

но если число 1 не в первую очередь

 companyID IN ('2,3,1')

его возвращает false

Ответ 3

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

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

Ответ 4

потому что второй запрос ищет строки с идентификатором 1 ИЛИ 2 ИЛИ 3, первый запрос ищет одно из значений с разделителями-запятыми, которые существуют в идентификаторе companyID,

и еще одна проблема заключается в том, что вы не присоединяетесь к таблицам на общем ключе в вашем месте, чтобы вы могли получить мутацию строк, которые: count (table1) * count (table2);

Ваша проблема действительно существует с частью 2 моего ответа. (со вторым запросом)

Ответ 5

Позвольте мне объяснить, когда следует использовать FIND_IN_SET и когда использовать IN.

Возьмем таблицу A, в которой есть столбцы с именем "помощь", "aname". Давайте возьмем таблицу B, в которой есть столбцы с именем "bid", "bname", "aids".

Теперь в таблице A и таблице B указаны фиктивные значения.

Таблица A

help aname

1 Apple

2 Банан

3 Манго

Таблица B

bid bname aids

1 Apple 1,2

2 Банан 2,1

3 Манго 3,1,2

enter code here

Case1: если вы хотите получить эти записи из таблицы b, которая имеет 1 значение, присутствующее в столбцах вспомогательных средств, вы должны использовать FIND_IN_SET.

Запрос: выберите * из A JOIN B ON FIND_IN_SET (A.aid, b.aids), где A.aid = 1;

Случай 2: если вы хотите получить эти записи из таблицы a, которая имеет 1 ИЛИ 2 ИЛИ 3 значение, присутствующее в столбцах помощи, вы должны использовать IN.

Запрос: выберите * из A JOIN B ON A.aid IN (b.aids);

Теперь вот вам, что вам нужно с помощью запроса mysql.

Ответ 6

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