mysql - 作为字段值mysql返回的部分字符串

Question

我从 mysql 查询结果中获取不完整字符串值。看起来，当字符串太长时，该值被切片。我正在以 JSON 字符串的形式获取该值。我的本地数据库无论字段值多长都没有问题。

这是我的查询:

CREATE DEFINER=`devdbuser`@`localhost` PROCEDURE `GetTrainingFilesByID`(IN trainingID int)
BEGIN  
  SELECT t1.*, concat(t2.firstname,' ',t2.lastname) as username
  FROM users t2
  INNER JOIN 
  (
    SELECT t.*,
    CONCAT('[',
          GROUP_CONCAT(CONCAT('{"id":"',d.id,
                               '","name":"', d.title, 
                               '","file_path":"',d.doc_path,
                               '","is_video":"',d.is_video,'"}'
                              )), ']'
          ) files 
          FROM training t 
          LEFT JOIN training_documents d 
          ON t.id = d.training_id 
          GROUP BY t.id
  ) t1
  ON t1.updated_by = t2.id
  WHERE t1.id = trainingID
  LIMIT 1;
  END

问题出在 files 字段值上。

这是不完整输出:

"[{"id":"1", "name":"Branch Workflow Model for GBD_1.pdf", "file_path":"1455443689.pdf", "is_video":"0"},
  {"id":"2", "name":"http://192.168.11.32/GBD-Videos/testvideo.mp4", "file_path":"http://192.168.11.32/GBD-Videos/testvideo.mp4", "is_video":"1"},
  {"id":"6", "name":"COD-CC-CrowdWisdom-Report-exclusive_mybigcommerce_com-2018_09_26-05_28_58.pdf", "file_path":"1862665875.pdf", "is_video":"0"},
  {"id":"7", "name":"https://www.youtube.com/watch?v=rCAIY5n1hPA.mp4", "file_path":"https://www.youtube.com/watch?v=rCAIY5n1hPA.mp4", "is_video":"1"},
  {"id":"8", "name":"https://www.youtube.com/watch?v=rCAIY5n1hP1.mp4", "file_path":"https://www.youtube.com/watch?v=rCAIY5n1hP1.mp4", "is_video":"1"},
  {"id":"19", "name":"Branch Workflow Model for GBD.docx", "file_path":"1250685453.docx", "is_video":"0"},
  {"id":"20", "name":"COK_CorporateMedicineDelivery_Growbydata.pdf", "file_path":"1245383653.pdf", "is_video":"0"},
  {"id":"21", "name":"COK_Intro_Growbydata.pdf", "file_path":"1918218679.pdf", "is_video":"0"},
  {"id":"22", "name":"http:/]"

mysql 是否有一些功能可以防止长字段结果值？我该如何解决这个问题？

Answer 1

这主要是因为您正在使用组连接。我建议您运行此查询，以便增加组连接的限制

SET GLOBAL group_concat_max_len=15000

希望这有帮助。 :)