24.02.2020 09:25

Поиск недостающих идентификаторов в MySQL

Поиск недостающих идентификаторов в MySQL

В этой статье я объясню, как найти недостающие идентификаторы в MySQL. Недавно я столкнулся с задачей определения пропущенных идентификаторов счет-фактур (инвойсов) в MySQL. Я пытался найти готовое решение, но подходящего решения не было найдено. Во многих случаях вообще не учитывалось, что может быть несколько пропущенных идентификаторов один за другим. Используйте мое решение, чтобы найти недостающие идентификаторы в MySQL:

SET @minId = 1000;
SELECT @maxId := max(`id`) FROM `invoices`;

CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` (
	`id` INT UNSIGNED NOT NULL
);

DROP PROCEDURE IF EXISTS insertIds;

DELIMITER $$
CREATE PROCEDURE insertIds(startId INT, endId INT)
BEGIN
	SET @i = IFNULL(startId, 1);

	WHILE @i <= endId DO
		INSERT INTO `tmp` VALUES (@i);
		SET @i = @i + 1;
	END WHILE;
END $$
DELIMITER ;

CALL insertIds(@minId, @maxId);

SELECT `tmp`.`id` FROM `tmp`
LEFT JOIN `invoices` AS `t` on `t`.`id` = `tmp`.`id`
WHERE `t`.`id` IS NULL;

Я постараюсь объяснить вам мое решение. Давайте сначала создадим простую таблицу `invoices` с пропущенными идентификаторами:

--
-- Table structure for table `invoices`
--
CREATE TABLE `invoices` (
	`id` INT UNSIGNED NOT NULL
);

--
-- Dumping data for table `invoices`
--
INSERT INTO `invoices` (`id`) VALUES (1000), (1001), (1002), (1005), (1007), (1010);

Мы устанавливаем переменную @minId, с которой начинаем поиск. Затем мы определяем максимальное значение идентификатора и устанавливаем переменную @maxId:

SET @minId = 1000;
SELECT @maxId := max(`id`) FROM `invoices`;

Нам нужно создать временную таблицу `tmp`, в которую мы будем вставлять все последовательные идентификаторы в диапазоне от @minId до @maxId:

CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` (
	`id` INT UNSIGNED NOT NULL
);

Теперь давайте создадим хранимую процедуру insertIds, которая будет вставлять последовательные идентификаторы в таблицу `tmp` в диапазоне от @minId до @maxId:

DROP PROCEDURE IF EXISTS insertIds;

DELIMITER $$
CREATE PROCEDURE insertIds(startId INT, endId INT)
BEGIN
	SET @i = IFNULL(startId, 1);

	WHILE @i <= endId DO
		INSERT INTO `tmp` VALUES (@i);
		SET @i = @i + 1;
	END WHILE;
END $$
DELIMITER ;

К сожалению, вы не можете создать хранимую процедуру с проверкой на существование:

CREATE PROCEDURE IF NOT EXISTS generateCodes(startId INT, endId INT, codePrefix VARCHAR(64))

Поэтому вначале создания хранимой процедуры мы удаляем ее, чтобы не возникла ошибка "PROCEDURE generateCodes already exists".

Вы также можете прочитать мою статью о том, как использовать цикл WHILE в MySQL.

Теперь мы можем вызвать хранимую процедуру insertIds для генерации последовательных идентификаторов:

CALL insertIds(@minId, @maxId);

Остается последний шаг, в котором мы получаем недостающие идентификаторы, используя LEFT JOIN:

SELECT `tmp`.`id` FROM `tmp`
LEFT JOIN `invoices` AS `t` on `t`.`id` = `tmp`.`id`
WHERE `t`.`id` IS NULL;

--
--	[output]
--
--	id
--	1003
--	1004
--	1006
--	1008
--	1009

Другие статьи