Detailed example of concatenating multiple fields in mysql

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

Recommend

Vue practice of preventing multiple clicks

Generally, click events will be divided into diff...

In-depth analysis of the various backgrounds, usage scenarios and techniques of CSS

This article has been included on GitHub https://...

Overview of MySQL Statistics

MySQL executes SQL through the process of SQL par...

vue-element-admin global loading waiting

Recent requirements: Global loading, all interfac...

jQuery implements a simple carousel effect

Hello everyone, today I will share with you the i...

MySQL master-slave synchronization mechanism and synchronization delay problem tracking process

Preface As a DBA, you will often encounter some M...

A method of making carousel images with CSS3

Slideshows are often seen on web pages. They have...

mysql-8.0.17-winx64 deployment method

1. Download mysql-8.0.17-winx64 from the official...

JavaScript implements H5 gold coin function (example code)

Today I made a Spring Festival gold coin red enve...

Detailed explanation of using tcpdump command to capture and analyze data packets in Linux

Preface tcpdump is a well-known command-line pack...

19 MySQL optimization methods in database management

After MySQL database optimization, not only can t...

Detailed tutorial on OpenStack environment deployment based on CentOS (OpenStack installation)

Effect display: Environment preparation controlle...

How to solve the problem that MySQL cannot start because it cannot create PID

Problem Description The MySQL startup error messa...

element-ui Mark the coordinate points after uploading the picture

What is element-ui element-ui is a desktop compon...

Linux command line quick tips: How to locate a file

We all have files stored on our computers -- dire...