mysql - 迁移 - mysql 5.6 -> mariadb 10.1 -> sql 错误

Question

从 Mysql 5.6 迁移到 MariaDB 10.1 后，我得到了这些 sql 错误

SQL 1

SELECT dv.id,IF(de.id IS NULL,0,1) is_attached,dv.time,dv.label,dv.is_pdf_scan,dv.is_pic_low_resolution,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.file_name,u.name from_name,dv.from_email
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
LEFT JOIN `user` u ON u.id=dv.user_id
WHERE IF(de.id IS NULL,0,1)=0 && dv.time>=1420070400 && dv.time<=1451606399 && dv.client_id=1 && dv.block_id=4544 && dv.is_ready=1 && dv.is_deleted=0
GROUP BY dv.id
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

错误 1

#1055 - 'dynaccount.de.id' isn't in GROUP BY 

SQL 2

SELECT dv.id,dv.time,dv.label,dv.is_pdf_scan,dv.file_sha1,dv.file_ext,dv.file_ext_thumb
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
WHERE de.enclosure_id=270729 && dv.client_id=1 && dv.block_id=4431 && dv.is_ready=1 && dv.is_deleted=0
GROUP BY dv.id
ORDER BY dv.time DESC,dv.id DESC

错误2

#1055 'dynaccount.dv.time' isn't in GROUP BY

更新

在 phpmyadmin 中，单击 databases 选项卡时出现类似的错误

SQL

SELECT s.SCHEMA_NAME,s.DEFAULT_COLLATION_NAME
FROM `information_schema`.SCHEMATA s
GROUP BY BINARY s.SCHEMA_NAME
ORDER BY BINARY `SCHEMA_NAME` ASC

错误

#1055 - 'information_schema.s.SCHEMA_NAME' isn't in GROUP BY 

Answer 1

您的 SQL_MODE 系统变量似乎包含 ONLY_FULL_GROUP_BY 模式。

ONLY_FULL_GROUP_BY 强制执行严格的 ANSI SQL 行为并禁用 MySQL 特定的 GROUP BY 扩展。

参见 also ，并注意此模式是 MySQL 5.7 中的默认模式，因此从 MySQL 5.6 迁移到 MySQL 5.7 时也会遇到同样的问题