php - Mysql:按顺序将值插入到间隙中

Question

所以我的问题是这样的:检查 1 - 14 中缺少哪些行，然后将我的新值插入到打开的行中的最佳方法是什么。

我知道没有必要填补数据库中的空白，但我正在学习 php 类(class)，我需要实现以下目标:

我有一个表，主键设置为id，但不是auto_increment。因此，如果 14 个值之一被删除，则需要将新值放置在间隙中。

我读了一点，发现了一些关于SO的问题，但他们都只是想检查是否存在差距，我需要以某种方式告诉它允许更新值的内容和位置。

我有这个

SELECT MIN(t1.position_id)+1 AS unused
    FROM playerposition AS t1
    WHERE NOT EXISTS (SELECT * FROM playerposition AS t2 WHERE t2.position_id = t1.position_id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM playerposition WHERE position_id = 1)
) AS subquery

但这只返回第一行，我不太理解这个查询。任何帮助、建议或指导将不胜感激。

更新

我对查询进行了更改，现在得到了空值。我的新查询如下所示

SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
FROM
  (
    SELECT a1.position_id AS a, MIN(a2.position_id) AS b
    FROM playerposition  AS a1
      LEFT JOIN playerposition AS a2 ON a2.position_id > a1.position_id
    WHERE a1.position_id <= 14
    GROUP BY a1.position_id
  ) AS tab

WHERE
  b > a + 1

不过，我仍然需要 INSERT 部分的帮助

Answer 1

下面的代码帮助我实现了我想要的结果。我确信可以进行改进以使代码变得更好

if (mysqli_num_rows($resultCheckPos) == 14) {
        echo "You can only have 14 positions in total";
    } else {
        $testQ = "SELECT min(unused) AS unused
FROM (
    SELECT MIN(t1.position_id)+1 AS unused
    FROM playerposition AS t1
    WHERE NOT EXISTS (SELECT * FROM playerposition AS t2 WHERE t2.position_id = t1.position_id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM playerposition WHERE position_id = 1)
) AS subquery";

        $resultTest = $conn->query($testQ);
        $rowTest = mysqli_fetch_assoc($resultTest);
        $row = $rowTest['unused'];

        $insertTest = "INSERT INTO playerposition (position_id, position_descr) VALUES ('$row','$posDesc')";
        $resultInsertTest = $conn->query($insertTest);
        header("Refresh:0");
    }