Mysql ONLY_FULL_GROUP_BY模式问题-为每个相册选择单个图像URL(任意) [英] Mysql ONLY_FULL_GROUP_BY mode issue - Pick single Image URL(any) for each Album

问题描述

我在mysql查询中需要帮助，这是详细信息:

I need help in a mysql query, here are the details:

三张桌子Album_Master，Album_Photo_Map和Photo_Details

Three Tables, Album_Master , Album_Photo_Map, Photo_Details

相册主表结构

album_id(P) | album_name | user_id(F)
1               abc           1
2               xyz           1
3               pqr           1
4               e3e           2

相册照片地图表结构

auto_id(P) | album_id(F) | photo_id
1             1              123
2             1              124
3             2              123 
4             2              125
5             1              127
6             3              127

Photo_Details表结构

Photo_Details Table Structure

auto_id(P) | image_id(F) | image_url
1               123          http....
2               124          http....
3               125          http...

我想编写一个查询以获取带有user_id 1的图像URL的相册名称我期望的输出是

I want to write a query to get the album name with image url for user_id 1 The output I am expecting here is

album_id | album_name | image_url
1            abc          http.. (either 123 or 124 or 127 - only one url)
2            xyz          http.. (either 123 or 125 - only one)
3            pqr          http.. (127 only)

我正在使用的查询要花费太多时间来执行，将近8秒钟.

The query I am using is taking too much time to execute, almost 8s.

SELECT A.album_id
     , A.album_name
     , D.image_url 
  from Album_Master A
     , Album_Photo_Map E 
  LEFT 
  JOIN Photo_Details D 
    ON (D.image_id = (
      SELECT P.photo_id 
        FROM Album_Photo_Map P
           , Photo_Details Q 
       WHERE P.photo_id = Q.image_id 
         AND P.album_id = E.album_id limit 0,1)
       ) 
 WHERE A.album_id = E.album_id 
   AND A.user_id = 1 
 group 
    by A.album_id
     , E.album_id
     , D.image_url;

我正在寻找查询的优化版本，对您的帮助将不胜感激.如果我分组使用 image_url 会创建多个记录，那么如果我删除 D.image_url 也会给我错误

I am looking for an optimize version of the query, any help will be really appreciated. If I use image_url in group by it is creating multiple records, also if I remove D.image_url it gives me error

D.image_url'，在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by

D.image_url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

注意:用户可以在多个相册中分配一张照片，结果每个相册只能选择一张照片，一个相册中可能有100张照片.

Note: user can assign one photo in multiple albums, result should pick only one photo per album, there might be 100 of photos in an album.

推荐答案

如果应该在结果集中包含哪个url都没有关系，那么您只可以按 album_id 进行分组，就可以了.去.

If it doesn't matter which url it should include in resultset then you can just do group by album_id only and you are good to go.

SELECT 
    A.album_id, A.album_name, D.image_url 
FROM album_master A
INNER JOIN album_photo_map P ON A.album_id = P.album_id
INNER JOIN photo_details D ON P.photo_id = D.image_id
GROUP BY A.album_id;

注意:如果即使您没有相册也要相册信息，请在查询中使用 LEFT JOIN 而不是 INNER JOIN .

Note: If you want album info even there is no photo attached to it then use LEFT JOIN instead of INNER JOIN in query.

由于ONLY_FULL_GROUP_BY而引起的功能依赖性问题

Functional Dependency Issue due to ONLY_FULL_GROUP_BY

MySQL 5.7.5及更高版本实现了对功能依赖性的检测.如果启用了ONLY_FULL_GROUP_BY SQL模式(默认情况下为MySQL)，MySQL将拒绝选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名且在功能上不依赖于它们的未聚合列的查询

MySQL 5.7.5 and later implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them

特定问题:D.image_url"在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by

Specific Issue: D.image_url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方案1: 如果可能，对 group by 子句中未包括的其他列使用 aggregate 函数.

Solution 1: If possible use aggregate function for the other columns which are not included in group by clause.

解决方案2: 如果您知道，对于给定的数据集，每个 album_id 值实际上唯一地确定了 image_url 值，这意味着 image_url 实际上在功能上取决于 album_id .所以你可以做

Solution 2: If you know that, for a given data set, each album_id value in fact uniquely determines the image_url value that means image_url is effectively functionally dependent on album_id. So you can do

SELECT 
    A.album_id, A.album_name, ANY_VALUE(D.image_url) AS image_url 
FROM album_master A
INNER JOIN album_photo_map P ON A.album_id = P.album_id
INNER JOIN photo_details D ON P.photo_id = D.image_id
GROUP BY A.album_id;

解决方案3:或者在mysql中禁用 only_full_group_by

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

更多信息请查看

A more info check Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL MODE

这篇关于Mysql ONLY_FULL_GROUP_BY模式问题-为每个相册选择单个图像URL(任意)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！