tidb执行sql语句报错

建表语句
CREATE TABLE new_user_role_group (
user_id int(11) NOT NULL,
role_id int(11) NOT NULL,
selected_group_id int(11) NOT NULL,
PRIMARY KEY (user_id,role_id,selected_group_id),
KEY fk_new_user_role_group_2_idx (selected_group_id),
KEY fk_new_user_role_group_3_idx (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

sql-model
‘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’

执行语句:
select new_user_role_group.role_id, new_user_role_group.selected_group_id
from new_user_role_group where user_id=‘45’ group by new_user_role_group.selected_group_id

在mysql中可以运行

在tidb中报一下错误:
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘achievement6.new_user_role_group.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘achievement6.new_user_role_group.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

请大神指教!

通过设定 sql-model 解决问题。
set @@global.sql_mode= ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

如果业务满足也可以将group by 字段完整添加

好的。 如何提高tidb查询的速度呢?数据库中表字段已经创建索引,在mysql中运行时间为2秒多一点,改成tidb之后查询速度变成了14秒多,有什么优化的方案吗?

上面说的有点太模糊了,建议给下慢日志,及具体的 SQL

您好,具体查看哪些日志?我的是单机部署的方式

如果是单机,就建议使用mysql 吧,tidb 还是需要按照标准环境配置,并且数据量大一些才比较明显。