Detailed example of concatenating multiple fields in mysql

The MySQL query result row field splicing can be implemented using the following two functions:

1. concat function

mysql> select concat('1','2','3') from test;
+---------------------+
| concat('1','2','3') |
+---------------------+
| 123 |
+---------------------+

If there is NULL in the connection string, the return result is NULL:

mysql> select concat('1','2',NULL,'3') from test;
+--------------------------+
| concat('1','2',NULL,'3') |
+--------------------------+
| NULL |
+--------------------------+

2. concat_ws function

concat(separator,str1,str2,...) stands for concat with separator and is a special form of concat(). The first parameter is the separator for the other parameters. The delimiter is placed between the two strings to be concatenated. The separator can be a string or other parameters.

mysql> select concat_ws(':','1','2','3') from test;
+----------------------------+
| concat_ws(':','1','2','3') |
+----------------------------+
| 1:2:3 |
+----------------------------+

If the delimiter is NULL, the return value is NULL:

mysql> select concat_ws(NULL,'1','2','3') from test; 
+-----------------------------+
| concat_ws(NULL,'1','2','3') |
+-----------------------------+
| NULL | 
+-----------------------------+

If there is NULL in the parameter, it will be ignored:

mysql> select concat_ws(':','1','2',NULL,NULL,NULL,'3') from test;
+-------------------------------------------+
| concat_ws(':','1','2',NULL,NULL,NULL,'3') |
+-------------------------------------------+
| 1:2:3 |
+-------------------------------------------+

You can judge NULL and replace it with other values:

mysql> select concat_ws(':','1','2',ifNULL(NULL,'0'),'3') from bank limit 1; 
+---------------------------------------------+
| concat_ws(':','1','2',ifNULL(NULL,'0'),'3') |
+---------------------------------------------+
| 1:2:0:3 | 
+---------------------------------------------+

Supplement: Supplement: When grouping in MySQL, concatenate the values of a field

t_dog table

t_vaccine table

t_dog_vaccine table

Problem Description

I need to query the vaccine corresponding to each dog in dog_vaccine. Since there is a many-to-many relationship between dogs and vaccines, one dog may correspond to multiple vaccines, but I want to concatenate these multiple vaccines into one using strings and then map them to a Java entity class.

Solve it step by step

I used two left join queries to associate the three tables t_dog, t_vaccine, and t_dog_vaccine.

select dv.id as id,
 d.dog_name as dogName,v.vaccine_name 
 from 
 t_dog_vaccine dv
  left join t_dog d on dv.dog_id = d.id
  left join t_vaccine v on dv.vaccine_id = v.id

Although this matches the dog and the vaccine, there are multiple records for each dog, and I only want data such as the dog's name and the values of multiple vaccine_names merged into a string. After thinking about it, I suddenly thought of group by.

select dv.id as id,
 d.dog_name as dogName,v.vaccine_name 
 from 
 t_dog_vaccine dv
  left join t_dog d on dv.dog_id = d.id
  left join t_vaccine v on dv.vaccine_id = v.id 
  group by dogName

Using group by is obviously not the result I expected. Although there is only one dogName, there is only one vaccine corresponding to it. I want multiple vaccine names, what should I do? Is there a function that can do string concatenation? Emmmmm, got it, group_concat. . .

select dv.id as id,
 d.dog_name as dogName,
 group_concat(v.vaccine_name) as dogVaccineName  
 from 
 t_dog_vaccine dv
  left join t_dog d on dv.dog_id = d.id
  left join t_vaccine v on dv.vaccine_id = v.id 
 group by d.dog_name

This has achieved the effect I wanted.

group_concat usage

Default usage

select group_concat(vaccine_name) as dogVaccineName from t_vaccine where id in(select vaccine_id from t_dog_vaccine where dog_id = 1)

The default delimiter for group_concat is ','. If we want to change this delimiter, we can do so.

REPLACE(group_concat(vaccine_name),',','Fill in the separator you want to change here')

For example, I want to change the default one to;

select REPLACE(group_concat(vaccine_name),',',';') as dogVaccineName from t_vaccine where id in(select vaccine_id from t_dog_vaccine where dog_id = 1)

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:

How to assign default values to fields when querying MySQL
Add a field to the table in the MySQL command line (field name, whether it is empty, default value)
MySQL table field setting default value (graphic tutorial and pay attention to details)
In-depth explanation of hidden fields, a new feature of MySQL 8.0
A brief discussion on which fields in Mysql are suitable for indexing
mysql update case update field value is not fixed operation
When modifying a record in MySQL, the update operation field = field + string
How to split and merge multiple values in a single field in MySQL
Detailed analysis of the syntax of Mysql update to modify multiple fields and
How to set the default value of a MySQL field

<<: Web page HTML code explanation: ordered list and unordered list

>>: css Get all elements starting from the nth one