php - 如何解决 mysql group_concat 限制？

Question

我有一个表格，显示客户的任务列表。每个任务都可以有多个笔记。任务和注释保存在单独的表中，但通过执行 group_concat 的子查询将其放在该表中，以便能够显示任务和该任务的注释之间的多对一关系。问题是冗长的注释不显示全文。注释表中的字段包含所需的全文。它是一个blob数据类型，数据长度为1191字节。我的研究告诉我，使用 group_concat 进行此选择会使返回的 Note 字段超过 1024 个字节，这是限制。事实上，'group_concat_max_len' 设置为 1024。我的老板指示我与其增加数据限制，不如弄清楚如何在限制内工作。

<div class="portlet light bordered">
         <div class="portlet-title">
             <div class="col-md-8">
                <div class="caption font-dark">
                     <i class="fa fa-tasks font-dark"></i>
                     <span class="caption-subject bold uppercase">Task List</span>
                </div>
             </div>
        
         </div>

    <div class="portlet-body">
        <table class="table table-striped table-bordered table-hover dt-responsive" 
         width="100%" id="sample_3" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th class="all">Task Step</th>
                    <th class="all">Envr</th>
                    <th class="all">Task Name</th>
                    <th class="all">Task Description</th>
                    <th class="none">Notes:</th>
                    <th class="all">Due Date</th>
                    <th class="all">Current Status</th>
                </tr>
            </thead>
            <tbody>
                <?php    

                include "../includes/DBConn.php";

                $sql = "SELECT ID, FID , TaskCategory, Environment, TaskName, TaskDescription, 
                CreateDate, Cast(date_format(DueDate, \"%m-%d-%Y\") as char) as DueDate, 
                CompletedDate, CompletedBy, CurrStatus,
                (SELECT GROUP_CONCAT(Cast(date_format(DATE(CreateDate),\"%m-%d-%Y\") as 
                char), \" <strong>\" , Cast(TIME(CreateDate) as char) , \"</strong> : \" , 
                Notes ORDER BY CreateDate ASC SEPARATOR '<br>')
                FROM arc.notes B where A.id = B.taskid
                ) as Notes
                FROM arc.task A
                WHERE FID = '$FID'";
                $result = $conn->query($sql);
                

                if ($result->num_rows > 0) {
                    // output data of each row
                    while($row = $result->fetch_assoc()) {
                    
                    $TaskID = $row['ID'];    
                    $TaskStep = $row['TaskCategory'] . $row['ID'];
                    $Environment = $row['Environment'];
                    $TaskName = $row['TaskName'];
                    $TaskDescription = $row['TaskDescription'];
                    $Notes = $row['Notes'];
                    $DueDate = $row['DueDate'];
                    $CompleteDate = $row['CompletedDate'];
                    $CurrStatus = $row['CurrStatus'];
                ?>

                <tr>
                    <td><?php echo "$TaskStep "; if(!empty($Notes)) { echo " <i class=\"fa fa- 
                    sticky-note\"></i>";} ?></td>
                    <td><?php echo "$Environment"; ?></td>
                    <td><?php echo "$TaskName"; ?></td>
                    <td><?php echo "$TaskDescription"; ?></td>
                    <td><a class="btn btn-circle btn-xs red-mint" data-toggle="modal" 
                        href="#newNote<?php echo$TaskID;?>"><i class="fa fa-plus"></i>Add Note 
                        </a> 
                        <br>
                        <?php echo $Notes; ?>
                    </td>
                    <td><?php echo "$DueDate"; ?></td>
                    <td><?php switch ($CurrStatus) {
                            case (0): echo "<strong><a class=\"btn btn-xs btn-primary btn- 
                                      block\" data-toggle=\"modal\" href=\"#StatusChange" . 
                                      $TaskStep . "\">AWAITING ACTION</strong>";
                                      break;
                            case (1): echo "<strong><a class=\"btn btn-xs btn-success btn- 
                                      block\" data-toggle=\"modal\" href=\"#StatusChange" . 
                                      $TaskStep . "\">IN PROGRESS</strong>";
                                      break;
                            case (2): echo "<strong><a class=\"btn btn-xs grey-gallery btn- 
                                      block\" data-toggle=\"modal\" href=\"#StatusChange" . 
                                      $TaskStep . "\">COMPLETED</strong>";
                                      break;
                            default: echo "No Status";
                        }
                        ?>
                    </td>
            </tbody>
        </div>
    </div>
</div>  

标签： phpmysqldatabase