The GROUP_CONCAT(expr) function concatenates an empty string from expr. If there is no non-empty string, it will return NULL. The syntax is as follows:

It is widely used in recursive query, but it is not easy to use it well. So let's take a look:

Suppose there is such a tree table t_region of the connection table model, and its basic structure is as follows:

1 comma as the default connection character.

2 you can use DISTINCT to filter duplicate values, or add ORDER BY to sort the values, or use SEPARATOR to specify separators:

The return values here are filtered by repeated values, sorted in reverse order, and spaces are used as separators.

GROUP_CONCAT () has a maximum length limit. The default value is 1024.

It can be set dynamically through the group_concat_max_len parameter. Parameter ranges can be global or conversational.

The setting syntax is as follows:

This value is an unsigned integer, and the maximum value is related to the number of version digits:

If the value of group_concat_max_len is set to 5 12 or less, the return value type of group_concat is VARCHAR or VARBINARY；; Otherwise it is text or BLOB.

In fact, the value of group_concat_max_len can be set larger, but it will be limited by the parameter max_allowed_packet.

Many people don't know this, because it only appears when there is a large amount of data.

When 1 uses the default value:

It can be seen that when the total length reaches 1024, the following records are truncated.

2group_concat_max_len is set to the maximum value:

After this setting, under normal circumstances, the string of GROUP_CONCAT will no longer be truncated O(∩_∩)O~