环境:win10、PHPstudy、mysql5.7.26
一、问题提示:
1、Expression #1 of ORDER BY clause is not in GROUP BY clause and contains 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
2、Invalid default value for 'created_time'
二、百度查出问题的地方:
ONLY_FULL_GROUP_BY:
NO_ZERO_IN_DATE,NO_ZERO_DATE:
三、解决问题的思路想法:既然是高版本,既然是规范行为,为什么要取消限制呢?两种方法
1、取消限制
A、治本方法
如果不知道在自己的环境下在哪里设置mysql的配置,可以通过以下命令查询
CMD命令中执行mysql --help 查找配置路径(windows是my.ini,linux是my.cnf)
Default options are read from the following files in the given order:
翻译:默认选项按给定顺序从以下文件中读取
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf D:\phpstudy_pro\Extensions\MySQL5.7.26\my.ini D:\phpstudy_pro\Extensions\MySQL5.7.26\my.cnf
我在自己的环境下,只发现有D:\phpstudy_pro\Extensions\MySQL5.7.26\my.ini,并没有发现sql_mode的设置
于是在my.ini的[mysqld]的最后加上设置(记住不是my.ini整个设置的最后,否则不会生效,设置之后要重启mysql才能生效)sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
然后再查询sql_mode发现就是设置的值了,疑问的是如果没有这样设置,sql_mode的值也能查出来,那是调用哪里的呢?
B、治标方法:在mysql命令中做以下设置,重启后会失效
set @@sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2、规范sql语法
A、ONLY_FULL_GROUP_BY 是加强SQL规范的,其目的是让SQL查询出来的结果更符合规范,更准确。
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。简而言之,就是SELECT后面接的列必须被GROUP BY后面接的列所包含。如:
select a,b from table group by a,b,c; (正确)
select a,b,c from table group by a,b; (错误)
这个配置会使得GROUP BY语句环境变得十分狭窄,所以一般都不加这个配置
B、NO_ZERO_DATE & NO_ZERO_IN_DATE
将`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
修改为:
`created_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '插入时间',
注意:东八区要加8个小时,不管是系统时间是UTC还是CST,如果是CST诂计要在my.ini中设置default-time-zone吧,没试过,我的win10系统是UTC +8时区
因时间默认值设置为:0000-00-00 00:00:00的形式目前没发现有什么问题,所以在my.ini中将NO_ZERO_IN_DATE,NO_ZERO_DATE去掉,没必要这样规范。也没必要在my.ini中设置:default-time-zone='+08:00',直接默认就行
即设置为:
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
四、其它
1、登录
windows登录mysql,先切换到mysql.exe的路径下,如:
cd D:\phpstudy_pro\Extensions\MySQL5.7.26\bin
执行:mysql -p 3306 -h 127.0.0.1 -u root -p root
2、查看sql_mode:select @@sql_mode
3、设置system_time_zone(系统环境时区,mysql配置时区,会话时区)
A、系统环境时区:查看时区mysql命令:show variables like '%time_zone%'; 我这里可以看到mysql采用系统的时区设置
B、会话时区:
set time_zone = '+8:00'; 将当前会话的时区修改为东8区,即北京时间
flush privileges; 执行完成上面的SQL语句之后,如果想让时区立即生效,还需要执行这个语句
C、mysql配置时区:修改配置文件:打开my.ini文件,搜索default-time-zone,如果存在就修改值。如果不存在,可以在character-set-server后面添加一行即可:default-time-zone='+8:00'
参考网址:
http://www.manongjc.com/article/16878.html
https://www.jianshu.com/p/3dbccdef6031 明确mysql时区
https://jingyan.baidu.com/article/2fb0ba40d170dc00f2ec5fed.html 查看windows时区