MySQL :only_full_group_by解决方案

·  阅读 79

表结构

# 车主表
create table owner
(
    id         int(10) auto_increment
        primary key,
    owner_name varchar(10) null,
    brand_id   int(10)     null
);

# 车辆表
create table vehicle
(
    id       int(10) auto_increment
        primary key,
    brand_id int(10) null
);

# 车辆品牌表
create table brand
(
    id         int(10) auto_increment
        primary key,
    brand_name varchar(10) null
);


INSERT INTO `halodb`.`owner` (`id`, `owner_name`, `brand_id`) VALUES (1, 'debug', 1);
INSERT INTO `halodb`.`owner` (`id`, `owner_name`, `brand_id`) VALUES (2, 'Ltx', 2);
INSERT INTO `halodb`.`owner` (`id`, `owner_name`, `brand_id`) VALUES (3, 'Ltx', 1);

INSERT INTO `halodb`.`brand` (`id`, `brand_name`) VALUES (1, '比亚迪');
INSERT INTO `halodb`.`brand` (`id`, `brand_name`) VALUES (2, '大众');

INSERT INTO `halodb`.`vehicle` (`id`, `brand_id`) VALUES (1, 1);
INSERT INTO `halodb`.`vehicle` (`id`, `brand_id`) VALUES (2, 2);
INSERT INTO `halodb`.`vehicle` (`id`, `brand_id`) VALUES (3, 1);
复制代码

image-20220115215310713

image-20220115215610068

image-20220115215630849


不使用group by分组:

select v.id as vehicleId,
       b.id as brandId, b.brand_name as brandName,
       o.owner_name as ownerName
from vehicle v
         left join brand b on v.brand_id = b.id
         left join owner o on b.id = o.brand_id
复制代码

执行结果长这样: 在这里插入图片描述

由于关联查询条件一对多问题,出现主表数据重复的问题。

使用group by对数据分组

select v.id as vehicleId,
       b.id as brandId, b.brand_name as brandName,
       o.owner_name as ownerName
from vehicle v
         left join brand b on v.brand_id = b.id
         left join owner o on b.id = o.brand_id
group by vehicleId;
复制代码

❌ 这样显然是不对的。

报错: this is incompatible with sql_mode=only_full_group_by

原因就是在MySQL 5.7.5以上版本后,要求group by 的字段需要查询的字段与group by的字段满足唯一性。也就是vehicleId有多个,MySQL不知道用哪个。所以在写SQL时保持一个习惯:唯一性的数据需要写在首位。

SQL 标准中不允许 SELECT 列表,HAVING 条件语句,或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。

建议

在使用group by时也需要注意,group by的该列一定是唯一的,如果group列出现数据重复数据时,仅会显示一条数据。

为测试该问题,在数据库新增一条重复数据

image-20220115230309202

select brand_name from brand group by brand_name;
复制代码

执行结果长这样:

image-20220115230506532

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改