Одной из ранее не рассматриваемых полезных функций 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 или 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 (атомарность, целостность, изоляция, постоянство) представляет собой способ описания четырех требований, которым должны удовлетворять транзакции:
Транзакция, результат выполнения которой записан в базу данных, называют подтвержденной. Транзакция, результат выполнения которой не записан в базу данных — то есть база данных возвращена в то состояние, каком она была до начала выполнения транзакции — называют откатанной транзакцией.
По умолчанию 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);
Вы должны получить сообщение об ошибке, аналогично следующему:
Веб-инструмент
Обсудить на форуме