Все о MySQL. Копирование, импорт и экспорт записей

Кроме возможности ручного ввода записей с помощью оператора INSERT, MySQL имеет в своем арсенале множество конструкций ЯМД, предназначенных для автоматического добавления записей путем копирования их из другой таблицы или импортирования их из тестового файла. Кроме того, возможен экспорт записей в текстовые файлы с помощью оператора SELECT - это возможность, которой можно воспользоваться при резервном копировании или переносе данных. Подробнее об этом - в следующих разделах. 
Копирование записей
MySQL позволяет объединять операторы INSERT и SELECT для динамического заполнения таблицы результатами выборки оператора SELECT или для копирования данных из одной таблицы в другую. Например, у вас имеется таблица с данными о служащих,

и требуется создать записи по пользователям для каждого из этих служащих в отдельной таблице users. Для этого можно воспользоваться оператором INSERT совместно с оператором SELECT для автоматического воссоздания всех нужных записей.
Листинг 9.61.
mysql> INSERT INTO users (uname, upass) SELECT LOWER (fname), PASSWORD (LOWER (fname)) FROM employees; Query OK, 6 rows affected (0.07 sec) Records: 6 Duplicates: 0 Warnings: 6

Совершенно очевидно, что список полей, приведенный в операторе INSERT, должен полностью совпадать со списком столбцов, возвращаемых предложением SELECT. Несоответствие полей приводит к ошибке.
Листинг 9.62.
mysql> INSERT INTO tbl1 (fld1, fld2) SELECT fld1, fld2, fld3 FROM
tbl2;
ERROR 1136: Column count doesn't match value count at row 1
Естественно, можно добавить к оператору SELECT предложение WHERE для того, чтобы копировалась только определенная выборка или подмножество записей исходной таблицы.
Листинг 9.63.
mysql> INSERT INTO users (uname, upass) SELECT LOWER(fname), PASSWORD (LOWER(fname)) FROM employees WHERE country = 'US';
Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 3

Как выбраться из западни
Наличие в диалекте SQL MySQL синтаксической конструкции INSERT... SELECT вместе с ключевым словом IGNORE и поддержкой временных таблиц, обеспечивает широкие возможности для продуктивной перезаписи запросов SELECT и увеличения скорости работы.
Например, у нас есть сложный запрос, включающий выборку набора значений оп­ределенного поля, а обработчик MySQL не имеет возможности оптимизации их из-за их сложности. Опытные программисты SQL могут улучшить их характеристики путем (а) разбивки одного сложного запроса на ряд простых, которые оптимизатор обрабатывает эффективнее, (б) а затем используя синтаксическую конструкцию INSERT IGNORE...SELECT для добавления полученного результата во временную таблицу, и после этого создать временную таблицу с ключевым словом UNIQUE по соответствующему полю. В результате имеем: набор неповторяющихся значений по этому полю и возможность ускорения выполнения запроса.

Импорт записей
Оператор INSERT - это не единственный способ добавления данных в таблицу. MySQL также позволяет производить одновременную вставку нескольких записей с помощью команды LOAD DATA INFILE. Эта команда может использоваться для "заливки" данных из текстового файла (расположенного на сервере или на локальном компьютере клиента), анализа его на основе информации об ограничителях столбцов и строк и автоматической генерации последовательности операторов INSERT для записи данных в таблицу.
Этот подход очень удобен при необходимости ввода большого количества информации в базу данных, при этом даже структурированные данные не требуется представлять в виде операторов SQL. Создание операторов INSERT вручную для каждой записи в этом случае может оказаться утомительным и потребует слишком много времени; оператор LOAD DATA INFILE предлагает более быструю и надежную альтернативу.
Чтобы лучше понять принцип работы оператора LOAD DATA INFILE, приведем пример. Пусть в нашем распоряжении имеется пустая таблица birthdays,

По умолчанию MySQL "считает", что файл с данными находится на сервере в каталоге, определенном оператором LOAD DATA INFILE. Если данные находятся на локальном компьютере, в оператор можно добавить ключевое слово LOCAL, таким образом MySQL "поймет", что файл находится в файловой системе клиента. Следующий пример демонстрирует, как можно осуществлять загрузку данных с клиентской машины.
Листинг 9.68.
rnysql> LOAD DATA LOCAL INFILE 'c:data.txt' INTO TABLE birthdays FIELDS TERMINATED BY',' LINES TERMINATED BY 'rn';
Query OK, 5 rows affected (0.06 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 Если в файле данных находится меньше данных, чем в таблице, или значения располагаются совсем не в том порядке, что поля таблицы, то этот порядок можно задать, указав перечисление полей в операторе LOAD DATA INFILE. Так, таблица addressbook содержит пять столбцов, 

Очевидно, что MySQL вставит значения NULL (если это разрешено ограничениями по таблице и полям), когда возникнет вопрос о полях, которые в списке не указаны.
На верном пути
Если при работе с файлами данных, расположенными на сервере, в операторе LOAD DATA INFILE путь к данным не задан (или в случае задания относительного пути), 
MySQL сначала выполняет поиск в соответствующем каталоге базы данных (или использует его в качестве корня относительного пути). Однако абсолютные пути будут рассматриваться в соответствии с тем, как они заданы.
Следующие ключевые слова используются для управления поведением команды LOAD DATA INFILE.
■ Ключевое слово LOW_PRIORITY приводит к ожиданию сервером того момента, пока другие потоки не закончат использование таблицы. Процесс импортирования начинается только после освобождения таблицы. С другой стороны, ключевое слово CONCURRENT позволяет во время выполнения операции импортирования считывать данные другим клиентам из таблицы (однако это ключевое слово работает только с таблицами формата MyISAM):
mysql> LOAD DATA LOW_PRIORITY INFILE 'log.asc' INTO TABLE log-
data;
Query OK, 76 rows affected (0.10 sec)
mysql> LOAD DATA CONCURRENT LOCAL INFILE 'd:palmaddressbook.txt' INTO TABLE addressbook;
Query OK, 106 rows affected (1.45 sec)
■ Ключевое слово IGNORE позволяет при дублировании новой записью какой-либо уже существующей записи, просто пропустить ее (вместо того, чтобы завершить выполнение операции, что является стандартным действием в этой ситуации). Есть и другая возможность - заменить существующие записи новыми записями из файла данных. Этого эффекта можно добиться применением вместо ключевого слова IGNORE ключевого слова REPLACE:
mysql> LOAD DATA INFILE '/tmp/users.txt1 INTO TABLE users LINES TERMINATED BY 'rn' (uname);
ERROR 1062: Duplicate entry 'emma' for key 1
mysql> LOAD DATA INFILE '/tmp/users.txt' IGNORE INTO TABLE users LINES TERMINATED BY 'rn' (uname);
Query OK, 5 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 1 Warnings: 0
mysql> LOAD DATA INFILE '/tmp/users.txt' REPLACE INTO TABLE users LINES TERMINATED BY 'rn' (uname);
Query OK, 7 rows affected (0.06 sec) Records: 6 Deleted: 1 Skipped: 0 Warnings: 0