What is the mysql column conversion function?

In mysql, the column conversion function is the "group_concat()" function; this function is used to merge non-null column values ​​​​according to the grouping conditions and finally return them. If there are null values ​​in them, the returned result is empty. , the syntax is "select group_concat(name separator ';') column name from table name;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the mysql column conversion function

GROUP_CONCAT(expr) This function combines non-null column values ​​​​according to the grouping conditions and finally returns. If there is a null value, it will be returned as null

In MySQL, how to convert a column into a row? For example, each product will belong to multiple categories (for example, Huawei mobile phones can be classified into mobile phones or digital categories). How to display all categories of this product in one piece of data.

The idea is very simple and can be solved through the MySQL function group_concat.

Create test table:

create table test.test_mysql_liezhuanhang (
id         bigint auto_increment comment '主键',
name       varchar(100),
age        int(5),
primary key (id)
)engine=innodb default charset=utf8mb4 comment='测试表'

Import test data:

insert into test.test_mysql_liezhuanhang (name, age) values ('李威', 18), ('李威', 19), ('李威', 18), ('李白', 20), ('李白', 20), ('李白', 19);

Display all names, separated by semicolon, default separated by ,

select group_concat(name  separator ';') concat
  from test.test_mysql_liezhuanhang 
;

Show all names, remove duplicates from the same names

select group_concat(distinct name) concat
  from test.test_mysql_liezhuanhang 
;

Show all ages, remove duplicates and sort by age in ascending order

select group_concat(distinct age order by age) concat
  from test.test_mysql_liezhuanhang 
;

Recommended learning: mysql video tutorial

The above is the detailed content of What is the mysql column conversion function?. For more information, please follow other related articles on the PHP Chinese website!