php - 使用 PHP 从 MySQL 列中删除重复的逗号分隔值

Question

我有一个包含如下列的数据库:

id     options
1      Website,Website,Newspaper,Newspaper,TV,TV,Radio,Radio
2      Website,Website,Newspaper,Newspaper
3      Website,Website,TV,TV

目标是删除重复的条目并将 options 列规范化为:

id     options
1      Website,Newspaper,TV,Radio
2      Website,Newspaper
3      Website,TV

我开发了以下 PHP 代码:

$sql = "SELECT id, options FROM table";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $id = $row['id'];
        $values_array = explode( ',' , $row['options'] );
        if(count($values_array) != count(array_unique($values_array)))
        {
            $likes = array_unique($values_array);
            $new = implode(',', $likes);
            $sql = "UPDATE table SET options=".$new." WHERE id = '$id'";
        }
    }
} else {
    echo "0 results";
}
$conn->close();

这并不能完成工作。一切似乎都正常，但尝试使用新的数组数据更新 options 列。

这似乎并不太难，只是寻找一些关于如何让它工作的指导。

提前致谢!

Answer 1

可以直接在mysql里做

UPDATE T
 JOIN
 (SELECT id,GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(t.options, ',', sub0.aNum), ',', -1)) AS ids
FROM t
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(t.options) - LENGTH(REPLACE(t.options, ',', ''))) >= sub0.aNum
GROUP BY id)x
ON x.id=t.id
SET t.options=x.ids

FIDDLE

受到这个answer的启发