When querying MySQL database, use group_ “Row XXX was cut by group_ Concat () “, check it out because of group_ There is a limit on the maximum length of concat. If it exceeds the maximum length, it will be truncated,
Let’s check the global environment variables of MySQL
mysql> SELECT @@global.group_concat_max_len;
+-------------------------------+
| @@global.group_concat_max_len |
+-------------------------------+
| 1024 |
+-------------------------------+
Check the maximum length of the query field in the production environment
mysql> select max(length(extra)) from credit.apply;
+--------------------+
| max(length(extra)) |
+--------------------+
| 9599 |
+--------------------+
Check the maximum number of aggregations again
mysql> select max(c1) from (select custid,count(1) as c1 from credit.apply group by custid )t;
+---------+
| max(c1) |
+---------+
| 58 |
+---------+
So it is estimated that the maximum possible length is
9599 *58+67=556809
You can change the group_ concat_ max_ Len variable solves the problem
SET group_concat_max_len=556809;
MySQL settings group_ concat_ max_ len
GROUP_ The concat function is used to connect multiple strings into a string. When splicing into a string, there will be the problem of splicing length. The default splicing length of MySQL is 1024 bytes. Because 1024 bytes will not be enough, sometimes it needs to be modified according to the situation. The method is as follows.
1. View the current MySQL group_ concat_ max_ len
Enter the MySQL state and enter: Show variables like ‘group_ concat_ max_ len’;
If it has not been modified, you will get the following result
2. Modify MySQL group_ concat_ max_ len
a) , if it is not convenient to restart mysql, you can set it in the MySQL state through the command, such as:
- SET GLOBAL group_ concat_ max_ len = 102400; SET SESSION group_ concat_ max_ len = 102400;
Check through mode 1.
Note: in this way, the configuration file will be read and reset after the restart of MySQL, which will lead to invalid settings, so it is recommended to modify the configuration file
b) , modify configuration file: my.ini
Add a new configuration under [mysqld]: group_ concat_ max_ len = 102400
Restart and view through mode 1.
In particular, sometimes we don’t know how many bytes we need to meet the requirements. In this case, we can consider not setting the maximum number of bytes (that is, using the maximum number of bytes), that is, setting group in the configuration file_ concat_ max_ len=-1
In this case, you can view the results in mode 1 as follows:
Note: 4294967295 = 2 ^ 32 – 1
Read More:
- Group by query only_ FULL_ GROUP_ By error
- Handling of expression not in group by key [value] reported by hive on October 12, 2020
- Group by operator of hive execution plan
- Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
- Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and co
- MS SQL Could not obtain information about Windows NT group/user ‘domain\login’, error code 0x5. [SQ…
- Concat error caused by tensorflow version
- Failed to set permission group
- Solution to prompt “system group policy forbids installation of this device” in win10 system
- Problem solving of IOS apple mach-o linker error group
- The solution of Maya exporting obj to sp without identifying group information
- RuntimeError: Default process group has not been initialized, please make sure to call init_process_
- Ios-79-resolve error report apple mach-o linker (LD) error group
- The time of the time database displayed by the front end is inconsistent
- Elasticsearch6. X invalid time range query bug
- “Invalid month” in SQL query
- After Nacos started, the client worker log was printed all the time
- Solution to the problem that SQL database query result field contains new line character, which leads to copy to excel dislocation
- ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS sqlState
- Blender graphic tutorial: loop cut for polygon modeling commands