Mysql implements three functions for field splicing

When exporting data to operations, it is inevitable to splice the fields. If MySQL can do it, a lot of code can be saved.

concat()
concat_ws()
group_concat()

Mysql does have several functions that can concatenate fields.

concat()

Use empty strings to concatenate multiple fields into one field

mysql> select concat(id, type) from mm_content limit 10;
+------------------+
| concat(id, type) |
+------------------+
| 100818image |
| 100824image |
| 100825video |
| 100826video |
| 100827video |
| 100828video |
| 100829video |
| 100830video |
| 100831video |
| 100832video |
+------------------+
10 rows in set (0.00 sec)

However, if any field value is NULL, the result is NULL.

mysql> select concat(id, type, tags) from mm_content limit 10;
+------------------------+
| concat(id, type, tags) |
+------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------------------------+
10 rows in set (0.00 sec)

concat_ws()

If you want to use a delimiter to split the fields in the above method, you need to insert a string in the middle of each field, which is very troublesome.

concat_ws() can solve the separator problem at one time, and all values will not be NUll just because one value is NUll.

mysql> select concat_ws(' ', id, type, tags) from mm_content limit 10;
+--------------------------------+
| concat_ws(' ', id, type, tags) |
+--------------------------------+
| 100818 image |
| 100824 image |
| 100825 video |
| 100826 video |
| 100827 video |
| 100828 video |
| 100829 video |
| 100830 video |
| 100831 video |
| 100832 video |
+--------------------------------+
10 rows in set (0.00 sec)

group_concat()

The last one is amazing. Under normal circumstances, a statement written like this will definitely result in an error.

mysql> select id from test_user group by age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

However, group_concat() can concatenate other fields in the grouped state into a string for query

mysql> select group_concat(name) from test_user group by age;
+--------------------+
| group_concat(name) |
+--------------------+
| wen,ning |
| wxnacy,win |
+--------------------+
2 rows in set (0.00 sec)

By default, commas are used to separate the data, but we can also specify a delimiter.

mysql> select group_concat(name separator ' ') from test_user group by age;
+----------------------------------+
| group_concat(name separator ' ') |
+----------------------------------+
| wen ning |
| wxnacy win |
+----------------------------------+
2 rows in set (0.00 sec)

Arrange the strings in a certain order

mysql> select group_concat(name order by id desc separator ' ') from test_user group by age;
+---------------------------------------------------+
| group_concat(name order by id desc separator ' ') |
+---------------------------------------------------+
| ning wen |
| win wxnacy |
+---------------------------------------------------+
2 rows in set (0.00 sec)

If you want to concatenate multiple fields, the default is to use an empty string for concatenation. We can use concat_ws() method to nest one layer.

mysql> select group_concat(concat_ws(',', id, name) separator ' ') from test_user group by age;
+------------------------------------------------------+
| group_concat(concat_ws(',', id, name) separator ' ') |
+------------------------------------------------------+
| 1,wen 2,ning |
| 3,wxnacy 4,win |
+------------------------------------------------------+
2 rows in set (0.00 sec)

The above is the details of the three functions that Mysql implements field splicing. For more information about MySQL string splicing, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: