SQL: выберите Ключи, которые не существуют в одной таблице

У меня есть таблица с нормальной настройкой auto inc. Идентификаторы. Некоторые из строк были удалены, поэтому список идентификаторов может выглядеть примерно так:

(1, 2, 3, 5, 8,...)

Затем из другого источника (Изменить: Другой источник = НЕ в базе данных) У меня есть этот массив:

(1, 3, 4, 5, 7, 8)

Я ищу запрос, который я могу использовать в базе данных, чтобы получить список ID: s NOT в таблице из массива, который у меня есть. Что будет:

(4, 7)

Есть ли такие? Мое решение прямо сейчас либо создает временную таблицу, поэтому команда "WHERE table.id IS NULL" работает или, вероятно, хуже, используя функцию PHP array_diff, чтобы увидеть, что отсутствует после того, как вы получили все идентификаторы из таблицы.

Поскольку список идентификаторов закрывается на миллионы или строки, я очень хочу найти лучшее решение.

Спасибо! /Томас

Изменить 2:

Мое основное приложение - довольно простая таблица, заполненная множеством строк. Это приложение администрируется с помощью браузера, и я использую PHP в качестве интерпретатора для кода.

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

Проблема заключается в том, что моя система не может быть на 100% уверен, что пользователь сделал все правильно, когда нажал клавишу "экспорт". Или, что никакие строки никогда не были созданы в другой системе.

Из другой системы я могу получить CSV файл, где все строки, которые есть в системе. Таким образом, сравнивая файл CSV и мою таблицу, я вижу, что: * В другой системе отсутствуют какие-либо строки, которые должны были быть импортированы * Если кто-то создал строки в другой системе

Проблема не в том, чтобы "решить это". Это лучшее решение, потому что в строках так много данных.

Еще раз спасибо!

/Томас

Ответ 1

Мы можем использовать MYSQL не в опции.

SELECT id
FROM table_one
WHERE id NOT IN ( SELECT id FROM table_two )

Edited

Если вы получаете исходный код из файла csv, вам просто нужно просто поставить эти значения так:

Я предполагаю, что CSV равны 1,2,3,..., n

SELECT id
FROM table_one
WHERE id NOT IN ( 1,2,3,...,n );

РЕДАКТИРОВАТЬ 2

Или Если вы хотите выбрать другой способ, вы можете использовать mysqlimport для импорта данных во временную таблицу в базе данных MySQL и получения результата и удаления таблицы.

Вроде:

Создать таблицу

CREATE TABLE my_temp_table(
   ids INT,
);

загрузить .csv файл

LOAD DATA LOCAL INFILE 'yourIDs.csv' INTO TABLE my_temp_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ids);

Выбор записей

SELECT ids FROM my_temp_table
WHERE ids NOT IN ( SELECT id FROM table_one )

dropping table

DROP TABLE IF EXISTS my_temp_table

Ответ 2

Как использовать left join; что-то вроде этого:

select second_table.id
from second_table
    left join first_table on first_table.id = second_table.id
where first_table.is is null

Вы также можете пойти с подзапросом; в зависимости от ситуации он может или не может быть быстрее:

select second_table.id
from second_table
where second_table.id not in (
    select first_table.id
    from first_table
)

Или с помощью not exists:

select second_table.id
from second_table
where not exists (
    select 1
    from first_table
    where first_table.id = second_table.id
)

Ответ 3

Функция, которую вы ищете, НЕ IN (псевдоним для < > ALL)

Документация MYSQL:

http://dev.mysql.com/doc/refman/5.0/en/all-subqueries.html

Пример его использования:

http://www.roseindia.net/sql/mysql-example/not-in.shtml

Наслаждайтесь!

Ответ 4

Проблема заключается в том, что T1 может иметь миллион строк или десять миллионов строк, и это число может измениться, поэтому вы не знаете, сколько строк ваша таблица сравнения, T2, та, которая не имеет пробелов, должна иметь для выполняя WHERE NOT EXISTS или LEFT JOIN для тестирования NULL.

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

Учтите, чтобы уточнить требования к вашей заявке?

Ответ 5

ОК, я прочитал ваши изменения/разработки. Синхронизация двух баз данных, где вторая не должна вставлять новые строки, но может сделать это, звучит как проблема, ожидающая своего появления.

Ни один из предложенных выше способов (ГДЕ НЕ СУЩЕСТВУЕТ ИЛИ ЛЕВЫЙ ПРИСОЕДИНЕННЫЙ) не является воздушным, и ни один из способов гарантировать логическую целостность между этими двумя системами. Они не сообщают вам, какая система создала строку в ситуациях, когда обе таблицы содержат строку с одним и тем же идентификатором. Теперь вы сосредотачиваетесь на пробелах, но другая проблема заключается в дублировании идентификаторов.

Например, если обе таблицы имеют строку с идентификатором 13887, вы не можете предположить, что database1 создал строку. Он мог быть вставлен в базу данных2, а затем база данных1 могла вставить новую строку, используя тот же самый идентификатор. Вам нужно будет сравнить все значения столбцов, чтобы убедиться, что строки одинаковые или нет.

Поэтому я бы предложил, чтобы вы также изучали GUID в качестве замены для автоинкрементных целых чисел. Вы не можете запретить вставлять строки базы данных2, но, по крайней мере, с помощью GUID вы не столкнетесь с проблемой, когда вторая база данных вставила строку и назначила ей значение первичного ключа, которое может использовать ваша первая база данных, в результате чего две разные строки с тот же идентификатор. Также полезно использовать столбцы CreationDateTime и LastUpdateDateTime.

Однако правильное решение, если оно доступно вам, состоит в том, чтобы поддерживать только одну базу данных и предоставлять пользователям удаленный доступ к ней, например, через веб-интерфейс. Это устранит беспорядок и осложнение проблем с репликацией/синхронизацией.

Если веб-интерфейс удаленного доступа невозможен, возможно, вы можете сделать одну из баз данных только для чтения? Или база данных2 должна обновлять строки? Возможно, вы можете отказаться от привилегии вставки? Какой механизм базы данных вы используете?

Ответ 6

У меня та же проблема: у меня есть список значений от пользователя, и я хочу найти подмножество, которое не существует в другой таблице. Я сделал это в оракуле, построив псевдо-таблицу в предложении select. Вот как это сделать в Oracle. Попробуйте в MySQL без "от двойного":

-- find ids from user (1,2,3) that *don't* exist in my person table
-- build a pseudo table and join it with my person table
select pseudo.id from (
  select '1' as id from dual
  union select '2' as id from dual
  union select '3' as id from dual
) pseudo
left join person
  on person.person_id = pseudo.id
where person.person_id is null