mysql - ejemplo - FIND_IN_SET() vs IN()

in mysql

Question

Tengo 2 tablas en mi base de datos. Una es para pedidos y otra para empresas.

Pedidos tiene esta estructura:

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

Y la compañía tiene esta estructura:

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

Para obtener los nombres de las compañías de una orden, puedo hacer una consulta como tal:

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

Esa consulta funciona bien, pero la siguiente consulta no.

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

¿Por qué funciona la primera consulta pero no la segunda?

La primera consulta regresa:

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

La segunda consulta solo devuelve:

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

¿Por qué es esto? ¿Por qué la primera consulta devuelve todas las compañías, pero la segunda consulta solo devuelve la primera?

Answer 1

Para obtener el nombre de todas las empresas relacionadas, no se basa en un Id específico.

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

Answer 2

Permítanme explicar cuándo usar FIND_IN_SET y cuándo usar IN.

Tomemos la tabla A que tiene columnas llamadas "aid", "aname". Tomemos la tabla B que tiene columnas llamadas "bid", "bname", "aid".

Ahora hay valores ficticios en la Tabla A y la Tabla B como se muestra a continuación.

Tabla A

ayuda aname

1 Apple

2 Plátano

3 Mango

Tabla B

bid bname aid

1 manzana 1,2

2 Plátano 2,1

3 Mango 3,1,2

enter code here

Caso 1: si desea obtener esos registros de la tabla b que tiene 1 valor presente en las columnas de ayudas, entonces debe usar FIND_IN_SET.

Consulta: seleccionar * de A UNIR B EN FIND_IN_SET (A.aid, b.aids) donde A.aid = 1;

Caso2: si desea obtener los registros de la tabla a que tiene 1 OR 2 O 3 valor presente en las columnas de ayuda, entonces debe usar IN.

Consulta: selecciona * de A UNE B EN A.aid IN (b.aids);

Ahora aquí hasta usted que lo que necesita a través de la consulta mysql.

Answer 3

attachedCompanyIDs es una gran cadena, por lo que tratar de encontrar la empresa en este mysql es un número entero

cuando usas donde en

entonces si comapnyid = 1:

companyID IN (''1,2,3'')

esto es verdadero retorno

pero si el número 1 no está en primer lugar

 companyID IN (''2,3,1'')

su devolución es falsa

Answer 4

porque la segunda consulta busca filas con el id 1 OR 2 OR 3, la primera consulta busca uno de los valores delimitados por comas para existir en companyID,

y otro problema aquí es que no está uniendo las tablas en una clave común en su lugar, de modo que obtendrá una mutación de filas que = cuente (tabla1) * recuento (tabla2);

Tu problema realmente existe con la parte 2 de mi respuesta. (con su segunda consulta)

Answer 5

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

attachedCompanyIDs es un valor escalar que se convierte en INT (tipo de companyID de la companyID ).

El elenco solo devuelve números hasta el primer dígito que no sea (una coma en tu caso).

Así,

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

En PostgreSQL , podría convertir la cadena en una matriz (o almacenarla como una matriz en primer lugar):

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

y esto incluso usaría un índice en companyID .

Desafortunadamente, esto no funciona en MySQL ya que este último no admite matrices.

Puede encontrar interesante este artículo (vea el n #2 ):

• 10 cosas en MySQL (eso no funcionará como se esperaba)

Actualizar:

Si hay un límite razonable en el número de valores en las listas separadas por comas (por ejemplo, no más de 5 ), entonces puede intentar usar esta consulta:

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 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