mysql ORDER BY clause is not in SELECT list

sql

select distinct(T.id) as id from table T order by T.created_at

错误详情:

Error 3065: Expression #1 of ORDER BY clause is not in SELECT list, references column 'xxxx' which is not in SELECT list; this is incompatible with DISTINCT

查询sql_mode

mysql> show variables like "sql_mode"\G;
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: 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_SUBSTITUTIO

错误是由ONLY_FULL_GROUP_BY，这个sql_mode引起的。

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.
如果查询引用了不在group by 子句中的列话，这个查询是不被允许的。但是sql并没有出现group by, 但是参考:https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html 得知distinct是group by 的一种特殊情况。
在这种情况下sql就会变成:

select T.id as id from table T group by id order by T.created_at

修改Mysql配置

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