mysql only_full_group_by off 仍然显示错误

Question

数据库错误错误:SQLSTATE[42000]:语法错误或访问冲突:1055 SELECT 列表的表达式 #27 不在 GROUP BY 子句中，并且包含非聚合列“erp.CFPG.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by

不兼容

SQL 查询:CALL get_product_groups(NULL,NULL,NULL,'0','12');

注意:如果您想自定义此错误消息，请创建 app/View/Errors/pdo_error.ctp

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_product_groups`(   
    IN _product_group_ids TEXT,
    IN _product_ids TEXT,
    IN _customer_id INT,
    IN _start INT,
    IN _limit INT 
)
BEGIN

    SELECT    
        PG.*,
        Image.*,
        AVG(fn_current_product_mrp(PGD.product_id)) AS avg_price,
        MIN(fn_current_product_mrp(PGD.product_id)) AS min_price,
        MAX(fn_current_product_mrp(PGD.product_id)) AS max_price,
        if (CFPG.id IS NULL, 0, 1) AS is_like,
        fn_discount(PG.id, CURDATE()) as discount_per,
        fn_product_rating(PG.id, NULL) AS rating,
        fn_product_rating_user_count(PG.id, NULL) AS rating_user_count,
        (SELECT SUM(fn_current_stock_sevenrock_warehouse(product_id, 1)) from product_group_details WHERE product_group_id = PG.id) as stock
    FROM
        product_groups PG 
        INNER JOIN product_group_details PGD ON PGD.product_group_id = PG.id                
        INNER JOIN images Image ON Image.id = PG.image_id
        LEFT JOIN customer_faviourate_product_groups CFPG ON CFPG.product_group_id = PG.id AND CFPG.customer_id = _customer_id
    WHERE
        PG.is_live = 1
        AND (_product_group_ids IS NULL OR FIND_IN_SET(PG.id, _product_group_ids) )
        AND (_product_ids IS NULL OR FIND_IN_SET(PGD.product_id, _product_ids) )
    GROUP BY
        PG.id
    ORDER BY
        PG.rank ASC, PG.id DESC
    LIMIT
        _start, _limit

    ;
END

但是，以下是sql_mode

Answer 1

运行此命令:

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