MySQL 5.6有ANY_VALUE功能吗? [英] Is there ANY_VALUE capability for mysql 5.6?

查看:3160
本文介绍了MySQL 5.6有ANY_VALUE功能吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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



这里是查询。

  SELECT c.id,c.name,i。* 
FROM countries c,images i
WHERE i.country_id = c.id
GROUP BY c.id;固定为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中

所以如果我修复了开发中的sql语句,我会在生产中遇到错误。



你知道任何解决方案或polifill为MySQL 5.6中的ANY_VALUE函数吗?

解决方案

您滥用臭名昭着的非标准MySQL扩展到GROUP BY 。标准SQL将始终拒绝您的查询,因为您提到的是不是聚合的列,并且未在 GROUP BY 中提及。在你的开发系统中,你试图用 ANY_VALUE()来解决这个问题。 在生产中,您可以关闭 ONLY_FULL_GROUP_BY MySQL Mode。试着做这个

  SET @mode:= @@ SESSION.sql_mode; 
SET SESSION sql_mode ='';
/ *您的查询在这里* /
SET SESSION sql_mode = @mode;

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



但是,看,你的查询是不正确的。当您可以说服它运行时,它会从 images 表中返回一个随机选择的行。这种不确定性经常会让用户和技术支持人员感到困惑。为什么不让查询更好,所以它选择了一个特定的图像。如果你的 images 表有一个自动增量 id 列,你可以选择第一个图像。

  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图像i ON first.id = i .id

每个国家/地区都会返回一行,并显示可预测的图片。

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.

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