¿Existe la capacidad ANY_VALUE para mysql 5.6?

group-by aggregate-functions

Question

Actualmente estoy trabajando con MySQL 5.7 en desarrollo y 5.6 en producción. Cada vez que ejecuto una consulta con un grupo en desarrollo, aparece un error como "Código de error: 1055. La expresión # 1 de la lista SELECT no está en GROUP BY"

Aquí está la consulta.

SELECT c.id, c.name, i.* 
 FROM countries c, images i 
WHERE i.country_id = c.id
GROUP BY c.id; Fixed for 5.7; 

SELECT c.id, c.name,
       ANY_VALUE(i.url) url, 
       ANY_VALUE(i.lat) lat, 
       ANY_VALUE(i.lng) lng 
  FROM countries c, images i
 WHERE i.country_id = c.id
 GROUP BY c.id;

Para resolverlo, uso la función mysql de 5.7 ANY_VALUE, pero el problema principal es que no está disponible en mysql 5.6

Entonces, si corrijo la declaración sql para el desarrollo, obtendré un error en la producción.

¿Conoces alguna solución o polifill para la función ANY_VALUE en mysql 5.6?

Answer 1

Durante décadas, podría escribir consultas que no eran válidas en SQL estándar pero que eran perfectamente válidas en MySQL

En SQL estándar, una consulta que incluye una cláusula GROUP BY no puede hacer referencia a columnas no agregadas en la lista de selección que no están nombradas en la cláusula GROUP BY. Por ejemplo, esta consulta es ilegal en SQL estándar porque la columna de nombre no agregado en la lista de selección no aparece en GROUP BY:

SELECCIONE o.custid, c.name, MAX (o.payment) DE los pedidos AS o, clientes AS c DONDE o.custid = c.custid GROUP BY o.custid; Para que la consulta sea legal, la columna de nombre debe omitirse de la lista de selección o nombrarse en la cláusula GROUP BY.

MySQL extiende el uso estándar SQL de GROUP BY para que la lista de selección pueda hacer referencia a columnas no agregadas que no se mencionan en la cláusula GROUP BY.

Esto viene de la página del manual de Mysql 5.6 en GROUP BY . Si mira la misma página para 5.7.6 , verá que las cosas han cambiado. ¡Y cambió dramáticamente!

Esa página también te da la solución. Deshabilite ONLY_FULL_GROUP_BY Eso hará posible que su antigua consulta 5.6 funcione en 5.7.6 (elimine ANY_VALUE de su consulta ya que no está disponible en 5.7.6 pero use el ONLY_FULL_GROUP_BY en su lugar).

Answer 2

En lugar de ANY_VALUE , puede usar las funciones agregadas MIN o MAX .

Alternativamente, puede considerar no configurar el modo SQL ONLY_FULL_GROUP_BY , que está configurado de forma predeterminada para MySql 5.7, y es responsable de la diferencia que experimenta con MySql 5.6. Luego puede retrasar la actualización de sus consultas hasta que haya migrado todos sus entornos a MySql 5.7.

Cuál de las dos es la mejor opción, es discutible, pero a largo plazo será mejor adaptar sus consultas para que se adhieran a la regla ONLY_FULL_GROUP_BY . Usar MIN o MAX ciertamente puede ser útil para hacerlo.

Answer 3

Está haciendo mal uso de la notoria extensión MySQL no estándar de GROUP BY . SQL estándar siempre rechazará su consulta, porque está mencionando columnas que no son agregados y no se mencionan en GROUP BY . En su sistema de desarrollo, está intentando ANY_VALUE() con ANY_VALUE() .

En producción, puede desactivar el modo MySQL ONLY_FULL_GROUP_BY. Intenta hacer this :

  SET @mode := @@SESSION.sql_mode;
  SET SESSION sql_mode = '''';
  /* your query here */
  SET SESSION sql_mode = @mode;

Esto permitirá que MySQL acepte su consulta.

Pero mira, tu consulta no es realmente correcta. Cuando puede persuadirlo para que se ejecute, devuelve una fila elegida al azar de la tabla de images . Ese tipo de indeterminación a menudo causa confusión para los usuarios y su equipo de soporte técnico.

¿Por qué no mejorar la consulta, por lo que elige una imagen en particular? Si su tabla de images tiene una columna de id incremento automático, puede hacer esto para seleccionar la "primera" imagen.

SELECT c.id, c.name, i.*
  FROM countries c
  LEFT JOIN (
       SELECT MIN(id) id, country_id
         FROM images
        GROUP BY country_id
       ) first ON c.id = first.country_id
  LEFT JOIN images i ON first.id = i.id

Eso devolverá una fila por país con una imagen predecible que se muestra.