mysql 5.6 是否有 ANY_VALUE 功能? [英] Is there ANY_VALUE capability for mysql 5.6?

问题描述

目前我在开发中使用 mysql 5.7，在生产中使用 5.6.每次我在开发中使用 group by 运行查询时，我都会收到一些错误，例如错误代码:1055.SELECT 列表的表达式 #1 不在 GROUP BY 中"

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 "

这是查询.

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;

为了解决我使用 5.7 ANY_VALUE 中的 mysql 函数的问题，但主要问题是它在 mysql 5.6 中不可用

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

因此，如果我修复了用于开发的 sql 语句，我将在生产中遇到错误.

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

你知道mysql 5.6中ANY_VALUE函数有什么解决方案或polifill吗?

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

推荐答案

您滥用了 对 GROUP BY 的臭名昭著的非标准 MySQL 扩展.标准 SQL 将始终拒绝您的查询，因为您提到的列不是聚合列，也未在 GROUP BY 中提及.在您的开发系统中，您正在尝试使用 ANY_VALUE() 解决这个问题.

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().

在生产中，您可以关闭ONLY_FULL_GROUP_BY MySQL模式.尝试做这个:

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;

这将允许 MySQL 接受您的查询.

This will allow MySQL to accept your query.

但是看，您的查询并不正确.当您可以说服它运行时，它会返回从 images 表中随机选择的一行.这种不确定性通常会给用户和您的技术支持人员带来困惑.

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.

为什么不让查询更好，所以它会选择一个特定的图像.如果您的 images 表有一个自动增量 id 列，您可以执行此操作以选择第一个"图像.

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.

这篇关于mysql 5.6 是否有 ANY_VALUE 功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！