SELECT q.*, a.answer FROM `questions` q LEFT JOIN ( SELECT question_id, GROUP_CONCAT(text SEPARATOR '|| ') answer FROM answers GROUP BY question_id ) a ON q.uid = a.question_id WHERE q.deleted = 0 GROUP BY q.uid

This query selects questions and answers to them, sticking them together. In the plate, the answers are stored as text . When sampling and concatenation of the text is cut off. For example, if I have 3 large answers, the whole first answer and half of the second return me somewhere. If you make these answers small in the database, everything is fine. What is the problem here?

  • 2
    SET SESSION group_concat_max_len = 1000000; - Vfvtnjd
  • Please issue as an answer, and I will note. Maybe someone else will come in handy. - VK

1 answer 1

The GROUP_CONCAT function has a limit on the amount of output. By default, 1024 characters for each join are for each output line. If the size of the data received as a result of concatenation is larger, the displayed text will be cut off.

To expand the size, run the command:

 SET SESSION group_concat_max_len = 1000000;

or SET @@group_concat_max_len = 1000000; (similar to the previous one).

But this command will be applied only to the current session. At the end of the session, for example, when opening a new connection, the value of this variable will return to the initial value. To set a global variable, run the following command:

 SET GLOBAL group_concat_max_len = 1000000;