php - 如果另一列中不存在，如何更新第一个 NULL 列

Question

我想运行 mysql 查询来搜索并更新表 levels 中的第一个空列，其中 uid1、uid2 的值、uid3、uid4 列不等于 myUniqueID。

这是我的表格的简化版本，其中包含内容:

mysql> select * from levels;
+----+--------------+--------------+----------------+-----------------+-----------------------------------------+
| level_id|        uid1    |      uid2      |      uid3      |      uid4      |      status    |      level     |
+----+--------------+--------------+----------------------------------------------------------------------------+
|    1    | 576da2176b9867 | 57709be6486012 | 57709be6006055 | 57709be6486077 |     closed     |     level9     |
|    2    | 57709be6486012 | 577c132ed56645 | 57709be0000044 | 57709be6486033 |     closed     |     level5     |
|    3    | 577c132ed56645 | 577c1339f34e11 | 57709be6486002 | 57709be6486011 |     running    |     level3     |
|    4    | 577c1339f34e11 |     NULL       |      NULL      |      NULL      |     opened     |     level2     |
+----+--------------+--------------+----------------------------------------------------------------------------+

这是我的查询:

UPDATE `levels` set 
`uid1`=IF(`uid1` IS NULL, IF ('$myUniqueID' not in (`uid2`,`uid3`,`uid4`),'$myUniqueID', NULL ), `uid1`),
`uid2`=IF(`uid1` IS NULL, IF ('$myUniqueID' not in (`uid1`,`uid3`,`uid4`),'$myUniqueID', NULL ), `uid2`),
`uid3`=IF(`uid1` IS NULL, IF ('$myUniqueID' not in (`uid2`,`uid1`,`uid4`),'$myUniqueID', NULL ), `uid3`),
`uid4`=IF(`uid1` IS NULL, IF ('$myUniqueID' not in (`uid2`,`uid3`,`uid1`),'$myUniqueID', NULL ), `uid4`)
WHERE  levels.`status` ='opened'  and  levels.level = 'level2' 

假设 $myUniqueID 的值为 11111111111，此查询将更新第 4 行的 uid2 列并将其设置在 11111111111。像这样:

+----+--------------+--------------+----------------+-----------------+-----------------------------------------+
| level_id|        uid1    |      uid2      |      uid3      |      uid4      |      status    |      level     |
+----+--------------+--------------+----------------------------------------------------------------------------+
|    1    | 576da2176b9867 | 57709be6486012 | 57709be6006055 | 57709be6486077 |     closed     |     level9     |
|    2    | 57709be6486012 | 577c132ed56645 | 57709be0000044 | 57709be6486033 |     closed     |     level5     |
|    3    | 577c132ed56645 | 577c1339f34e11 | 57709be6486002 | 57709be6486011 |     running    |     level3     |
|    4    | 577c1339f34e11 |  111111111111  |      NULL      |      NULL      |     opened     |     level2     |
+----+--------------+--------------+----------------------------------------------------------------------------+

问题是内部IF总是返回FALSE，因此表不会改变。

Question_1:有人能找到解决此问题的方法吗？

问题_2:如何判断 MySQL UPDATE 何时成功以及实际更新的数据？

Answer 1

您正在每个分配中测试 uid1 IS NULL。它必须检查所有前面的列是否为 NOT NULL 及其本身 IS NULL。

UPDATE `levels` set 
    `uid1`=IF(`uid1` IS NULL, IF ('$myUniqueID' not in (IFNULL(`uid2`, ''),IFNULL(`uid3`, ''),IFNULL(`uid4`, '')),'$myUniqueID', NULL ), `uid1`),
    `uid2`=IF(`uid1` IS NOT NULL AND `uid2` IS NULL, IF ('$myUniqueID' not in (`uid1`,IFNULL(`uid3`, ''),IFNULL(`uid4`, '')),'$myUniqueID', NULL ), `uid2`),
    `uid3`=IF(`uid1` IS NOT NULL AND `uid2` IS NOT NULL AND `uid3` IS NULL, IF ('$myUniqueID' not in (`uid2`, `uid1`,IFNULL(`uid4`, '')),'$myUniqueID', NULL ), `uid3`),
    `uid4`=IF(`uid1` IS NOT NULL AND `uid2` IS NOT NULL AND `uid3` IS NOT NULL AND `uid4` IS NULL, IF ('$myUniqueID' not in (`uid2`, `uid3`, `uid1`),'$myUniqueID', NULL ), `uid4`)
WHERE  levels.`status` ='opened'  and  levels.level = 'level2' 

DEMO

问题 2:您可以在 UPDATE 查询后使用 SELECT ROW_COUNT(); 来了解更新了多少行。如果没有更新任何内容，将返回 0。