SQL -- GROUP_CONCAT函数使用

一个收货地址表cb_client_address_receive，一个收货地址联系人表cb_client_address_receive_contact；一个地址对应多个联系人； 优化SQL：SELECT addressReceive.id, addressReceive.country_id, addressReceive.provence_id, addressReceive.city_id, addressReceive.area_id, addressReceive.client_address_receive_detail, addressReceive.zip_code, addressReceive.client_address_receive_long, addressReceive.client_address_receive_lat, addressContact.client_address_receive_contact_code, addressContact.client_address_receive_contact_name, addressContact.client_address_receive_contact_number, addressContact.client_address_receive_contact_email FROM cb_client_address_receive addressReceive JOIN cb_client_address_receive_contact addressContact ON addressContact.enum_delete = 'NORMAL' AND addressContact.client_address_receive_id = addressReceive.id WHERE addressReceive.enum_delete = 'NORMAL' AND addressReceive.id = '1';最小的维度为收货地址id(addressReceive.id),其中关于收货地址联系人多个，使用英文逗号分割.

    你可以尝试使用子查询和GROUP_CONCAT函数来实现将多个联系人以逗号分隔的形式展示出来。以下是优化后的SQL语句示例：

SELECT
    addressReceive.id,
    addressReceive.country_id,
    addressReceive.provence_id,
    addressReceive.city_id,
    addressReceive.area_id,
    addressReceive.client_address_receive_detail,
    addressReceive.zip_code,
    addressReceive.client_address_receive_long,
    addressReceive.client_address_receive_lat,
    GROUP_CONCAT(addressContact.client_address_receive_contact_code SEPARATOR ',') AS client_address_receive_contact_code,
    GROUP_CONCAT(addressContact.client_address_receive_contact_name SEPARATOR ',') AS client_address_receive_contact_name,
    GROUP_CONCAT(addressContact.client_address_receive_contact_number SEPARATOR ',') AS client_address_receive_contact_number,
    GROUP_CONCAT(addressContact.client_address_receive_contact_email SEPARATOR ',') AS client_address_receive_contact_email
FROM cb_client_address_receive addressReceive
JOIN cb_client_address_receive_contact addressContact ON addressContact.enum_delete = 'NORMAL'
    AND addressContact.client_address_receive_id = addressReceive.id
WHERE addressReceive.enum_delete = 'NORMAL' AND addressReceive.id = '1'
GROUP BY addressReceive.id;

这样，查询结果会返回一个收货地址和相应的多个联系人，联系人字段会用逗号分隔。

请注意，GROUP_CONCAT函数在MySQL中默认有长度限制，如果超出限制可能会导致结果截断。如果你预计会有大量联系人时，请考虑使用其他方法或进行分页处理。

GROUP_CONCAT函数在MySQL中默认有长度限制是多少？

在MySQL中，默认情况下，GROUP_CONCAT函数的结果长度限制为1024个字节。这意味着如果GROUP_CONCAT函数返回的字符串超过1024个字节，则结果会被截断。然而，你可以通过设置group_concat_max_len系统变量来增加这个限制。例如，可以使用以下语句将最大长度设置为10,000个字符：

SET SESSION group_concat_max_len = 10000;

请注意，这是会话级别的设置，只对当前连接有效。如果需要在所有会话中更改此限制，请使用SET GLOBAL group_concat_max_len语句。

需要注意的是，增加GROUP_CONCAT函数的结果长度限制可能会增加内存消耗。确保在做出更改之前评估系统资源的使用情况。