MySQL与MariaDB执行同一查询结果不一致问题求助

问题原因分析 #

1. 数据类型不匹配 #

数据库迁移过程中，consid、devid等关联字段的数据类型在两种数据库中未保持一致，导致隐式类型转换，部分关联条件无法正确匹配，最终丢失结果行。

2. GROUP BY行为差异 #

MySQL 8.2.0默认开启ONLY_FULL_GROUP_BY模式，严格遵循SQL标准：GROUP BY子句必须包含SELECT中所有非聚合列，否则查询报错或正确按分组字段拆分结果。而MariaDB 10.1默认未开启该模式，当SELECT中存在未加入GROUP BY的非聚合列时，会随机取某一行的非聚合列值，甚至错误合并分组，只返回部分结果。

3. 查询优化器执行逻辑差异 #

MariaDB 10.1的查询优化器对多表JOIN的执行顺序与MySQL 8.2.0不同，log_station与a_device通过FIND_IN_SET关联后，后续JOIN未正确匹配所有a_device_cons记录，导致结果丢失。

解决方案 #

1. 统一关联字段数据类型 #

检查a_device_cons.aid、log_stop.consid、sp_asset.aid等所有关联字段的数据类型，确保在MySQL和MariaDB中完全一致（如均为INT或均为VARCHAR），避免隐式转换导致的关联失败。

2. 修正GROUP BY语句（直接临时解决方案） #

将SELECT中所有非聚合列添加到GROUP BY子句中，确保符合SQL标准，消除不同数据库的行为差异：

SELECT
  adc.que,
  a.adname,
  a.adidentify,
  adc.loc,
  spa.aname,
  adc.qty,
  adc.var1,
  COUNT(ls.up_device) AS usage_count,
  MAX(lstp.stopdate) AS last_stop_date
FROM
  a_device a
  JOIN a_device_cons adc ON a.adid = adc.devid
  JOIN log_station ls ON FIND_IN_SET(
    ls.up_device,
    REPLACE(a.adidentify, '|', ',')
  ) > 0
  JOIN (
    SELECT
      devid,
      consid,
      consloc,
      MAX(stopdate) AS stopdate
    FROM
      log_stop
    WHERE
      consid IS NOT NULL
    GROUP BY
      devid,
      consid,
      consloc
  ) AS max_stopdate ON adc.devid = max_stopdate.devid
  AND adc.aid = max_stopdate.consid
  AND adc.loc = max_stopdate.consloc
  JOIN log_stop lstp ON adc.devid = lstp.devid
  AND adc.aid = lstp.consid
  AND adc.loc = lstp.consloc
  AND max_stopdate.stopdate = lstp.stopdate
  JOIN sp_asset spa ON spa.aid = lstp.consid
WHERE
  ls.up_date > lstp.stopdate
GROUP BY
  adc.que,
  a.adname,
  a.adidentify,
  adc.loc,
  spa.aname,
  adc.qty,
  adc.var1,
  lstp.devid,
  lstp.consid,
  lstp.consloc;

3. 重构数据模型，替代FIND_IN_SET（长期优化方案） #

FIND_IN_SET性能较差且兼容性易出问题，建议将a_device.adidentify的多值存储改为关联表，用标准JOIN替代字符串分割查询：

-- 1. 创建设备标识关联表
CREATE TABLE a_device_identifies (
  adid VARCHAR(50) NOT NULL,
  identify VARCHAR(50) NOT NULL,
  FOREIGN KEY (adid) REFERENCES a_device(adid)
);

-- 2. 插入现有标识数据
INSERT INTO a_device_identifies (adid, identify)
VALUES 
('hDix3NT20240423', 'TEST4003CH2_100002_B'),
('hDix3NT20240423', 'TEST4004TC1_100002_B'),
('hDix3NT20240423', 'TEST4004TC2_100002_B');

-- 3. 重构后的查询语句
SELECT
  adc.que,
  a.adname,
  a.adidentify,
  adc.loc,
  spa.aname,
  adc.qty,
  adc.var1,
  COUNT(ls.up_device) AS usage_count,
  MAX(lstp.stopdate) AS last_stop_date
FROM
  a_device a
  JOIN a_device_cons adc ON a.adid = adc.devid
  JOIN a_device_identifies adi ON a.adid = adi.adid
  JOIN log_station ls ON adi.identify = ls.up_device
  JOIN (
    SELECT
      devid,
      consid,
      consloc,
      MAX(stopdate) AS stopdate
    FROM
      log_stop
    WHERE
      consid IS NOT NULL
    GROUP BY
      devid,
      consid,
      consloc
  ) AS max_stopdate ON adc.devid = max_stopdate.devid
  AND adc.aid = max_stopdate.consid
  AND adc.loc = max_stopdate.consloc
  JOIN log_stop lstp ON adc.devid = lstp.devid
  AND adc.aid = lstp.consid
  AND adc.loc = lstp.consloc
  AND max_stopdate.stopdate = lstp.stopdate
  JOIN sp_asset spa ON spa.aid = lstp.consid
WHERE
  ls.up_date > lstp.stopdate
GROUP BY
  adc.que,
  a.adname,
  a.adidentify,
  adc.loc,
  spa.aname,
  adc.qty,
  adc.var1,
  lstp.devid,
  lstp.consid,
  lstp.consloc;

4. 调整MariaDB的sql_mode，对齐MySQL行为 #

在MariaDB中开启ONLY_FULL_GROUP_BY模式，使其分组行为与MySQL一致：

-- 临时生效（数据库重启后失效）
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 永久生效，修改my.cnf（Linux）或my.ini（Windows）文件
[mysqld]
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

内容的提问来源于stack exchange，提问作者MarrionBerry