Блог

Главная - Вопросы по программированию - MySQL получает результаты элементов, не найденных в предложении where

MySQL получает результаты элементов, не найденных в предложении where

#mysql

#mysql

Вопрос:

У меня есть такой запрос:

 select id from users where id in (1,2,3,4,5)

У меня есть пользователь с идентификаторами 1,3 и 4. То, что я пытаюсь сделать, это получить результаты не найденных идентификаторов. Пример, если у меня есть идентификаторы 1,3 и 4 в таблице, я ожидаю, что будут возвращены 2 и 5, поскольку эти идентификаторы не существуют. Я надеюсь, что это имеет смысл и возможно.

Комментарии:

1. Итак, вы хотите вернуть что-то, чего не существует? Вы можете добиться этого с помощью таблицы чисел.

Ответ №1:

Да, вы можете это сделать. Создайте таблицу и вставьте в нее идентификаторы, которые вы хотите перечислить, следующим образом:

 create table enumerate_ids (eid int not null primary key);
insert into enumerate_ids values (1),(2),(3),(4),(5);

Затем выполните объединение с вашей таблицей users, чтобы найти, какой из них не существует.

 select eid from enumerate_ids e
where not exists (
  select 1 from users where id = e.eid
)

Или вы можете сделать это:

 select eid
from enumerate_ids e
left join users u on u.id = e.eid
where u.id is null

Пример: https://dbfiddle.uk/?rdbms=mysql_5.7amp;fiddle=5e1e138aacf5f41a3d5d28e999993ccc

Если вы делаете все это динамически, вы используете код своего приложения для создания временной таблицы, вводите идентификаторы, которые хотите перечислить, или сверяете с таблицей users, выполняете запрос, подобный приведенному выше, получаете результаты, а затем удаляете временную таблицу.

Я думаю, вы могли бы сделать это и с помощью хранимой процедуры, чтобы вы могли передавать ей свои значения, и она выдает идентификаторы, которые не существуют.

Ответ №2:

Мы можем использовать WITH recursive для генерации набора увеличивающихся идентификаторов, а затем выполнить not in operation для таблицы пользователя.

 WITH recursive numbers AS (
    select 1 as id
   union all
   select id   1
   from numbers
   where id < (select max(id) from user))
select id from numbers where id not in (select distinct(id) from user);

Комментарии:

1. Если пользовательская таблица содержит более 1000 строк, вы можете установить @@cte_max_recursion_depth более высокое соответствующее число.

2. Это хороший пример поиска недостающих данных в последовательном наборе идентификаторов. Очень аккуратно.

Ответ №3:

Сначала создайте числовые представления (или выберите нужный вам размер):

 CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

И присоединяет его к таблице users:

 SELECT n AS unused_id 
FROM generator_16 number 
    LEFT JOIN users ON users.id=number.n 
WHERE users.id IS NULL AND number.n < (SELECT MAX(users.id) FROM test)

Результатом являются строки с неиспользуемыми идентификаторами.

Одно примечание: Вы хотели вернуть несуществующий номер, превышающий максимальный существующий идентификатор, в приведенном выше запросе я увеличил до максимального существующего номера. Вы можете изменить это на const, если хотите.

Метки: ,