mysql5.7数据库在执行建表语句时报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause andcontains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
错误原因好像是以前咱们的SQL语句写法都不规范,现在mysql5.7版本开始默认按照only_full_group_by模式了,导致报错。
解决办法就是去掉这个模式。
执行select @@sql_mode;可看到目前支持的sql模式,发现有only_full_group_by。
直接在mysql的配置文件里,我用的centos7,其中mysql的配置文件叫/etc/my.cnf,
进入其中找到[mysqld],在[mysqld]下面写上红字部分(就是把上面查出来的模式去掉only_full_group_by即可)。重启mysql服务。
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/usr/local/mysql/data/mysql.err
pid-file=/usr/local/mysql/data/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
socket=/usr/local/mysql/data/mysql.sock