php - mySQL 数据库更新时的动态计算

Question

我有一个项目表，每个项目都通过一个名为 category 的行中的整数单独与一个类别相关联。我还有每个项目的显示顺序，与其相关类别相关。这保存在名为 pictureorder 的行中。直截了当？

当用户删除包含与之关联的项目/图片的类别时，我的问题就出现了。我告诉 mySQL 搜索与该类别关联的任何项目，并将它们与由 0 表示的“默认”类别相关联。

// IF THERE'S ANY PICTURES THAT WERE IN THAT CATEGORY,
// MOVE THEM INTO THE DEFAULT CATEGORY.
$uncategorise = "UPDATE pictures 
    SET category = '0'
    WHERE category = '$categoryID'
    AND username = '$userID'";
$queryUncat = mysql_query($uncategorise) or die(mysql_error());

这行得通。但是，项目的图片顺序被破坏，因为从已删除类别转移的项目可能会在默认类别中创建重复的图片顺序。我知道如何以多次调用数据库的方式解决这个问题。这感觉太贵了。我的问题是我是否可以使用一些我不知道的技术来更新一个递增的 pictureorder 整数到数据库，因为它被调用而不将该行作为键？类似的东西:

$uncategorise = "UPDATE pictures 
    SET category = '0',
        pictureorder = 'TotalofDefaultCategory++'
    WHERE category = '$categoryID'
    AND username = '$useID'";
$queryUncat = mysql_query($uncategorise) or die(mysql_error());

提前致谢!

更新1

根据下面 Kyle 的建议，我正在尝试以下操作但没有成功:

UPDATE pictures 
    SET category = '0',
        pictureorder = (SELECT COUNT(category) + 1 WHERE category='0' AND username='$useID')
    WHERE category = '$categoryID'
    AND username = '$useID';

如果我省略 pictureorder = (SELECT COUNT(category) + 1 WHERE category='0' AND username='$useID')，只要类别匹配 $categoryID 和 $useID。但是，如果我包括附加行，则没有任何变化。我没有收到任何错误，但图片顺序没有改变。类别也没有。语法是否正确？

此外，如果我将 pictureorder = (SELECT... 替换为 pictureorder = 2 它会按预期工作。所以除了语法不正确(我希望它不会像我担心的那样以这种方式包含 COUNT 好得令人难以置信)。

Answer 1

您可以使用服务器端变量:

第一个查询:

select @cnt := 1;

第二个查询:

update pictures SET pictureorder := @cnt, @cnt := @cnt + 1
WHERE ...
order by pictureorder, ...

您需要一个适当的 order by 子句来确保具有重复 pictureorder 值的记录以正确的顺序放置 - 移动的记录是在已经存在的记录之前还是之后类别？