MySQL 字符串拼接和聚合函数详解

旧城等待， 2024-05-01 16:29 53阅读 0赞

在数据库查询中，字符串拼接和聚合是常见的操作。MySQL 提供了一些函数来处理这些任务，其中包括 `CONCAT()`、`CONCAT_WS()` 和 `GROUP_CONCAT()` 函数。本文将详细介绍这些函数的用法，并提供具体的例子。

#### 1. CONCAT() 函数 ####

`CONCAT()` 函数用于将多个字符串连接成一个字符串。其基本语法如下：

CONCAT(str1, str2, ...)

##### 示例 #####

假设我们有一个名为 `employees` 的表，其中包含 `first_name` 和 `last_name` 列。我们可以使用 `CONCAT()` 函数将它们连接成一个完整的姓名：

SELECT CONCAT(first_name, ' ', last_name) AS full_name
    FROM employees;

#### 2. CONCAT\_WS() 函数 ####

`CONCAT_WS()` 函数类似于 `CONCAT()` 函数，但它允许指定一个分隔符，在多个字符串之间添加分隔符。其语法如下：

CONCAT_WS(separator, str1, str2, ...)

##### 示例 #####

假设我们有一个名为 `addresses` 的表，包含 `street_address`、`city` 和 `postal_code` 列。我们可以使用 `CONCAT_WS()` 函数将它们连接成一个完整的地址，并用逗号分隔：

SELECT CONCAT_WS(', ', street_address, city, postal_code) AS full_address
    FROM addresses;

#### 3. GROUP\_CONCAT() 函数 ####

`GROUP_CONCAT()` 函数用于在对结果进行分组时，将每个组中的值连接成一个字符串。其语法如下：

GROUP_CONCAT(expression ORDER BY order_expression SEPARATOR separator)

##### 示例 #####

假设我们有一个名为 `employees` 的表，包含 `employee_id`、`employee_name` 和 `department_id` 列。我们想要获取每个部门的所有员工姓名，并将它们连接成一个字符串，可以使用 `GROUP_CONCAT()` 函数：

SELECT department_id, GROUP_CONCAT(employee_name ORDER BY employee_id SEPARATOR ', ') AS employee_list
    FROM employees
    GROUP BY department_id;
    
    
    //实例
    SELECT 
        t1.row_id as id,
        t1.order_no AS '订单编号',
     
        GROUP_CONCAT(DISTINCT t4.logistics_no order by  t4.logistics_no desc SEPARATOR ', ') as '物流单号'
    FROM 
        lnk_sale_order t1
    LEFT JOIN 
        lnk_accnt t2 ON t1.acct_id = t2.row_id
    LEFT JOIN 
        lnk_emp_info t3 ON t1.salesmen_id = t3.row_id
    LEFT JOIN 
        lnk_delivery t4 ON t4.order_id = t1.row_id
    WHERE 
       t1.order_no = '23LGDBJ014909280017'
    GROUP BY
        t1.order_no;

### 踩坑记录 ###

#### 1.数据量太大 ####

当数据太大，group\_concat超出了默认值1024，超过就会截断，group\_concat查询出来的数据就是不全。

解决：

1.查看当前 mysql group\_concat\_max\_len：  
进入mysql状态，输入：

show variables like 'group_concat_max_len';

结果如下：

可以看到当前值为1024.

2.在MySQL配置文件中添加配置：group\_concat\_max\_len = -1 （-1为最大值或根据实际需求设置长度），配置后需要重启MySQL服务  
如果是生产环境下，不能擅自重启MySQL服务，则可以通过语句设置group\_concat的作用范围，如：

set session group_concat_max_len = -1

#### 2.权限问题 ####

> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'linkbase.t1.row_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

如果报错

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))
    > 1227 - Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

是因为没有足够的权限来执行`SET GLOBAL`命令。这需要`SUPER`或`SYSTEM_VARIABLES_ADMIN`权限。

如果你无法获取这些权限，你可以尝试使用`SET SESSION`来临时修改`sql_mode`，而不是全局修改。这种方法只会在当前会话中生效，并且不需要特殊的权限。这样做只会在当前会话中移除`only_full_group_by`，而不会影响全局设置。

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));