Mysql 检查整数可用性

Question

我想知道是否有可能创建一个返回最小可用整数的查询。解释起来有点复杂，但是通过一个例子它会很方便:

代码:1 2 4 5 6

在这种情况下，我希望查询返回数字 3，因为它是仍然可用的最小整数。

提前致谢。

更新

我有以下表格:

Itens
+----+-----------+------+
| id | name      | code |
+----+-----------+------+
| 1  | produto 1 | 1    |
+----+-----------+------+
| 2  | produto 2 | 2    |
+----+-----------+------+
| 3  | produto 3 | 4    |
+----+-----------+------+
| 4  | produto 4 | 6    |
+----+-----------+------+

Categories
+----+------------+------------+
| id | name       | company_id |
+----+------------+------------+
| 1  | Category 1 |     1      |
+----+------------+------------+
| 2  | Category 2 |     2      |
+----+------------+------------+

Companies
+----+-----------+
| id | name      |
+----+-----------+
| 1  | Company 1 |
+----+-----------+
| 2  | Company 2 |
+----+-----------+

我需要在触发器中为每个公司执行此操作。我尝试了下面的代码，但不起作用。

CREATE TRIGGER `updateCodigoNull`
BEFORE INSERT ON `itens`
FOR EACH ROW
  BEGIN
    IF (NEW.codigo IS NULL)
    THEN
      SET NEW.codigo = (SELECT b.codigo - 1
                        FROM (
                               (SELECT
                                  2 AS 'codigo',
                                  1 AS 'missing',
                                  1 AS 'previous'
                                FROM itens
                                  INNER JOIN item_categories ON itens.category_id = item_categories.id
                                WHERE NOT EXISTS(SELECT 1
                                                 FROM itens
                                                 WHERE codigo = 1) AND item_categories.company_id = 2
                                LIMIT 1)
                               UNION
                               (SELECT
                                  codigo,
                                  IF(@previous != 0 AND @previous < codigo - 1, 1, 0) AS missing,
                                  @previous := codigo
                                FROM itens
                                  INNER JOIN item_categories
                                    ON itens.category_id = item_categories.id
                                  , (SELECT @previous := 0) a
                                WHERE item_categories.company_id = 2
                               )) b
                        WHERE b.missing = 1
                        ORDER BY b.codigo
                        LIMIT 1);
    END IF;
  END

Answer 1

如果这些数字是行 ID，那么您可以使用查询变量来跟踪先前的 ID，并在差异大于 1 时返回第一个 ID，例如:

创建和插入脚本:

create table test(id int);
insert into test values(1);
insert into test values(2);
insert into test values(4);
insert into test values(5);
insert into test values(7);

查询:

SELECT b.id - 1
FROM (
    SELECT id, IF(@previous != 0 AND @previous < id - 1, 1, 0) as missing, @previous:= id 
    FROM test , (SELECT @previous := 0) a
    ORDER BY id
    ) b
WHERE b.missing = 1
LIMIT 1;

这将返回 3。如果你想要前 2 个丢失的 id，那么你可以将 LIMIT 更改为 2 它将同时返回 3 和 6.

更新

如果您希望查询返回类似 1 的值(如果它们不存在)，那么您可以添加另一个查询并UNION 结果，例如:

SELECT b.id - 1
FROM (
  SELECT 2 AS 'id', 1 AS 'missing', 1 AS 'previous' FROM test WHERE NOT EXISTS (SELECT 1 FROM test WHERE id = 1) LIMIT 1
  UNION 
  SELECT id, IF(@previous != 0 AND @previous < id - 1, 1, 0) as missing, @previous:= id 
  FROM test , (SELECT @previous := 0) a
  ) b
WHERE b.missing = 1
ORDER BY b.id
LIMIT 1;

上面会返回 1，这里是 SQL Fiddle .