Использование временных таблиц для преобразования дат

Задача
Вы хотите выполнить предварительную обработку входных данных для MySQL, но у вас нет доступа к соответствующим внешним утилитам.

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

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

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


Предположим, что у вас есть таблица main, содержащая три столбца name, date и value, где date – столбец типа DATE со значениями в формате ISO (CCYY-MM-DD). Предположим также, что у вас есть файл данных newdata.txt, который нужно импортировать в таблицу, а его содержимое таково:

name1 01/01/99 38
name2 12/31/00 40
name3 02/28/01 42
name4 01/02/03 44

Даты имеют формат MM/DD/YY и должны быть преобразованы к формату ISO для хранения в виде значений DATE в MySQL. Можно применить к файлу рассмотренный ранее в главе сценарий cvt_date.pl: % cvt_date.pl --iformat=us --add-century newdata.txt >tmp

Затем можно загружать файл tmp в таблицу main. Но можно выполнить задачу целиком в MySQL, не обращаясь к внешним утилитам, а импортируя данные во временную таблицу и используя предложения SQL для выполнения следующих операций:

1. Создать пустую таблицу, в которую будут загружаться тестовые данные. Следующие предложения создают таблицу tmp как пустую копию таблицы main, к которой добавлен столбец cdate для хранения дат файла данных в виде символьных строк:

mysql> CREATE TABLE tmp SELECT * FROM main WHERE 1 < 0;
mysql> ALTER TABLE tmp ADD cdate CHAR(8);

2.


Загрузить файл данных во временную таблицу, сохраняя значения данных в столбце cdate, а не date:

mysql> LOAD DATA LOCAL INFILE 'newdata.txt' INTO TABLE tmp (name,cdate,value);

3. Преобразовать значения cdate из формата MM/DD/YY в YY-MM-DD и сохранить результаты в столбце date:

mysql> UPDATE tmp
-> SET date = CONCAT(RIGHT(cdate,2),'-',LEFT(cdate,2),'-',MID(cdate,4,2));

MySQL автоматически преобразует двузначные значения года в четырехзначные, так что исходные значения MM/DD/YY столбца cdate превратятся в столбце date в значения ISO в формате CCYY-MM-DD. Следующий запрос показывает, как выглядят исходные значения cdate и преобразованные значения date после выполнения предложения UPDATE:

mysql> SELECT cdate, date FROM tmp;

+----------+----------------+
| cdate | date |
+----------+----------------+
| 01/01/99 | 1999-01-01 |
| 12/31/00 | 2000-12-31 |
| 02/28/01 | 2001-02-28 |
| 01/02/03 | 2003-01-02 |
+----------+----------------+

4. Наконец, скопировать записи из таблицы tmp в main (используя преобразованные значения дат, а не исходные значения cdate) и удалить временную таблицу:

mysql> INSERT INTO main (name, date, value)
-> SELECT name, date, value FROM tmp;
mysql> DROP TABLE tmp;

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


То есть составляющая года должна соответствовать годам из диапазона с 1970 по 2069 год. Если это не так, необходимо выполнить преобразование года каким-то другим способом.

Кроме того, предполагается, что значения cdate всегда состоят ровно из восьми символов, поэтому для извлечения составляющих можно использовать функции LEFT(), MID() и RIGHT(). Если такое предположение неверно, необходимо изменить процедуру конвертации. Можно, например, применить SUBSTRING_INDEX() для разбиения строк на части по разделителям /:

mysql> UPDATE tmp
-> SET date =
-> CONCAT(SUBSTRING_INDEX(cdate,'/',-1),'-',
-> SUBSTRING_INDEX(cdate,'/',1),'-',
-> SUBSTRING_INDEX(SUBSTRING_INDEX(cdate,'/',2),'/',-1));

Еще одним приложением обработки данных после импортирования является разбиение имен. Если вы импортируете значения, включающие имя, пробел и фамилию, в столбец full_name, то можете разбить его на два отдельных столбца first_name и last_name при помощи таких предложений:

UPDATE имя_таблицы SET first_name = SUBSTRING_INDEX(full_name,' ',1);
UPDATE имя_таблицы SET last_name = SUBSTRING_INDEX(full_name,' ',-1);

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


и Sr. Если дело обстоит именно так, лучше выполнить предварительную обработку имен до импортирования, используя утилиту сравнения с образцом, которая лучше умеет разбивать полные имена на составляющие.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт XML в MySQL
Импорт с помощью LOAD DATA и утилиты mysqlimport
Импорт файлов в формате CSV

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