Resolving MySQL GROUP_CONCAT Length Limit Issues in Doctor Scheduling

This article shares a problem encountered at work regarding the MySQL GROUP_CONCAT function and aims to help readers facing similar issues.

Opening Remarks

After a year of changes in work and personal life, the author reflects on the passing of time and thanks friends for their support.

Business Scenario

The system includes a scheduling feature where a doctor can have multiple business types per day, each supporting multiple time slots (minimum unit 15 minutes).

Example:

【Online A】 2020-12-31 8:00~10:00
【Online A】 2020-12-31 10:30~12:00
【Online B】 2020-12-31 8:00~10:00
【Online B】 2020-12-31 10:30~12:00

To query a day's schedule, the following pseudo‑SQL is used for clarity:

SELECT
  dt,
  biz_type,
  GROUP_CONCAT(
    CONCAT_WS('|', id, start_time, end_time)
  ) AS multiPeriodDetail
FROM doctor_schedule_detail
WHERE doctor_id = '456231'
  AND dt = '2020-12-30'
GROUP BY doctor_id, biz_type, dt;

The CONCAT_WS('|', id, start_time, end_time) function concatenates the fields id (slot ID), start_time , and end_time with a pipe separator.

After grouping, GROUP_CONCAT combines the strings, using a comma as the default separator.

The query result is displayed as an image (omitted here).

The Pitfall

Although the code had run fine for a long time, a user reported that a doctor's schedule could not be found on the UI.

Investigation revealed that because each 15‑minute slot generates many concatenated strings, the result exceeded the default GROUP_CONCAT length limit, causing data truncation.

Note: the configuration variable group_concat_max_len defaults to 1024 bytes.

To view the current setting:

show variables like "group_concat_max_len";

Tip: Field Byte Length

In MySQL UTF‑8, a Chinese character occupies 3 bytes.

-- Character length result: 6
SELECT CHAR_LENGTH("你好2021");
-- Byte length result: 10
SELECT LENGTH("你好2021");

Solution

Two approaches were considered after identifying the cause:

First Approach: Increase the MySQL configuration value group_concat_max_len .

Method 1 (recommended for production): Add the following line to the MySQL configuration file:

group_concat_max_len = 102400

Method 2 (quick test): Execute SQL statements:

SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;

In production, Method 1 is preferred because changing the configuration requires proper procedures and approvals.

Second Approach: Adjust the business logic to reduce the amount of data concatenated by GROUP_CONCAT , e.g., by changing requirements or limiting the number of returned rows.

Changing already‑deployed functionality may impact the business, so this option was considered less favorable.

After weighing the options, the team chose to increase group_concat_max_len to a value calculated based on the specific workload.

Conclusion

In real‑world scenarios, problems often have multiple viable solutions; selecting the optimal one requires balancing technical constraints and business impact.