ресурс для начинающих веб-разработчиков
комплексные веб-услуги по созданию сайтов

Оператор LOAD DATA INFILE

Одной из ранее не рассматриваемых полезных функций MySQL является оператор LOAD DATA INFILE. Его можно использовать для загрузки данных из файла. Этот оператор выполняется очень быстро.

Эта представляющая множество возможностей команда принимает набор параметров. Типичная форма ее применения выглядит следующим образом:

LOAD DATA INFILE "newbooks.txt" INTO TABLE books;

Показанная строка считывает данные из файла newbooks.txt в таблицу books. По умолчанию поля данных в файле должны разделяться символами табуляции и быть заключены в одинарные кавычки, а строки должны разделяться символом новой строки (\n). Специальные символы должны быть отменены с помощью символа косой черты (\). Все эти характеристики доступны для конфигурирования через параметры оператора LOAD.

Чтобы иметь возможность использовать оператор LOAD DATA INFILE, пользователь должен обладать полномочиями FILE, описанными ранее.

Механизмы хранения

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

Тип таблицы можно выбрать во время ее создания с помощью следующего оператора:

CREATE TABLE имя_таблицы TYPE = тип . . .

Возможны следующие типы таблиц:

• MyISAM. Этот тип используется по умолчанию, и именно он применялся до сих пор. Он построен на основе традиционного типа ISAM, название которого представляет собой аббревиатуру от Indexed Sequential Asses Method(индесно-последовательный метод доступа) — стандартного метода хранения записей и файлов. В отличии от ISAM, MyISAM предосталяет ряд дополнительных преимуществ. По сравнению с другими механизмами хранения MyISAM обеспечивает наибольшее количество средств проверки и восстановления таблиц. Таблицы MyISAM допускают сжатие и поддерживают полнотекстовой поиск. Тем не менее, они не обеспечивают безопасное выполнение транзакций и не поддерживают внешние ключи.
• ISAM. Этот тип описан в предыдущем пункте. Пользоваться таблицами ISAM не рекомендуется.
• MEMORY(ранее отот тип назывался HEAP). Таблицы этого типа храняться в памяти и их индексы хешируются. Обработка таблиц MEMOTY выполняется очень быстро, однако любой сбой приводит к потере данных. Перечисленные характеристики делают таблицы типа MEMORY идеально подходящими для хранения временных данных или данных, поученных в результате вычислений. При использовании этого типа в операторе CREATE TABLE необходимо указывать параметр MAX_ROWS (максимальное число строк). В противном случае эти таблицы могут занять всю память. Кроме того, эти таблицы не могут содержать столбца типа BLOB, TEXT или AUTO INCREMENT.
• MERGE. Эти таблицы позволяют при запросах выполнять обработку коллекции таблиц MyISAM rf единой таблицы. В результате удается обходить ограничения на максимальный размер таблиц в некоторых операционных системах.
• BDB. Эти таблицы обеспечивают безопасное выполнение транзакций, то есть они предоставляют возможность использования операторов COMMIT и ROLLBACK. Их обработка выполняется медленнее, чем таблиц MyISAM, но зато они задействуют все преимущества транзакций. Эти таблицы построены на основе базы данных Berkeley DB.
• InnoDB. эти таблицы также обеспечивают безопасное выполнение транзакций, и в отношении них справедливы те же соображения, что и по поводу таблиц BDB. Они также поддерживают внешние ключи. Обработка таблиц InnoDB выполняется быстрее, и они предоставляют большие функциональные возможности, нежели таблицы BDB, поэтому мы рекомендуем их использовать в тех случаях, когда требуется обеспечить безопастное выполнение транзакций.

В большинстве случаев в веб-приложениях придется применять таблицы MyISAM или InnoDB, либо их комбинацию.

Таблицы MyISAM следует использовать в тех случаях, когда в таблицах приходится выполнять много операций SELECT или INSERT (но не и тех и других одновременно), поскольку они обеспечивают наиболее быстрое их выполнение. Для многих веб-приложений, таких как каталоги, MyISAM является лучшим выбором. Таблицы MyISAM следует применять и тогда, когда необходимы возможности полнотекстового поиска. Таблицы InnoDB следует использовать в тех случаях, когда важно обеспечить выполнение трансзакций, например, в таблицах финансовых данных или в ситуациях поочередного применения операций INSERT и SELECT (например, на сетевых досках объявлений или форумах).

Тип MEMORY можно использовать для временных таблиц или для реализации представлений, а тип MERGE — когда приходится иметь дело с очень большими таблицами MyISAM.

После создания тип таблицы можно изменить с помощью оператора ALTER TABLE, как показано в следующем примере:

