Example of CONCAT (string concatenation function) and GROUP_CONCAT _ MySQL

An example of CONCAT (string concatenation function) and GROUP_CONCAT CONCAT

Sometimes, we need to link the data obtained from different columns. Each database provides methods to achieve this purpose:

MySQL: CONCAT ()
Oracle: CONCAT (), |
SQL Server: +

The CONCAT () syntax is as follows:
CONCAT (String 1, string 2, string 3,...): concatenates string 1, string 2, string 3, and other words.
Note that Oracle CONCAT () only supports two parameters;
In other words, only two strings can be connected at a time. However, in Oracle, we can use '|' to concatenate multiple strings at a time.
Let's look at several examples. Suppose we have the following table:
Geography Table
Region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

Example 1:
MySQL/Oracle:
Select concat (region_name, store_name) FROM Geography
WHERE store_name = 'Boston ';

Result:
'Astboston'

Example 2:
Oracle:
SELECT region_name | ''| store_name FROM Geography
WHERE store_name = 'Boston ';

Result:

'East Boston'

Example 3:

SQL Server:
SELECT region_name + ''+ store_name FROM Geography
WHERE store_name = 'Boston ';

Result:

'East Boston'

GROUP_CONCAT

GROUP_CONCAT ()It is a function provided BY the MySQL database. it is usually used together with group by. for details, refer to the MySQL official document: Batch.
Syntax:
GROUP_CONCAT ([DISTINCT] expr [, expr...]
[Order by {unsigned_integer | col_name | expr}
[ASC | DESC] [, col_name...]
[SEPARATOR str_val])

1. for example:
SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id;

+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 3, 4, 5 |
+ ------------ + --------- +

This requires no php loop.
$ Row = $ pdo-> query ("SELECT student_id, GROUP_CONCAT (courses_id) AS courses FROM student_courses WHERE student_id = 2 group by student_id ");
$ Result = explode (',', $ row ['course']);

2. of course, the SEPARATOR can also be customized. the default delimiter is ",". to change it to "|", use SEPARATOR. for example:
SELECT student_id, GROUP_CONCAT (courses_id SEPARATOR '|') AS courses FROM student_courses WHERE student_id = 2 group by student_id;

+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 3 | 4 | 5 |
+ ------------ + --------- +
3. In addition, you can sort the values of this group and connect them to strings. for example, sort the values by courses_id in descending order:

SELECT student_id, GROUP_CONCAT (courses_id order by courses_id DESC) AS courses FROM student_courses WHERE student_id = 2 group by student_id;
+ ------------ + --------- +
| Student_id | courses |
+ ------------ + --------- +
| 2 | 5, 4, 3 |
+ ------------ + --------- +



4. notes:


A.int field connection trap


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]


B. length trap
After group_concat is used, it does not work if limit is used in select.
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.
The syntax for this operation in the program 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.