MySQL / 10. Импорт и экспорт данных

Проверка корректности при помощи справочной таблицы

Задача
Вам нужно убедиться в том, что вводимые значения имеются в справочной таблице.

Решение
Выполните запросы, чтобы проверить, содержатся ли значения в таблице. Конкретный способ выполнения зависит от количества вводимых значений и размера таблицы.

Обсуждение
Для того чтобы проверить соответствие вводимых значений содержимому справочной таблицы, можно использовать приемы, подобные представленным в рецепте 10.27 для тестирования столбцов ENUM и SET. Но если количество членов в столбцах ENUM и SET ограничено максимальным значением в 65 536 и 64 соответственно, справочная таблица может включать практически неограниченное количество записей. Вероятно, вы не захотите считывать их все в память.

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

Создание отдельных запросов
Для одноразовых операций можно тестировать значение, проверяя, входит ли оно в справочную таблицу. Следующий запрос возвращает значение «истина» (не ноль), если значение найдено, и «ложь» в противном случае:$valid = $dbh->selectrow_array ("SELECT COUNT(*) FROM $tbl_name WHERE val = ?",undef, $val);

Такая проверка подходит, например, для тестирования значения, переданного через веб-форму, но для больших объемов данных она не эффективна. Результаты уже выполненных проверок не запоминаются, так что придется выполнять запросы для каждого отдельного вводимого значения.

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

Сначала выполним запрос, извлекающий все значения справочной таблицы и сформируем из них хеш:

my %members; # хеш для справочных значений
my $sth = $dbh->prepare ("SELECT val FROM $tbl_name");
$sth->execute ();
while (my ($val) = $sth->fetchrow_array ())
{
$members{$val} = 1;
}

Затем проверим каждое значение, выполняя тест на существование ключа хеша:

$valid = exists ($members{$val});

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

Хеш как кэш для уже просмотренных справочных значений
Можно сочетать два описанных приема: индивидуальные запросы и использование хеша для хранения информации о существовании значения. Такой подход особенно удобен для очень больших справочных таблиц. Начнем с пустого хеша:

my %members; # хеш для справочных значений

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

Допустимость значения ввода определяется значением, сопоставленным ключу, а не существованием ключа:

if (!exists ($members{$val})) # такое значение еще не встречалось
{
my $count = $dbh->selectrow_array (
"SELECT COUNT(*) FROM $tbl_name WHERE val = ?",
undef, $val);
# хранить истину/ложь как признак найденного значения
$members{$val} = ($count > 0);
}
$valid = $members{$val};

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

Обратите внимание на то, что хеш используется не совсем так, как в предыдущем случае. Раньше корректность значения определялась существованием вводимого значения как ключа хеша, а сопоставленное ключу значение было несущественным. А для случая «хеш как кэш» наличие ключа хеша означает не «значение разрешено», а «значение уже проверено». Для каждого ключа сопоставленное ему значение показывает, присутствует ли вводимое значение в справочной таблице. (Если хранить как ключи только значения, найденные в справочной таблице, то придется выполнять запрос длякаждого экземпляра недопустимого значения входных данных, что неэффективно.)

Статьи по MySQL на эту тему:

Диагностическая утилита для LOAD DATA
Импорт XML в MySQL
Использование временных таблиц для преобразования дат
Использование дат с недостающими частями
Обмен данными между MySQL и FileMaker Pro

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных