Mysql 5.7 into the pit

Environment: win10, PHPstudy, mysql5.7.26

I. Question prompt:

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'

Second, where Baidu found the problem:

ONLY_FULL_GROUP_BY:

NO_ZERO_IN_DATE,NO_ZERO_DATE:

Three, the idea of ​​solving the problem: Since it is a high-level version, since it is a normative behavior, why should the restriction be lifted?Two ways

1. Cancel restrictions

A, the cure method

If you don't know where to set the mysql configuration in your own environment, you can query it with the following command

Execute mysql --help in the CMD command to find the configuration path (windows is my.ini, linux is my.cnf)

Default options are read from the following files in the given order:

Translation: Default options 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

In my own environment, I only found D:\phpstudy_pro\Extensions\MySQL5.7.26\my.ini, but did not find the setting of sql_mode

So add the setting at the end of [mysqld] of my.ini (remember that it is not the last of the entire setting of my.ini, otherwise it will not take effect. After setting, restart mysql to take effect) sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then query sql_mode and find that it is the set value. The doubt is that if it is not set in this way, the value of sql_mode can also be found out. Where is it called?

B. Symptomatic method: do the following settings in the mysql command, it will be invalid after restarting

set @@sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

2. Standard sql syntax

A, ONLY_FULL_GROUP_BY is to strengthen the SQL specification, and its purpose is to make the results of the SQL query more in line with the specification and more accurate.

For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is invalid because the column is not in the GROUP BY clause.In short, the column following the SELECT must be included in the column following the GROUP BY.Such as:
select a,b from table group by a,b,c; (correct)
select a,b,c from table group by a,b; (wrong)
This configuration will make GROUPThe BY statement environment has become very narrow, so this configuration is generally not added

B, NO_ZERO_DATE & NO_ZERO_IN_DATE

Insert `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insert time',

Modify to:

`created_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT 'Insert time',

Note: The East Eighth District needs to add 8 hours, no matter whether the system time is UTC or CST, if it is CST, you need to set the default-time-zone in my.ini, I haven't tried it, my win10 systemis UTC +8 time zone

Because the default time value is set to: 0000-00-00 00:00:00, no problem has been found so far, so remove NO_ZERO_IN_DATE and NO_ZERO_DATE in my.ini, there is no need for such a specification.There is no need to set in my.ini: default-time-zone='+08:00', just default directly

It is set to:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Four, other

1. Login

Windows login to mysql, first switch to the path of mysql.exe, such as:

cd D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

Execute: mysql -p 3306 -h 127.0.0.1 -u root -p root

2. View sql_mode: select @@sql_mode

3. Set system_time_zone (system environment time zone, mysql configuration time zone, session time zone)

A. System environment time zone: check the time zone mysql command: show variables like '%time_zone%'; I can see here that mysql adopts the system time zone setting

B. Session time zone:

set time_zone = '+8:00'; Change the time zone of the current session to East 8th zone, namely Beijing time

flush privileges; After executing the above SQL statement, if you want the time zone to take effect immediately, you also need to execute this statement

C, mysql configuration time zone: modify the configuration file: open the my.ini file, search for default-time-zone, and modify the value if it exists.If it does not exist, you can add a line after character-set-server: default-time-zone='+8:00'

Reference URL:

http://www.manongjc.com/article/16878.html

https://www.jianshu.com/p/3dbccdef6031 Clear mysql time zone

https://jingyan.baidu.com/article/2fb0ba40d170dc00f2ec5fed.html View windows time zone