Sql_mode parsing and setting for MySQL

Reference Address: http://blog.csdn.net/ccccalculator/article/details/70432123

Because the use of group by in MySQL is always a 1055 error, which leads to the need to see what is the reason, query the relevant information, now note the notes, so that later can refer to the use:

Sql_mode: In short, it defines the SQL syntax that your MySQL should support, the checksum of the data, and so on.

SELECT @ @sql_mode: Using this command we can view the Sql_mode of our current database

Mysql> SELECT @ @sql_mode; +----------------------------------------------------------------------------------- --------------------------------------------------------+| @ @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 |+--------------------------------------------------------------------------------- ----------------------------------------------------------+1 Row in Set (0.00 sec)

Let's take a look at the meanings of the various values of Sql_mode:

Only_full_group_by:

For a group by aggregation operation, if the column in select does not appear in group by, the SQL is considered illegal because the column is not in the GROUP BY clause

Because of the only_full_group_by, we have to use the group by statement correctly in MySQL, only select Column1 from TB1 Group by Column1 (that is , only the fields of group by are displayed. All others have to report 1055 of the mistake )

Instance:

Mysql> SELECT * from tt1;+----+-------+--------+| ID | Name  | Gender |+----+-------+--------+|  1 | Xiong |      0 | |  2 | Ying  |      0 | |  3 | Cai   |      0 | |  4 | Zhang |      0 | |  5 | Li    |      1 | |  6 | Wang  |      1 |+----+-------+--------+6 rows in Set (0.00 sec) mysql> Select Id,name from TT1 group by name; ERROR 1055 (42000):

Mysql> SELECT * from tt1;+----+-------+--------+| ID | Name  | Gender |+----+-------+--------+|  1 | Xiong |      0 | |  2 | Ying  |      0 | |  3 | Cai   |      0 | |  4 | Zhang |      0 | |  5 | Li    |      1 | |  6 | Wang  |      1 |+----+-------+--------+6 rows in Set (0.00 sec) mysql> SELECT * from TT1 group by name; ERROR 1055 (42000):

Mysql> SELECT * from tt1;+----+-------+--------+| ID | Name  | Gender |+----+-------+--------+|  1 | Xiong |      0 | |  2 | Ying  |      0 | |  3 | Cai   |      0 | |  4 | Zhang |      0 | |  5 | Li    |      1 | |  6 | Wang  |      1 |+----+-------+--------+6 rows in Set (0.00 sec) mysql> Select name from TT1 GROUP by name;+-------+| Name  |+-------+| cai   | | li    | | Wang  | | xiong | | ying  | | Zhang |+-------+6 rows in Set (0.00 sec)

So we have to use the correct use of group BY, we have to delete the only_full_group_by

Set sql_mode= (select Replace(@ @sql_mode,' only_full_group_by ',')); You can use this statement to replace a space only_full_group_ By so that we can use

Mysql> SET sql_mode= (SELECT REPLACE (@ @sql_mode, ' only_full_group_by ', ')); Query OK, 0 rows Affected (0.00 sec) mysql> Select Id,name from TT1 GROUP by name;+----+-------+| ID | Name  |+----+-------+|  3 | Cai   | |  5 | Li    | |  6 | Wang  | |  1 | Xiong | |  2 | Ying  | |  4 | Zhang |+----+-------+6 rows in Set (0.00 sec)

Mysql> SET sql_mode= (SELECT REPLACE (@ @sql_mode, ' only_full_group_by ', ')); Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from TT1 GROUP BY name;+----+-------+--------+| ID | Name  | Gender |+----+-------+--------+|  3 | Cai   |      0 | |  5 | Li    |      1 | |  6 | Wang  |      1 | |  1 | Xiong |      0 | |  2 | Ying  |      0 | |  4 | Zhang |      0 |+----+-------+--------+6 rows in Set (0.00 sec)

But this method is only a temporary modification, we can change the configuration file My.ini

Sql_mode= "Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_ Engine_substitution "

Sql_mode parsing and setting for MySQL