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

Because the use of group by in MySQL is always 1055 error, which leads to the need to check what is the reason, query the relevant information, now take notes down, so that later reference can be used:

sql_mode: In short, it defines the sql syntax that MySQL should support, the validation of data, and so on.

select @@sql_mode: With this command we can view 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 look at the meaning of sql_mode values:

ONLY_FULL_GROUP_BY：

For GROUP BY aggregation operations, if a column in SELECT does not appear in GROUP BY, then the SQL is considered illegal because the column is not in the GROUP BY clause.

Because there is only_full_group_by, so if we want to use the group by statement correctly in MySQL, we can only select column1 from tb1 group by column1(that is, only the group by field can be displayed, and all others must report 1055 error)

Examples:

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 name from tt1 group by name;+-------+| name |+-------+| cai || li || wang || xiong || ying || zhang |+-------+6 rows in set (0.00 sec)

So if we want to use group by correctly, we must delete only_full_group_by

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); this statement can be used to replace only_full_group_by with spaces, so 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 1 group by name;+---+--------+ tt| id | name |+----+-------+| 3 | cai || 5 | li || 6 | wang || 1 | xiong || 2 | ying || 4 | zhang |+----+-------+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

STRICT_TRANS_TABLES：

In this mode, if a value cannot be inserted into a transactional table, the current operation is aborted and no restrictions are placed on non-transactional tables

NO_ZERO_IN_DATE：

In strict mode, dates with a month or day component of 0 are not accepted. If IGNORE option is used, we insert '0000-00-00' for similar dates. In non-strict mode, the date is acceptable, but a warning is generated.

NO_ZERO_DATE：

In strict mode, do not use '0000-00-00' as a legal date. You can still insert zero dates with IGNORE option. In non-strict mode, the date is acceptable, but a warning is generated

ERROR_FOR_DIVISION_BY_ZERO：

In strict mode, during INSERT or UPDATE, if divided by zero (or MOD(X, 0)), an error is generated (otherwise a warning). If the pattern is not given, MySQL returns NULL when divided by zero. MySQL generates a divide-by-zero warning when used in INSERT IGNORE or UPDATE IGNORE, but the operation results in NULL

NO_AUTO_CREATE_USER：

Prevents GRANT from automatically creating new users unless a password is also specified.

NO_ENGINE_SUBSTITUTION：

If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, replace it with the default storage engine and throw an exception

MySQL 5.0 and above support three sql_mode modes: ANSI, TRADITIONAL and STRICT_TRANS_TABLES.

ANSI mode: loose mode, changing syntax and behavior to make it more consistent with standard SQL. Check the inserted data. If it does not conform to the defined type or length, adjust or truncate the data type and save it, and warn. For the errors mentioned at the beginning of this article, you can set sql_mode to ANSI mode first, so that you can insert data, and the database will replace the field value of the result with NULL value for divisor 0. Set the current database schema to ANSI schema:

mysql> set @@sql_mode=ANSI;

2. TRADITIONAL mode: strict mode. When inserting data into mysql database, strict verification of data is carried out to ensure that error data cannot be inserted.

error, not just a warning. When applied to things, things roll back. Note: Discard INSERT/UPDATE as soon as an error is found. If you are using a non-transactional storage engine, this is not the way you want it, because data changes made prior to the error do not "roll" and the result is updates "only partially made."

Set the current database mode to TRADITIONAL mode:

mysql> set @@sql_mode=TRADITIONAL;

STRICT_TRANS_TABLES mode: strict mode, strict verification of data, error data can not be inserted, error reported. If the given value cannot be inserted into the transaction table, the statement is discarded. For non-transactional tables, if the value appears on line 1 of a single-line or multiple-line statement, the statement is discarded. Set the current database mode to STRICT_TRANS_TABLES mode:

mysql> set @@sql_mode=STRICT_TRANS_TABLES;

There is no best or worst model, only the most appropriate model. Need to choose the most suitable mode according to their actual situation!!!

Another point to say, the change database mode here is session level, one-time, closed and then open does not count!!!

You can also set it via the configuration file:vim /etc/my.cnf

Add the following configuration to my.cnf (my.ini):

[mysqld]sql_mode='The mode you want'

summary

The above is a small series to introduce you to MySQL using group by is always 1055 error, I hope to help you!

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.