mysql - 从逗号分隔的列中获取不同的值并将其插入到mysql中的另一个表中

Question

我在 MySQL 表中有一列，该列由逗号分隔的值组成，并且同一列中存在各种重复值。数据如下所示-

从表名中选择 id,category_ids;
+--------+------------------------+
|编号 |类别 ID |
+--------+------------------------+
| 1062810 | 4,7,2,2,2,2,4,7 | 4,7,2,2,2,2,4,7 |
| 1062812 | 4 |
| 1062814 | 7,7,7,7,7,7,7,7 | 7,7,7,7,7,7,7,7 |
| 1062850 | 2,2,2,2,2,2,2,2,2,2,2 | 2,2,2,2,2,2,2,2,2,2,2 |
| 1063294 | 6,6,6,6,6,6,6,6,6,6,6 | 6,6,6,6,6,6,6,6,6,6,6 |


输出应该是

+---------+------------------------+
|编号 |类别 ID |
+--------+------------------------+
| 1062810 | 4,7,2 |
| 1062812 | 4 |
| 1062814 | 7 |
| 1062850 | 2 |
| 1063294 | 6 |


请帮助我，我是 mysql 新手

Answer 1

试试这个:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "select id,category_ids from tableName";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $id = $row["id"];
        $category_ids = array_unique(explode(',',$row["category_ids"]));

        $update_sql = "update tableName category_ids='".implode($category_ids,',')."' where id = ".$id ;
        $res = $conn->query($update_sql,$conn);

        echo "Update ID-".$id." Category ids:" .$row["category_ids"]. " Into " . implode($category_ids,','). "<br>";
    }
} else {
    echo "No data in your table";
}
$conn->close();