Using group by in MySQL always results in error 1055 (recommended)

Because using group by in MySQL always results in error 1055, I have to check the cause. I have searched for relevant information and now I am recording the notes for reference later:

sql_mode: In short, it defines the SQL syntax that your MySQL should support, data validation, etc.

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 meaning of each value of sql_mode:

ONLY_FULL_GROUP_BY:

For GROUP BY aggregation operations, if the columns in SELECT do not appear in GROUP BY, then this SQL will be considered illegal because the columns are not in the GROUP BY clause.

Because of only_full_group_by, 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 fields can be displayed, and all others will report error 1055)

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','')); You can use this statement to replace only_full_group_by with a space, 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)

However, 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 interrupted, and no restrictions are imposed on non-transactional tables.

NO_ZERO_IN_DATE:

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

NO_ZERO_DATE:

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

ERROR_FOR_DIVISION_BY_ZERO:

In strict mode, during an INSERT or UPDATE, division by zero (or MOD(X, 0)) generates an error (otherwise a warning). If this mode is not given, MySQL returns NULL on division by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generates a division-by-zero warning, but the result of the operation is NULL.

NO_AUTO_CREATE_USER:

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

NO_ENGINE_SUBSTITUTION:

If a required storage engine is disabled or not compiled in, an error is thrown. If this value is not set, the default storage engine is used instead and an exception is thrown.

3. It is said that MySQL 5.0 and above versions support three sql_mode modes: ANSI, TRADITIONAL and STRICT_TRANS_TABLES.

1. ANSI mode: loose mode, changes syntax and behavior to make it more consistent with standard SQL. Verify the inserted data. If it does not conform to the defined type or length, adjust the data type or truncate it before saving, and issue a warning. For the error mentioned at the beginning of this article, you can first set sql_mode to ANSI mode, so that you can insert data, and for the field values resulting from the divisor being 0, the database will replace them with NULL values. Set the current database mode to ANSI mode:

 mysql> set @@sql_mode=ANSI;

2. TRADITIONAL mode: Strict mode. When inserting data into the MySQL database, strict data verification is performed to ensure that incorrect data cannot be inserted.

error Errors, not just warnings. When used for transactions, the transaction will be rolled back. Note: Abort the INSERT/UPDATE immediately if an error is found. This is not what you want if you are using a non-transactional storage engine, because data changes made before the error will not be "rolled over", resulting in "partially executed" updates.

Set the current database mode to TRADITIONAL mode:

mysql> set @@sql_mode=TRADITIONAL;

3. STRICT_TRANS_TABLES mode: Strict mode, strict data verification is performed, incorrect data cannot be inserted, and an error is reported. If the given value cannot be inserted into the transaction table, the statement is aborted. For nontransactional tables, if the value appears in a single-row statement or in row 1 of a multi-row statement, the statement is abandoned. 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 suitable model. You need to choose the most suitable mode according to your actual situation! ! !

Another point is that the changes to the database mode here are all at the session level, one-time, and it will not count if you close and reopen it! ! !

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

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