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

Zabbix monitoring solution - the latest official version 4.4 [recommended]

Zabbix 2019/10/12 Chenxin refer to https://www.za...

Detailed explanation of JavaScript array deduplication

Table of contents 1. Array deduplication 2. Dedup...

A brief discussion on the differences between the three major databases: Mysql, SqlServer, and Oracle

MySQL advantage: Small size, fast speed, low tota...

Common causes and solutions for slow MySQL SQL statements

1. Slow query due to lack of index or invalid ind...

Detailed tutorial on installing Spring boot applications on Linux systems

Unix/Linux Services systemd services Operation pr...

CocosCreator Getting Started Tutorial: Network Communication

Network Communication Overview When developing an...

Vue implements DingTalk's attendance calendar

This article shares the specific code of Vue to i...

The vue configuration file automatically generates routing and menu instance code

Table of contents Written in front router.json Ro...

Compilation process analysis of Remax framework for writing small programs using React (recommended)

Remax is an open source framework developed by An...

JavaScript to implement voice queuing system

Table of contents introduce Key Features Effect d...

Mysql online recovery of undo table space actual combat record

1 Mysql5.6 1.1 Related parameters MySQL 5.6 adds ...

Solution to the problem "/bin/sh: pip: command not found" during Dockerfile build

The content of the written Dockerfile is: FROM py...

Share 12 commonly used Loaders in Webpack (Summary)

Table of contents Preface style-loader css-loader...

In-depth explanation of currying of JS functions

Table of contents 1. Supplementary knowledge poin...

Detailed graphic tutorial on installing Ubuntu 20.04 dual system on Windows 10

win10 + Ubuntu 20.04 LTS dual system installation...