如何拆分逗号implode存储的数据并统计explode后每个值的出现次数

你可以选择PHP层拆分统计（兼容性最好，适配所有MySQL版本）或者MySQL层直接拆分统计（适合MySQL 8.0及以上版本，逻辑更简洁）两种方案实现：

方案1：PHP层处理（推荐） #

直接读取所有nama_tmp字段值，拆分后统计计数即可，代码修改如下：

var chart4; 
$(document).ready(function() {
    chart4 = new Highcharts.Chart({
        chart: {
            renderTo: 'mygraph4',
            type: 'column'
        },   
        title: {
            text: 'Grafik Mekanik'
        },
        xAxis: {
            categories: ['Mekanik']
        },
        yAxis: {
            title: {
                text: 'Total Perbaikan'
            }
        },
        series: [
            <?php 
            include "system/koneksi.php";
            // 初始化姓名计数数组
            $nameCount = [];
            $sql = "SELECT nama_tmp FROM tb_tmp";
            $query = mysqli_query($connect, $sql) or die(mysqli_error($connect));
            while($row = mysqli_fetch_array($query)) {
                // 按逗号拆分字符串，逐个去除前后空格后统计
                $names = explode(',', $row['nama_tmp']);
                foreach($names as $name) {
                    $trimmedName = trim($name);
                    if($trimmedName !== '') { // 跳过空值
                        $nameCount[$trimmedName] = isset($nameCount[$trimmedName]) ? $nameCount[$trimmedName]+1 : 1;
                    }
                }
            }
            // 输出图表需要的series数据
            foreach($nameCount as $name => $total) {
            ?>
            {
                name: '<?php echo addslashes($name); ?>',
                data: [<?php echo $total; ?>]
            },
            <?php } ?>
        ]
    });
}); 

该方案修改量小，同时修复了原代码的SQL逻辑错误、SQL注入风险，适配所有使用场景。

方案2：MySQL 8.0+ 直接拆分统计 #

如果你的数据库版本为MySQL 8.0及以上，可以通过递归CTE直接在SQL层完成拆分统计，性能更高：

WITH RECURSIVE split_names AS (
    -- 拆分第一条数据
    SELECT
        TRIM(SUBSTRING_INDEX(nama_tmp, ',', 1)) AS name,
        TRIM(SUBSTRING(nama_tmp FROM LENGTH(SUBSTRING_INDEX(nama_tmp, ',', 1)) + 2)) AS remaining
    FROM tb_tmp
    WHERE nama_tmp != ''
    UNION ALL
    -- 递归拆分剩余字符串
    SELECT
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)) AS name,
        TRIM(SUBSTRING(remaining FROM LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)) AS remaining
    FROM split_names
    WHERE remaining != ''
)
SELECT name, COUNT(*) AS total
FROM split_names
GROUP BY name
ORDER BY total DESC;

直接执行上述SQL即可拿到每个姓名对应的出现次数，无需在PHP层做额外处理。

内容的提问来源于stack exchange，提问作者vivie