Concat and group_concat traps

Group_concat:

Connection trap of the 1.int Field

When using group_concat, note that if the connected field is of the int type, it must be converted to Char and then combined,
Otherwise, after you execute the statement (executescalar or any other method that executes the SQL return result), the returned result will not be a comma-separated string,
Instead, it is byte [].

This problem cannot be found in sqlyog or other tools.

Select group_concat (IPaddress) from t_ip returns comma-separated strings
Select group_concat (ID) from t_ip return byte []
Select group_concat (cast (ID as char) from t_dep returns comma-separated strings
Select group_concat (convert (ID, char) from t_dep returns comma-separated strings

Attached cast and convert usage:
Cast (expr as type), convert (expr, type), convert (expr using transcoding_name)
Cast () and convert () functions can be used to obtain values of one type and generate values of another type.

This type can be one of the following values:

Binary [(n)]
Char [(n)]
Date
Datetime
Decimal
Signed [integer]
Time
Unsigned [integer]

2. Length trap
When using group_concat to connect a field, there is a limit on the length, not the number of connections. But you can set it.

Using group_concat_max_len system variables, you can set the maximum allowed length.
ProgramThe syntax for this operation is as follows, where Val is an unsigned integer:
Set [session | Global] group_concat_max_len = val;
If the maximum length has been set, the result is ended with the maximum length.

Run set global group_concat_max_len = 10 in sqlyog and re-open sqlyog. The setting will take effect.

Note that this method is only temporary. to modify it for a long time, you need to modify the configuration section of MySQL.

Add group_concat_max_len = 99999 to the mysqld node of my. CNF ......

Restart MySQL.

 

Concat:

Used to connect fields or characters. If one of the joined fields is null, Concat returns NULL.

This is a very dangerous thing. I believe that most of the results that people who use Concat to connect multiple fields don't look like this:

Concat (A, B). If B is null, Concat (a, B) = NULL

What we want is:

Connect a and B. If B is null, it should be a after connection.

In this case, use concat_ws (separator, str1, str2 ,...)