MySQL5.7 new group by feature error 1055 solution, mysql5.71055

MySQL5.7 new group by feature error 1055 solution, mysql5.71055

The project was originally developed using mysql5.6. After switching to 5.7, it suddenly found that some of the original SQL statements run with an error code of 1055. The error message is related to "only_full_group_by" in SQL _mode, I read the reason online, saying that the only_full_group_by mode in mysql5.7 is enabled by default.
There are two solutions:

I. Use the any_value () function on fields that do not require group by in SQL query statements.

Of course, this is not suitable for projects that have developed many functions. After all, you must modify all the original SQL statements.

2. modify the configuration file my. cnf (my. ini in windows) and delete only_full_group_by.

Mysql of our project is installed on ubuntu. Find this file and open it. There is no SQL _mode configuration item in it. If you want to delete it, you will not have to delete it.

Of course, there are other ways to open the mysql command line and execute the command

select @@sql_mode

In this way, you can find the SQL _mode value, copy the value, and add the configuration item in my. cnf (delete the queried value from the only_full_group_by option, and copy the other values ):

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If the [mysqld] line is commented out, remember to open the comment. Restart the mysql service.

Note: use commands

set sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

In this way, you can modify the configuration items in one session, which does not take effect in other sessions.

The above is a small series of solutions to the new MySQL5.7 group by error 1055. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!