Как найти пробелы в последовательной нумерации в MySQL?
У нас есть база данных с таблицей, значения которой были импортированы из другой системы. Существует столбец с автоинкрементом, и нет повторяющихся значений, но отсутствуют значения. Например, запустив этот запрос:
select count(id) from arrc_vouchers where id between 1 and 100
должен вернуть 100, но вместо этого он возвращает 87. Можно ли выполнить какой-либо запрос, который вернет значения пропущенных чисел? Например, записи могут существовать для идентификаторов 1-70 и 83-100, но нет записей с идентификаторами 71-82. Я хочу вернуть 71, 72, 73 и т. Д.
Это возможно?
18 ответов
Обновить
ConfexianMJS дал гораздо лучший ответ с точки зрения производительности.
Ответ (не так быстро, как это возможно)
Вот версия, которая работает с таблицей любого размера (не только на 100 строк):
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
gap_starts_at- первый идентификатор в текущем разрывеgap_ends_at- последний идентификатор в текущем разрыве
Это просто помогло мне найти пробелы в таблице с более чем 80 тыс. Строк:
SELECT
CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
SELECT
@rownum:=@rownum+1 AS expected,
IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got
FROM
(SELECT @rownum:=0) AS a
JOIN YourTable
ORDER BY YourCol
) AS z
WHERE z.got!=0;
Результат:
+------------------+
| missing |
+------------------+
| 1 thru 99 |
| 666 thru 667 |
| 50000 |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)
Обратите внимание, что порядок столбцов expected а также got является критическим.
Если вы знаете, что YourCol не начинается с 1, и это не имеет значения, вы можете заменить
(SELECT @rownum:=0) AS a
с
(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a
Новый результат:
+------------------+
| missing |
+------------------+
| 666 thru 667 |
| 50000 |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)
Если вам нужно выполнить какую-то задачу сценария оболочки для отсутствующих идентификаторов, вы также можете использовать этот вариант для непосредственного создания выражения, которое вы можете перебирать в bash.
SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM (SELECT @rownum: = @ rownum + 1, как и ожидалось, ЕСЛИ (@rownum= высота, 0, @rownum:= высота) AS получил FROM (SELECT @rownum:=0) как блок JOIN ORDER BY по высоте) AS z ГДЕ z.got!=0;
Это производит вывод как
$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)
Затем вы можете скопировать и вставить его в цикл for в терминале bash, чтобы выполнить команду для каждого идентификатора.
for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
echo $ID
# fill the gaps
done
Это то же самое, что и выше, только то, что оно читаемо и исполняемо. Изменяя команду "CONCAT" выше, можно генерировать синтаксис для других языков программирования. Или, может быть, даже SQL.
Быстрый и грязный запрос, который должен сделать свое дело:
SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
FROM
(
SELECT a1.id AS a , MIN(a2.id) AS b
FROM arrc_vouchers AS a1
LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id
WHERE a1.id <= 100
GROUP BY a1.id
) AS tab
WHERE
b > a + 1
Это даст вам таблицу с идентификатором, у которого над ним пропущены идентификаторы, и с именем next_id, а также с количеством пропущенных между... например
id next_id missing_inbetween 1 4 2 68 70 1 75 87 11
Если вы используете MariaDB у вас есть более быстрый (800%) вариант
SELECT * FROM seq_1_to_50000 where seq not in (select col from table);
Создайте временную таблицу со 100 строками и одним столбцом, содержащим значения 1-100.
Outer Присоедините эту таблицу к своей таблице arrc_vouchers и выберите значения в одном столбце, где идентификатор arrc_vouchers равен нулю.
Кодирование это слепое, но должно работать.
select tempid from temptable
left join arrc_vouchers on temptable.tempid = arrc_vouchers.id
where arrc_vouchers.id is null
Если есть последовательность, имеющая промежуток не более одного между двумя числами (например, 1,3,5,6), то запрос, который можно использовать:
select s.id+1 from source1 s where s.id+1 not in(select id from source1) and s.id+1<(select max(id) from source1);
- table_name -
source1 - имя_столбца -
id
Альтернативное решение, которое требует запроса + некоторый код, выполняющий некоторую обработку:
select l.id lValue, c.id cValue, r.id rValue
from
arrc_vouchers l
right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null)
left join arrc_vouchers r on r.id=c.id+1
where 1=1
and c.id > 0
and (l.id is null or r.id is null)
order by c.id asc;
Обратите внимание, что запрос не содержит какой-либо подвыборки, которая, как мы знаем, не выполняется должным образом планировщиком MySQL.
Это будет возвращать одну запись на centralValue (cValue), которая не имеет меньшее значение (lValue) или большее значение (rValue), т.е.
lValue |cValue|rValue
-------+------+-------
{null} | 2 | 3
8 | 9 | {null}
{null} | 22 | 23
23 | 24 | {null}
{null} | 29 | {null}
{null} | 33 | {null}
Не вдаваясь в подробности (мы увидим их в следующих параграфах), этот вывод означает, что:
- Нет значений между 0 и 2
- Нет значений между 9 и 22
- Нет значений между 24 и 29
- Нет значений между 29 и 33
- Нет значений между 33 и МАКС. ЗНАЧЕНИЕ
Таким образом, основная идея состоит в том, чтобы сделать правые и левые соединения с одной и той же таблицей, видя, есть ли у нас значения смежности на значение (то есть: если центральное значение равно "3", то мы проверяем 3-1=2 слева и 3+1 в справа), и когда ROW имеет значение NULL в RIGHT или LEFT, тогда мы знаем, что смежного значения нет.
Полный необработанный вывод моей таблицы:
select * from arrc_vouchers order by id asc;
0
2
3
4
5
6
7
8
9
22
23
24
29
33
Некоторые заметки:
- Инструкция SQL IF в условии соединения необходима, если вы определили поле id как UNSIGNED, поэтому оно не позволит вам уменьшить его до нуля. Это не является строго необходимым, если вы сохраняете значение c.value > 0, как указано в следующей заметке, но я включаю его просто как документ.
- Я фильтрую нулевое центральное значение, поскольку нас не интересует какое-либо предыдущее значение, и мы можем извлечь значение из следующей строки.
Я попробовал это по-разному, и лучшая производительность, которую я нашел, - это простой запрос:
select a.id+1 gapIni
,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
from arrc_vouchers a
left join arrc_vouchers b on b.id=a.id+1
where b.id is null
order by 1
;
... одно соединение слева, чтобы проверить, существует ли следующий идентификатор, только если следующий, если не найден, то подзапрос находит следующий существующий идентификатор, чтобы найти конец пропуска. Я сделал это, потому что запрос с равным (=) лучше, чем оператор больше (>).
Используя http://sqlfiddle.com/, он не показывает столь отличную производительность запросов других, но в реальной базе данных этот запрос выше в 3 раза быстрее, чем другие.
Схема:
CREATE TABLE arrc_vouchers (id int primary key)
;
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
;
Следуйте ниже всем запросам, которые я сделал для сравнения производительности:
select a.id+1 gapIni
,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
from arrc_vouchers a
left join arrc_vouchers b on b.id=a.id+1
where b.id is null
order by 1
;
select *, (gapEnd-gapIni) qt
from (
select id+1 gapIni
,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
from arrc_vouchers a
order by id
) a where gapEnd <> gapIni
;
select id+1 gapIni
,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
#,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
from arrc_vouchers a
where id+1 <> (select x.id from arrc_vouchers x where x.id>a.id limit 1)
order by id
;
select id+1 gapIni
,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
from arrc_vouchers a
order by id
;
select id+1 gapIni
,coalesce((select id from arrc_vouchers x where x.id=a.id+1),concat('*** GAT *** ',(select x.id from arrc_vouchers x where x.id>a.id limit 1))) gapEnd
from arrc_vouchers a
order by id
;
Может быть, это кому-то поможет и пригодится.
Вы можете увидеть и проверить мой запрос, используя этот http://sqlfiddle.com/:
Простое, но эффективное решение для поиска недостающих значений автоинкремента
ВЫБРАТЬ +1 ИЗ
table_name ГДЕ
id+1 НЕ В (ВЫБРАТЬ идентификатор ИЗ имя_таблицы)
Возможно, это не относится к делу, но я искал что-то подобное, чтобы перечислить пробелы в последовательности чисел, и нашел этот пост, в котором есть несколько разных решений, в зависимости от того, что именно вы ищете. Я искал первый доступный пробел в последовательности (то есть следующий доступный номер), и это, кажется, работает нормально.
ВЫБЕРИТЕ МИНУТУ (l.number_sequence + 1) как nextavabile от пациентов, поскольку l ВНЕШНЕЕ ОБЪЕДИНЕНИЕ пациентов, как r на l.number_sequence + 1 = r.number_sequence ГДЕ r.number_sequence равен NULL. Несколько других сценариев и решений, обсуждаемых там, с 2005 года!
Хотя кажется, что все это работает, результирующий набор возвращается в течение очень длительного времени, когда имеется 50000 записей.
Я использовал это, и он находит разрыв или следующий доступный (последний использовался + 1) с гораздо более быстрым возвращением из запроса.
SELECT a.id as beforegap, a.id+1 as avail
FROM table_name a
where (select b.id from table_name b where b.id=a.id+1) is null
limit 1;
Основываясь на ответе, данном выше Lucek, эта хранимая процедура позволяет вам указать имена таблиц и столбцов, которые вы хотите проверить, чтобы найти несмежные записи - таким образом, отвечая на исходный вопрос, а также демонстрируя, как можно использовать @var для представления таблиц &/ или столбцы в хранимой процедуре.
create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64))
language sql
not deterministic
contains sql
sql security definer
comment ''
begin
declare strsql varchar(1000);
declare tbl varchar(64);
declare col varchar(64);
set @tbl=cast(param_tbl as char character set utf8);
set @col=cast(param_col as char character set utf8);
set @strsql=concat("select
( t1.",@col," + 1 ) as starts_at,
( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at
from ",@tbl," t1
where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 )
having ends_at is not null");
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
end
Вы можете использовать генерировать серии для генерации чисел от 1 до самого высокого идентификатора вашей таблицы. Затем выполните запрос с идентификатором, не входящим в эту серию.
CREATE TABLE arrc_vouchers (id int primary key);
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16);
WITH RECURSIVE odd_num_cte (id) AS
(
SELECT (select min(id) from arrc_vouchers)
union all
SELECT id+1 from odd_num_cte where id <(SELECT max(id) from arrc_vouchers)
)
SELECT cte.id
from arrc_vouchers ar right outer join odd_num_cte cte on ar.id=cte.id
where ar.id is null;
Начиная с комментария пользователя 933161,
select l.id + 1 as start from sequence as l inner join sequence as r on l.id + 1 = r.id where r.id is null;
лучше тем, что он не будет давать ложное срабатывание для конца списка записей. (Я не уверен, почему так много используют левые внешние соединения.) Кроме того,
insert into sequence (id) values (#);
где # — начальное значение пробела, которое заполнит это начальное значение. (Если есть поля, которые не могут быть нулевыми, вам придется добавить поля с фиктивными значениями.) Вы можете чередовать запрос начальных значений и заполнение каждого начального значения, пока запрос начальных значений не вернет пустой набор.
Конечно, этот подход будет полезен только в том случае, если вы работаете с достаточно небольшим набором данных, для которого разумно выполнять повторение вручную. Я недостаточно разбираюсь в таких вещах, как phpMyAdmin, чтобы придумать способы его автоматизации для больших наборов с большими и большими пробелами.
Еще один простой ответ, определяющий пробелы. Мы выполняем запрос, выбирая только нечетные числа, и присоединяем его к запросу со всеми четными числами. Пока вы не пропустите id 1; это должно дать вам исчерпывающий список того, где начинаются пробелы.
Вам все равно придется взглянуть на это место в базе данных, чтобы выяснить, сколько чисел есть пробел. Я обнаружил, что этот способ проще, чем предложенное решение, и его гораздо проще адаптировать к уникальным ситуациям.
SELECT *
FROM (SELECT * FROM MyTABLE WHERE MYFIELD % 2 > 0) AS A
RIGHT JOIN FROM (SELECT * FROM MyTABLE WHERE MYFIELD % 2 = 0) AS B
ON A.MYFIELD=(B.MYFIELD+1)
WHERE a.id IS NULL;
Это работает для меня:
SELECT distinct(l.membership_no + 1) as nextavabile
from Tablename as l
LEFT OUTER JOIN Tablename as r on l.membership_no + 1 = r.membership_no
WHERE r.membership_no is NULL and l.membership_no is not null order by nextavabile asc;
Это может не работать в MySQL, но на работе (Oracle) нам нужно что-то подобное.
Мы написали Stored Proc, который принял число в качестве значения Max. Затем Stored Proc создал временную таблицу с одним столбцом. Таблица содержала все числа от 1 до макс. Затем он сделал соединение NOT IN между временной таблицей и нашей таблицей интересов.
Если вы вызвали его с Max = Select max(id) из arrc_vouchers, он вернул бы все пропущенные значения.