Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve 3065 error when mysql5.7 runs". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "how to solve 3065 error when mysql5.7 runs" together.

The following SQL

SELECT DISTINCT questionid,questiontype FROM x2_questions AS questions,x2_quest2knows AS quest2knows WHERE find_in_set(quest2knows.qkknowsid,:knowsid) AND quest2knows.qktype = 0 AND quest2knows.qkquestionid = questions.questionid AND questions.questionstatus = 1 ORDER BY questionparent asc,questionsequence

Run-time error message

Expression #1 of ORDER BY clause is not in SELECT list, references column 'pe6.questions.questionparent' which is not in SELECT list; this is incompatible with DISTINCT

Reason for error:

In mysql version 5.7, DISTINCT and order by together will report error 3065, sql statement can not be executed. This is due to the 5.7 syntax being stricter than previous versions.

Solution:

Change mysql security mode, sql_mode.

Steps:

1. Enter the following command in mysql terminal to view relevant configuration information

show variables like '%sql_mode%';

By default you see the following message

| sql_mode | 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

2. Set sql_mode Remove ONLY_FULL_GROUP_BY

It is recommended to add or modify sql_model configuration option in mysqld of mysql configuration file my.cnf file (linux)/my.ini file (window)

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Restart mysql takes effect

4. verification

Enter the following command in the mysql terminal to see the results.

show variables like '%sql_mode%';

Thank you for reading, the above is "mysql5.7 running 3065 error how to solve" the content, after the study of this article, I believe that we have a deeper understanding of mysql5.7 running 3065 error how to solve this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.