mysql中的any_value,MySQL 5.6有ANY_VALUE功能吗？

currently im working with mysql 5.7 in development, and 5.6 in production. Each time i run a query with a group by in development i get some error like "Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY "

Here is the query.

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;

For solving that I use the mysql function from 5.7 ANY_VALUE, but the main issue is that its not available in mysql 5.6

So if I fix the sql statement for development i will get an error in production.

Do you know any solution or polifill for the ANY_VALUE function in mysql 5.6?

解决方案

You're misusing the notorious nonstandard MySQL extension to GROUP BY. Standard SQL will always reject your query, because you're mentioning columns that aren't aggregates and aren't mentioned in GROUP BY. In your dev system you're trying to work around that with ANY_VALUE().

In production, you can turn off the ONLY_FULL_GROUP_BY MySQL Mode. Try doing this:

SET @mode := @@SESSION.sql_mode;

SET SESSION sql_mode = '';

/* your query here */

SET SESSION sql_mode = @mode;

This will allow MySQL to accept your query.

But look, your query isn't really correct. When you can persuade it to run, it returns a randomly chosen row from the images table. That sort of indeterminacy often causes confusion for users and your tech support crew.

Why not make the query better, so it chooses a particular image. If your images table has an autoincrement id column you can do this to select the "first" image.

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

That will return one row per country with a predictable image shown.