MySql - Replace "array"-value

本文关键字：quot -value array Replace MySql | 更新日期: 2023-09-27

我在一列中有"1,3,6,17"这样的记录，并使用以下命令删除例如:"6" from them:

$sql = "SELECT SeId, SeCatId FROM series WHERE FIND_IN_SET(:catid,SeCatId)";
$stmt = $dbh->prepare($sql);
$stmt -> bindParam('catid',$id);
$stmt->execute();
$result = $stmt->FetchAll();
foreach($result as $row){
  $seid = $row['SeId'];
  $ids = explode(",",$row['SeCatId']);
  $i = 0;
  foreach($ids as $catid) {
    if($catid===$id){unset($ids[$i]);}
    $i++;
  }
  $ids = implode(",",$ids);
  $sql = "UPDATE series SET SeCatId = :catid WHERE SeId = :id";
  $stmt = $dbh->prepare($sql);
  $stmt -> bindParam(':catid',$ids);
  $stmt -> bindParam(':id',$seid);
  $stmt->execute();
}

工作良好，但似乎是复杂的。有没有更简单的方法来做同样的事情?

如果使用更好的数据库设计，这将很容易。创建一个新表来处理关联，而不是将id存储在逗号分隔的字符串中。

series_cat_assoc ( SeId, SeCatId )

使用它，如果一个序列有多个类别，您可以插入多个行。这使得创建、更新和删除变得容易。例如，从给定系列中删除给定类别:

$sql = "SELECT series_cat_assoc WHERE SeCatId = :catid and SeId = :seid)";

您可以使用array_diff并执行如下操作

$stmt->bindParam(':catid', implode(',', array_diff(explode(",", $row['SeCatId']), array('6')));

但是你的问题不是PHP而是你的SQL模式。你不应该在同一个字段中有"数组"值(阅读数据库规范化了解原因)。正常化就是为了避免这些问题。您应该有一个独立的模式，其中SeId和SeCatId作为列，SeId是序列表的外键，而SeCatId是唯一的整数(不是数组)。

你应该有这样的行:

SeId | SeCatId
1    | 1
1    | 3
1    | 6
1    | 7

然后删除一个类别只需要简单的DELETE语句。

我不知道在算法上是否有更有效的方法，但从我对你代码的理解来看，你可以使用正则表达式来更简洁地完成它。替换:

foreach($result as $row){
$seid = $row['SeId'];
$ids = explode(",",$row['SeCatId']);
$i = 0;
foreach($ids as $catid) {
  if($catid===$id){unset($ids[$i]);}
  $i++;
}
$ids = implode(",",$ids);

与这个:

$ids = str_replace(','.$id.',', ',', $row['SeCatId']);
$ids = preg_replace('/(^|,)('.$id.')(,|$)/', "{$0}{$2}", $ids);

我强烈、强烈、强烈建议您使用关联/连接表来存储数据。但是，有时可能会遇到格式不好的数据库，因此必须执行这样的操作。如果是，你可以这样做:

select replace(trim(replace(replace(concat(',', SeCatId, ','), concat(',', 6, ','), ','), ',', ' '), ' ', ',')

思路如下:

在列表的开头和结尾加上逗号，这样所有元素都被分隔符
将逗号包围的值替换(因此"16"不受影响)
用空格替换所有逗号
使用trim()删除第一个和最后一个空格
用逗号

你可以在一次更新中做到这一点:

update table t
    set SeCatId = replace(trim(replace(replace(concat(',', SeCatId, ','), concat(',', 6, ','), ','), ',', ' '), ' ', ',')
    where concat(',', SeCatid, ',') like concat('%,', 6, ',%');