Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to solve the error report of ERROR 1055 (42000) in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn how to solve the error report of ERROR 1055 (42000) in mysql.

Error report:

ERROR 1055 (42000): Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'work_ad.api_community_pic.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The literal meaning is that sql_model=only_full_group_by is limited, so that SQL, which can be queried normally in previous MYSQL versions, cannot be used in 5.7,

The document states that the setting of ONLY_FULL_GROUP_BY will not allow query fields to include non-clustered columns

Query the mysql server version:

Select @ @ version shows 5.7.10

# check the syntax of sql_mode

Select @ @ GLOBAL.sql_mode;select @ @ SESSION.sql_mode

Display the results:

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

Only_full_group_by: to use this is to use the same group rules as oracle. The columns of select must be in group, or they must be SUM,AVG,MAX,MIN. In fact, this configuration is similar to distinct at present, so it is good to remove it.

Method 1:

The problem found in querying the mysql 1055 error code is that if sql_mode is set to include ONLY_FULL_GROUP_BY in the configuration of mysql, all the fields of select need to be included in the group by when querying.

That is, select XBI y from xxx group by XBI y

Otherwise, the error will be reported.

But check your own configuration my.cnf and find that there is no ONLY_FULL_GROUP_BY value in sql_mode.

Then check the configuration file of Laravel, config/database.php, and find the configuration of mysql.

'mysql' = > [' driver' = > 'mysql','host' = > env (' DB_HOST', 'localhost'),' port' = > env ('DB_PORT',' 3306'), 'database' = > env (' DB_DATABASE', 'forge'),' username' = > env ('DB_USERNAME',' forge'), 'password' = > env (' DB_PASSWORD','), 'charset' = >' utf8','collation' = > utf8_unicode_ci' 'prefix' = > env (' DB_PREFIX',''), 'strict' = > true,'engine' = > null,]

Found that there is a strict item, the default is true, the Internet also did not find the relevant explanation, according to the literal meaning to guess whether to turn on the strict mode, change it to false, test again and find the problem solved, you can output the correct result

Method 2:

# modify the syntax of sql_mode

This problem is solved by modifying the configuration file my.cnf.

1. Close the running mysql2. Modify / etc/my.cnf to delete the only_full_group_by in sql_mode= by 3. 0. Restart mysql and execute the wrong sql to find that ONLY_FULL_GROUP_BY was successfully executed: for GROUPBY aggregation operations, if the column in SELECT does not appear in GROUPBY, then the SQL is illegal because the column is not in the GROUPBY clause NO_AUTO_VALUE_ON_ZERO: this value affects the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. This option is useful if the user wants to insert a value of 0 and the column is self-growing. STRICT_TRANS_TABLES: in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restriction is imposed on the non-transactional table NO_ZERO_IN_DATE: in strict mode, zero dates and months are not allowed to NO_ZERO_DATE: set this value, the mysql database does not allow the insertion of zero dates, and inserting zero dates throws an error rather than a warning. ERROR_FOR_DIVISION_BY_ZERO: in the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the pattern is not given, MySQL returns NULL NO_AUTO_CREATE_USER when the data is divided by zero: forbids GRANT from creating a user NO_ENGINE_SUBSTITUTION with an empty password: throws an error if the required storage engine is disabled or uncompiled. When this value is not set, replace it with the default storage engine and throw an exception PIPES_AS_CONCAT: treat "| |" as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function Concat: when ANSI_QUOTES is enabled, the string cannot be referenced in double quotes because it is interpreted as an identifier

MYSQL5.7 is upgraded for better performance, and I guess MYSQL5.7 's restrictions on sql_mode are also for better query performance. So if you are in the development phase, if you want to use the mysql5.7 version, it is recommended that you read the document 5.7, and then write your sql more strictly, use less select * as much as possible, and just find out the data columns you want.

Since my program logically requires only different ip and last login time, my SQL is changed to the following:

SELECT ip,max (last_login) last_login FROM `sdb_login_ log` group by ip; here, I believe you have a deeper understanding of "ERROR 1055 (42000) error report in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.