MySQL from aggregate function group by to Sql_mode

When it comes to group by, everyone is not unfamiliar, it is to group the data of the query, we can achieve some special needs through this operation, such as Deduplication.

Recently used in the project hql: "from tsjrz where CBh =?" GROUP BY CName ", when Hibernate uses queries at the bottom, it will look up all the fields of the table and group them by C_name. The purpose of writing such a hql is to remove duplicate c_name values through group by. It then operates on the data that was taken.

My MySQL version is 5.6.17, when using the above notation is OK, the normal access to data and operation. However, in the formal environment (MySQL version 5.7.18), the HQL reported an error:

[ERR] 1055 -Expression #1  of SELECTList is  not inch GROUP  byClause and containsnonaggregatedcolumn 'Db_test.tsjrzo_. C_BH'which is  not 
Functionally dependent onColumnsinch GROUP  byClause This isIncompatible withSql_mode=Only_full_group_by.

This means that the SQL is written in violation of MySQL's database settings sql_mode=only_full_group_by.

What is only_full_group_by ?

For a group by aggregation operation, if the column in select does not appear in group BY, then this SQL is not legal because the column is not in the GROUP BY clause.

So here we are in the case of satisfying the only_full_group_by setup, our SQL query is modified as follows:

    Select A.*
      From
           (selectmax(D_CJSJ)  --Take the latest record from               
              where='123'
               Groupby      where= b.c_name

Here we note that the default setting for MySQL version 5.6 is not sql_mode=only_full_group_by, and the mysql5.7 version will sql_mode the default setting of Only_full_group_by, This leads to the difference. and of course we can't rely on some specific features of the database to write SQL arbitrarily.

MySQL's sql_mode some other common values :

1, No_auto_value_on_zero: This value affects the insertion of the self-growing column. By default, inserting 0 or null represents the generation of the next self-growth value. This option is useful if the user wants to insert a value of 0 and the column is self-growing;

2, Strict_trans_tables: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, the non-transactional table is not restricted;

3, No_zero_in_date: In strict mode, the date and month are not allowed to zero;

4, No_zero_date: Set this value, MySQL database is not allowed to insert 0 period, insert 0 will throw an error instead of warning;

5. Error_for_division_by_zero: During insert or update, if data is removed by 0, an error is generated rather than a warning. If the pattern is not given, then MySQL returns NULL when the data is removed by 0;

6. No_auto_create_user: Prohibit grant from creating a user with a blank password;

7. No_engine_substitution: If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, the default storage engine is substituted and an exception is thrown;

8, Pipes_as_concat: Will "| |" The concatenation operator, rather than the operator, that is treated as a string, which is the same as the Oracle database, and similar to the concatenation function of the string concat;

9, Ansi_quotes: After enabling ansi_quotes, you cannot use double quotation marks to refer to a string, because it is interpreted as a qualifier;

MySQL from aggregate function group by to Sql_mode