alter table orders type = innodb;

alter table order_items type = innodb;

В большинстве примеров применяются таблицы MyISAM. А теперь уделим немного времени использованию транзакций и способам их реализации в таблицах InnoDB.

Транзакции

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

Определения транзакций

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

Чтобы понять, почему эта возможность может оказаться настолько важной, рассмотрим банковскую базу данных. Представим себе ситуацию, в которой нужно выполнить перевод денег с одного счета на другой. Это действие предполагает снятие денег с одного счета на другой. Это действие предполагает снятие денег с одного счета и помещение их на другой, для чего потребуется выполнение, как минимум двух запросов. Что произойдет, если деньги будут сняты с одного счета, а напряжение питания пропадет прежде, чем они будут помещены на другой счет? Означает ли это, что деньги просто "исчезнут"?

Возможно, вы сталкивались с понятием соответствия ACID. ACID (атомарность, целостность, изоляция, постоянство) представляет собой способ описания четырех требований, которым должны удовлетворять транзакции:

• Атомарность. Транзакция должна быть атомарной, то есть либо выполняться полностью, либо не выполняться вообще.
• Целостность. Транзакция должна оставлять базу данных в целостном состоянии.
• Изоляция. Незавершенные транзакции не должны быть видимы другими пользователями базы данных, то есть до тех пор, пока они не завершены, транзакции должны оставаться изолированными.
• Постоянство. После сохранения в базе данных, результаты выполнения транзакции должны быть долговременными или постоянными.

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

Использование транзакции в сочетании с таблицами InnoDB

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

Чтобы в текущем сеансе отключить режим автоматического подтверждения, введите следующую команду:

set autocommit = 0;

Если режим автоматического подтверждения включен, транзакция должна начинаться с оператора

start transaction;

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

По завершении ввода операторов, образующих транзакцию, их можно подтвердить в базе данных с помощью следующей команды:

commit;

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

rollback;

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

Рассмотрим пример. Если это еще не сделано, выполните приведенные в предыдущем разделе операторы ALTER TABLE применительно к базе данных books, как показано ниже:

alter table orders type = innodb;

alter table order_items type = innodb;

Эти операторы преобразуют две таблицы в таблицы InnoDB. Впоследствии при желании можно будет выполнить обратное преобразование с помощью того же оператора, но на этот раз с параметром type = MyISAM.

Теперь откройте два соединения с базой данных books. В одном соединении добавьте в базу данных новую запись заказа:

insert into orders values (5, 1, 300.00, '2011-01-15');

insert into order_items values (5, '0-2-1', 1);

Теперь посмотрим, видим ли новый заказ:

select * from orders where orderid = 5;

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

Оставив текущее состояние открытым, переходите к другому соединению и выполните такой же запрос select. Заказ не должен отображаться в выводе. Если он всеже отображается, скорее всего, вы забыли отключить режим автоматического подтверждения. Проверьте это, а также то, что таблица была преобразована в формат InnoDB. Это обусловлено тем, что транзакция еще не подтверждена.

Вернитесь к первому соединению и подтвердите транзакцию:

commit;

Теперь соответствующая строка должна отображаться в выводе и во втором соединении.

Внешние ключи

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

Например, рассмотрим вставку строки в таблицу order_items. Прии этом необходимо указывать допустимое значение столбца orderid/ В случае использования таблицы MyISAM допустимость вставляемого значения orderid приходится проверять где-то в коде приложения. Применение внешних ключей в таблице InnoDB позволяет выполнять эту проверку в базе данных.

Рассмотрим, как определяются внешние ключи. Чтобы создать таблицу, которая использует внешний ключ с самого начала, можно следующим образом изменить соответствующий DDL-оператор:

create table order_items

(orderid int unsigned not null references orders (orderid),

isbn char (13) not null,

quantity tinyint unsigned,

primary key (orderid, isbn)

) type = InnoDB;

После orderid мы добавили слова references orders (orderid). Это означает, что данный столбец является внешним ключом, который должен содержать значение из столбца orderid таблицы orders.

Кроме того, в конец объявления помещен тип таблицы type = InnoDB. Это требуется для обеспечения работы внешних ключей.

С помощью операторов ALTER TABLE эти же изменения можно внести в существующую таблицу:

alter table order_items type = InnoDB;

alter table order_items

add foreign key (orderid) references orders (orderid);

Чтобы убедиться в работе этого изменения, можно попытаться вставить строку, содержащую значение поля orderid? которая не имеет соответствия в таблице orders:

insert into order_items

values (34, '0-3-4', 5);

Вы должны получить сообщение об ошибке, аналогично следующему: