MySQL - свободная реляционная система управления базами данных

Размещено на http://www.allbest.ru/

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ УКРАИНЫ

ДОНЕЦКИЙ НАЦИОНАЛЬНЫЙ УНИВЕРСИТЕТ

МАТЕМАТИЧЕСКИЙ ФАКУЛЬТЕТ

КАФЕДРА ПРИКЛАДНОЙ МАТЕМАТИКИ И ТСУ

Реферат

на тему: "MySQL - свободная реляционная система управления базами данных"

Донецк - 2011

1. Что такое MySQL?

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

MySQL был разработан компанией TcX для внутренних нужд, которые заключались в быстрой обработке очень больших баз данных. Компания утверждает, что использует MySQL с 1996 года на сервере с более чем 40 БД, которые содержат 10,000 таблиц, из которых более чем 500 имеют более 7 миллионов строк.

MySQL является идеальным решением для малых и средних приложений. Исходники сервера компилируются на множестве платформ. Наиболее полно возможности сервера проявляются на Unix-серверах, где есть поддержка многопоточности, что дает значительный прирост производительности.

На текущий момент MySQL все еще в стадии разработки, хотя версии 3.22 полностью работоспособны.

MySQL-сервер является бесплатным для некоммерческого использования. Иначе необходимо приобретение лицензии, стоимость которой составляет 190 EUR.

Возможности MySQL. MySQL поддерживает язык запросов SQL в стандарте ANSI 92, и кроме этого имеет множество расширений к этому стандарту, которых нет ни в одной другой СУБД.

Краткий перечень возможностей MySQL.

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

2. Количество строк в таблицах может достигать 50 млн.

3. Быстрое выполнение команд. Возможно MySQL самый быстрый сервер из существующих.

4. Простая и эффективная система безопасности.

MySQL действительно очень быстрый сервер, но для достижения этого разработчикам пришлось пожертвовать некоторыми требованиями к реляционным СУБД. В MySQL отсутствуют:

5. Поддержка вложенных запросов, типа SELECT * FROM table1 WHERE id IN (SELECT id FROM table2). Утверждается, что такая возможность будет в версии 3.23.

6. Не реализована поддержка транзакций. Взамен предлагается использовать LOCK/UNLOCK TABLE.

7. Нет поддержки внешних (foreign) ключей.

8. Нет поддержки триггеров и хранимых процедур.

9. Нет поддержки представлений (VIEW). В версии 3.23 планируется возможность создавать представления.

По словам создателей именно пункты 2-4 дали возможность достичь высокого быстродействия. Их реализация существенно снижает скорость сервера. Эти возможности не являются критичными при создании Web-приложений, что в сочетании с высоким быстродействием и малой ценой позволило серверу приобрести большую популярность.



2. Резервное копирование баз MySQL

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

Вообще, почти все хостинг-провайдеры производят резервное копирование всех файлов пользователей. Однако, не стоит забывать о том, что провайдеры делают backup, в основном, для себя, на случай аварии у себя. Именно по этой причине пользователи в условиях хостинга могут, конечно, рассчитывать на восстановление в случае удаления каких-то данных по вине самого пользователя, но вовсе не факт, что провайдер сделает восстановление MySQL-базы сразу по получению запроса. Лучше делать для себя копию и в случае чего ее использовать. Можно даже периодически копировать этот свой backup на другую, не провайдерскую машину - так надежнее, на всякий случай.

Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически "откладывать в сторонку" и копии скриптов. Гораздо более сложной представляется задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:

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

Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда "клюнул жареный петух". То есть, в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет.

В случае, если веб-мастер не владеет в достаточной мере навыками работы со специализированными утилитами из пакета MySQL, могут возникать трудности, связанные с ограничениями, налагаемыми хостинг-провайдером на пользовательские аккаунты. Например, если база очень большая и ее размер превышает лимит на доступную пользователю память (RAM), backup сделать будет сложно. Нужно пользоваться тонкими настройками утилит резервного копирования, что иногда тоже вызывает трудности на практике.

2.1 Как сделать копию базы MySQL?

Существует программа mysqldump, позволяющая быстро и просто производить операции по созданию резервных копий баз MySQL. Также mysqldump дает возможность делать очень тонкие настройки для управления процессом создания резервных копий баз данных или отдельных таблиц. Можно сказать, что mysqldump - это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.

Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер. Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS. Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга. Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае - " > dump.txt ". Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.

Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных. То есть, вывод mysqldump - это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария. Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись "1111", то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14

#

# Host: HOST Database: DBNAME

#--------------------------------------------------------

# Server version 3.23.39-log

#

# Table structure for table 'test'

#

CREATE TABLE test (

test2 int(11) default NULL

) TYPE=MyISAM;

#

# Dumping data for table 'test2'

#

INSERT INTO test2 VALUES ('1111');

Таким образом, mysqldump "опишет" все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс - воссоздание базы из резервной копии.

Для восстановления будем пользоваться стандартной программой mysql, которая входит в комплект поставки MySQL наряду с mysqldump. Допустим, у нас имеется backup в файле dump.txt. Нам нужно восстановить его в рабочую базу. Например, мы случайно удалили нашу базу данных, а теперь пытаемся исправить эту незадачу. Делаем так:

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt

То есть, заставляем mysql-клиент соединиться с сервером и выполнить сценарий, который у нас имеется. После выполнения этой команды в Вашей базе появятся таблицы и данные из резервной копии. Учитывайте то, что данные будут просто восстанавливаться по сценарию из dump.txt. То есть, если таблицы, которые упоминаются в дампе базы, уже существуют и имеют другую структуру, тут явно возникнет ошибка. Просто посмотрите на сценарий и на рабочую базу и представьте, что Вы вручную выполняете команды из сценария. Если уверены, что все будет хорошо - смело восстанавливайте.

2.2 Более тонкие настройки mysqldump

--databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;

--all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);

Ключ --help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;

--add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;

--no-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;

--result-file=... - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick. Это решит проблему.

2.3 Автоматизация резервного копирования

Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа - cron. Она позволяет запускать процессы в указанное пользователем время или с определенной периодичностью. Сразу оговоримся - cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время. Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME

| gzip -c > `date "+ %Y- %m- %d"`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz. Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали - date "+ %Y- %m- %d". Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то, что испортилось, например. Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find ~/каталог-с-архивами -name "*.gz" -mtime +7, Вы будете удалять архивы, которые "старше" семи дней. Прочитайте документацию по find - она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернету, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина - это очень надежная штука. Однако, как говорится, "береженого Бог бережет". Старая как мир истина в определенных условиях может и Вам помочь. Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron. Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов - scp. Читайте документацию по этой команде в man-странице man scp. Примерный запуск: scp 2002-01-03.gz: - закачиваем файл 2002-01-03.gz на машину your.host.ru авторизовавшись там под логином login.



3. Обеспечение целостности данных

3.1 Транзакции

Транзакции - это механизмы обеспечения целостности баз данных, особенно в случаях ошибок или отказа сервера.

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

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

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

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

· Целостность. Транзакция должна оставлять базу данных в целостном состоянии.

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

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

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

3.2 Триггеры в MySQL

Триггер - это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события (вставка, удаление, обновление строки).

Поддержка триггеров в MySQL началась с версии 5.0.2.

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

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

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

Синтаксис создания триггера:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt* This source code was highlighted with Source Code Highlighter.

trigger_name - название триггера

trigger_time - Время срабатывания триггера. BEFORE - перед событием. AFTER - после события.

trigger_event - Событие:

insert - событие возбуждается операторами insert, data load, replace

update - событие возбуждается оператором update

delete - событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера

tbl_name - название таблицы

trigger_stmt выражение, которое выполняется при активации триггера.

Применение. Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1-ю таблицу будет вести лог этого события:

Лог. Исходные данные:

- таблица, за которой мы будем следить

CREATE TABLE `test` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`content` TEXT NOT NULL) ENGINE = MYISAM - лог CREATE TABLE `log` (`id` INT(11) UNSIGNED NOTNULL AUTO_INCREMENT PRIMARY KEY,`msg` VARCHAR(255) NOT NULL,`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `row_id` INT(11) NOT NULL) ENGINE = MYISAM - триггер DELIMITER | CREATE TRIGGER `update_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO log Set msg = 'insert', row_id = NEW.id; END;* This source code was highlighted with Source Code Highlighter.

Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

Расширенный лог:

Исходные данные:

- Удаляем триггер

DROP TRIGGER `update_test`;

- Cоздадим еще одну таблицу,

- в которой будут храниться резервные копии строк из таблицы test

CREATE TABLE `testing`.`backup` (

`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

`row_id` INT(11) UNSIGNED NOT NULL,

`content` TEXT NOT NULL

) ENGINE = MYISAM

- триггеры

DELIMITER |

CREATE TRIGGER `update_test` before update ON `test`

FOR EACH ROW BEGIN

INSERT INTO backup Set row_id = OLD.id, content = OLD.content;

END;

CREATE TRIGGER `delete_test` before delete ON `test`

FOR EACH ROW BEGIN

INSERT INTO backup Set row_id = OLD.id, content = OLD.content;

END* This source code was highlighted with Source Code Highlighter.

Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

3.3 Поддержка целостности ваших данных

Ранее в этой книге, мы указывали на определенные связи, которые существуют между некоторыми полями типовых таблиц. Поле snum таблицы Заказчиков, например, соответствует полю snum в таблице Продавцов и таблице Порядков. Поле cnum таблицы Заказчиков также соответствует полю cnum таблицы Порядков. Мы назвали этот тип связи - справочной целостностью; и в ходе обсуждения, вы видели, как ее можно использовать.

В этой главе, вы будете исследовать справочную целостность более подробно и выясним все относительно ограничений, которые вы можете использовать чтобы ее поддерживать. Вы также увидите, как предписывает- с это ограничение, когда вы используете команды модификации DML. Поскольку справочна целостность включает в себя связь полей или групп полей, часто в разных таблицах, это действие может быть несколько сложнее чем другие ограничения. По этой причине, хорошо иметь с ней полное знакомство, даже если вы не планируете создавать таблицы. Ваши команды модификации могут стать эффективнее с помощью ограничения справочной целостности (как и с помощью других ограничений, но ограничение справочной целостности может воздействовать на другие таблицы кроме тех в которых оно определено), а определенные функции запроса, такие как объединения, являются многократно структурированы в терминах связей справочной целостности (как подчеркивалось в Главе 8).

Внешний ключ и родительский ключ. Когда все значения в одном поле таблицы представлены в поле другой таблицы, мы говорим, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Например, каждый из заказчиков в таблице Заказчиков имеет поле snum которое указывает на продавца, назначенного в таблице Продавцов. Для каждого порядка в таблице Порядков, имеется один и только этот продавец и один, и только этот заказчик. Это отображается с помощью полей snum и cnum в таблице Порядков.

Когда одно поле в таблице ссылается на другое, оно называется - внешним ключом; а поле на которое оно ссылается, называется - родительским ключом. Так что поле snum таблицы Заказчиков - это внешний ключ, а поле snum на которое оно ссылается в таблице Продавцов - это родительский ключ.

Аналогично, пол cnum и snum таблицы Порядков - это внешние ключи, которые ссылаются к их родительским ключам с именами в таблице Заказчиков и таблице Продавцов. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми, это - только соглашение которому мы следуем чтобы делать соединение более понятным.

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

Смысл внешнего и родительского ключей. Когда поле - является внешним ключом, оно определенным образом связано с таблицей, на которую он ссылается. Вы, фактически, говорите - " каждое значение в этом поле (внешнем ключе) непосредственно привязано к значению в другом поле (родительском ключе)." Каждое значение (каждая строка) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это так, то фактически ваша система, как говорится, будет в состоянии справочной целостности. Вы можете увидеть это на примере. Внешний ключ snum в таблице Заказчиков имеет значение 1001 для строк Hoffman и Clemens. Предположим, что мы имели две строки в таблице Продавцов со значением в поле snum = 1001. Как мы узнаем, к которому из двух продавцов были назначены заказчики Hoffman и Clemens? Аналогично, если нет никаких таких строк в таблице Продавцов, мы получим Hoffman и Clemens назначенными к продавцу, которого не существует!

Понятно, что каждое значение во внешнем ключе должно быть представлено один, и только один раз, в родительском ключе.

Фактически, данное значение внешнего ключа может ссылаться только к одному значению родительского ключа, не предполагая обратной возможности: т.е. любое число внешних ключей может ссылать к единственному значению родительского ключа. Вы можете увидеть это в типовых таблицах наших примеров. И Hoffman и Clemens назначены к Peel, так что оба их значения внешнего ключа совпадают с одним и тем же родительским ключом, что очень хорошо. Значение внешнего ключа должно ссылаться только к одному значению родительского ключа, зато значение родительского ключа может ссылаться с помощью любого количества значений внешнего ключа. В качестве иллюстрации, значения внешнего ключа из таблицы Заказчиков, совпавшие с их родительским ключом в Продавцов таблице, показываются в Рисунке 19.1. Для удобства мы не учитывали пол не относящиеся к этому примеру.

Ограничение foreign key. SQL поддерживает справочную целостность с ограничением FOREIGN KEY. Хотя ограничение FOREIGN KEY - это нова особенность в SQL, оно еще не обеспечивает его универсальности. Кроме того, некоторые его реализации, более сложны чем другие. Эта функция должна ограничивать значения, которые вы можете ввести в вашу базу данных чтобы заставить внешний ключ и родительский ключ соответствовать принципу справочной целостности. Одно из действий ограничения Внешнего Ключа - это отбрасывание значений для полей, ограниченных как внешний ключ который еще не представлен в ро- дительском ключе. Это ограничение также воздействует на вашу способность изменять или удалять значения родительского ключа (мы будем обсуждать это позже в этой главе).

Как можно поля представить в качестве внешних ключей? Вы используете ограничение FOREIGN KEY в команде CREATE TABLE (или ALTER TABLE), которая содержит поле, которое вы хотите объявить внешним ключом. Вы даете им родительскому ключу на которое вы будете ссылаться внутри ограничения FOREIGN KEY. Помещение этого ограничения в команду - такое же, что для других ограничений, обсужденных в предыдущей главе. Рисунок 19.1: Внешний Ключ таблицы Заказчиков с родительским ключом.

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

Внешний ключ как ограничение таблицы. Синтаксис ограничения таблицы FOREIGN KEY:

FOREIGN KEY <COLUMN list> REFERENCES

<PKTABLE> [<COLUMN list>]

Первый список столбцов - это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. Pktable - это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов - это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

* Они должны иметь одинаковое число столбцов.

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

Создадим таблицу Заказчиков с полем snum определенным в качестве внешнего ключа, ссылающегося на таблицу Продавцов:

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY

cname char(10),

city char(10),

snum integer,

FOREIGN KEY (snum) REFERENCES Salespeople

(snum);

Имейте в виду, что при использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, которые Вы указываете во внешнем ключе и родительском ключе, должны быть в состоянии справочной целостности. Иначе команда будет отклонена. Хотя ALTER TABLE очень полезна из-за ее удобства, вы должны будете в вашей системе, по возможности каждый раз, сначала формировать структурные принципы, типа справочной целостности.

Внешний ключ как ограничение столбцов. Вариант ограничения столбца ограничением FOREIGN KEY - по другому называется - ссылочное ограничение (REFERENCES), так как он фактически не со- держит в себе слов FOREIGN KEY, а просто использует слово REFERENCES, и далее им родительского ключа, подобно этому:

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10),

city char(10),

snum integer REFERENCES Salespeople (snum));

Вышеупомянутое определяет Customers.snum как внешний ключ у которого родительский ключ - это Salespeople.snum. Это эквивалентно такому ограничению таблицы: FOREIGN KEY (snum) REGERENCES Salespeople (snum)

Не указывать список столбцов первичных ключей. Используя ограничение FOREIGN KEY таблицы или столбца, вы можете не указывать список столбцов родительского ключа если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двум ключами все еще применим. Например, если мы поместили ограничение PRIMARY KEY в поле snum таблицы Продавцов, мы могли бы использовать его как внешний ключ в таблице Заказчиков (подобно предыдущему примеру) в этой команде:

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10),

city char(10),

snum integer REFERENCES Salespeople);

Это средство встраивалось в язык, чтобы поощрять вас использовать первич- ные ключи в качестве родительских ключей.

Как справочная целостность ограничивает значения родительского ключа. Поддержание справочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL). Этого недостаточно для родительского ключа в случае выполнения такого требования как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно, вы должны убедиться, что все пол, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.

Первичный ключ как уникальный внешний ключ. Ссылка ваших внешних ключей только на первичные ключи, как мы это делали в типовых таблицах, - хороша стратеги. Когда вы используете внешние ключи, вы связываете их не просто с родительскими ключами, на которые они ссылаются; вы связываете их с определенной строкой таблицы где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации, которая бы не была уже представлена во внешнем ключе. Смысл, например, пол snum как внешнего ключа в таблице Заказчиков - это связь которую он обеспечивает, не к значению пол snum на которое он ссылается, а к другой информации в таблице Продавцов, такой, например, как, имена продавцов, их местоположение, и так далее. Внешний ключ - это не просто связь между двум идентичными значениями; это - связь, с помощью этих двух значений, между двум строками таблицы, указанной в запросе. Это поле snum может использоваться чтобы связывать любую информацию в строке из таблицы Заказчиков со ссылочной строкой из таблицы Продавцов - например, чтобы узнать - живут ли они в том же самом городе, кто имеет более длинное имя, имеет ли продавец кроме данного заказчика каких-то других заказчиков, и так далее. Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, вы должны создать внешний ключ, который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранить структуру вашей базы данных ясной и простой, и - следовательно создающей меньше трудностей.

Ограничения внешнего ключа. Внешний ключ, в частности, может содержать только те значения, которые фактически представлены в родительском ключе или пустые(NULL). Попытка ввести другие значения в этот ключ будет отклонена. Вы можете объявить внешний ключ как NOT NULL, но это необязательно, и в большинстве случаев, нежелательно. Например, предположим, что вы вводите заказчика не зная заранее, к какому продавцу он будет назначен. Лучший выход в этой ситуации, будет если использовать значение NOT NULL, которое должно быть изменено позже на конкретное значение.

Что случится, если вы выполните команду модификации? Давайте условимся, что все внешние ключи, созданные в наших таблицах приме- ров, объявлены и предписаны с ограничениями внешнего ключа, следующим образом:

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,

sname char(10) NOT NULL,

city char(10),

comm decimal);

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

city char(10),

rating integer,

snum integer,

FOREIGN KEY (snum) REFERENCES Salespeople,

UNIQUE (cnum, snum);

CREATE TABLE Orders

(cnum integer NOT NULL PRIMARY KEY,

amt decimal,

odate date NOT NULL,

cnum integer NOT NULL

snum integer NOT NULL

FOREIGN KEY (cnum, snum) REFERENCES

CUSTOMERS (cnum, snum);

Включение описаний таблицы. Имеется несколько атрибутов таких определений о которых нужно поговорить. Причина, по которой мы решили сделать пол cnum и snum в таблице Порядков, единым внешним ключом - это гарантия того, что для каждого заказчика, содержащегося в порядках, продавец кредитующий этот порядок - тот же что и указанный в таблице Заказчиков. Чтобы создать такой внешний ключ, мы были бы должны поместить ограничение таблицы UNIQUE в два пол таблицы Заказчиков, даже если оно необязательно для самой этой таблицы. Пока поле cnum в этой таблица имеет ограничение PRIMARY KEY, оно будет уникально в любом случае, и следовательно, невозможно получить еще одну комбинацию пол cnum с каким-то другим полем. Создание внешнего ключа таким способом поддерживает целостность базы данных, даже если при этом вам будет запрещено внутреннее прерывание по ошибке и кредитовать любого продавца, иного чем тот который назначен именно этому заказчику.

С точки зрения поддержания целостности базы данных, внутренние прерывания (или исключения) конечно же нежелательны. Если вы их допускаете и в то же время хотите поддерживать целостность вашей базы данных, вы можете объявить пол snum и cnum в таблице Порядков независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно. Фактически, использование пол snum в таблице Порядков, как мы это делали, необязательно, хотя это полезно было сделать для разнообразия. Поле cnum связывая каждый порядок заказчиков в таблице Заказчиков, в таблице Порядков и в таблице Заказчиков, должно всегда быть общим чтобы находить правильное поле snum для данного порядка (не разрешая никаких исключений). Это означает что мы записываем фрагмент информации - какой заказчик назначен к какому продавцу - дважды, и нужно будет выполнять дополнительную работу чтобы удостовериться, что обе версии согласуются. Если мы не имеем ограничения внешнего ключа как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом), чтобы удостовериться что именно соответствующий продавец кредитовал каждую соответствующую продажу. Наличие такого типа информационной избыточности в вашей базе данных, называется деморализация (denormalization), что нежелательно в идеальной реляционной базе данных, хотя практически и может быть разрешена. Деморализация может заставить некоторые запросы выполняться быстрее, поскольку запрос в одной таблице выполняется всегда значительно быстрее чем в объединении.

Действие ограничений. Как такие ограничения воздействуют на возможность и невозможность Вами использовать команды модификации DML? Для полей, определенных как внешние ключи, ответ довольно простой: любые значения которые вы помещаете в эти пол с командой INSERT или UPDATE должны уже быть представлены в их родительских ключах. Вы можете помещать пустые(NULL) значения в эти пол, несмотря на то что значения NULL не позволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE) любые строки с внешними ключами, не используя родительские ключи вообще.

Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, еще проще, но возможно несколько более ограничен: любое значение родительского ключа ссылаемого с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков пока он еще имеет порядки в таблице Порядков. В зависимости от того, как вы используете эти таблицы, это может быть или желательно или хлопотно. Однако - это конечно лучше, чем иметь систему, которая позволит вам удалить заказчика с текущими порядками и оставить таблицу Порядков ссылающейся на несуществующих заказчиков. Смысл этой системы ограничения в том, что создатель таблицы Порядков, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи может наложить значительные ограничения на действия в этих таблицах. По этой причине, вы не сможете использовать таблицу, которой вы не распоряжаетесь (т.е. не вы ее создавали и не вы являетесь ее владельцем), по- ка владелец(создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22). Имеются некоторые другие возможные действия изменения родительского ключа, которые не являются частью ANSI, но могут быть найдены в некоторых коммерческих программах. Если вы хотите изменить или удалить текущее ссылочное значение родительского ключа, имеется по существу три возможности:

? Вы можете ограничить, или запретить, изменение (способом ANSI), обозначив, что изменения в родительском ключе - ограничены.

? Вы можете сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, что называется - каскадным изменением.

? Вы можете сделать изменение в родительском ключе, и установить внешний ключ в NULL, автоматически (полагая, что NULLS разрешен во внешнем ключе), что называется - пустым изменением внешнего ключа.

Даже в пределах этих трех категорий, вы можете не захотеть обрабатывать все команды модификации таким способом. INSERT, конечно, к делу не относится. Он помещает новые значения родительского ключа в таблицу, так что ни одно из этих значений не может быть вызвано в данный момент. Однако, вы можете захотеть позволить модификациям быть каскадными, но без удалений, и наоборот. Лучшей может быть ситуация которая позволит вам определять любую из трех категорий, независимо от команд UPDATE и DELETE. Мы будем следовательно ссылаться на эффект модификации (update effects) и эффект удаления (delete effects), которые определяют, что случится если вы выполните команды UPDATE или DELETE в родительском ключе. Эти эффекты, о которых мы говорили, называются: Ограниченные (RESTRICTED) изменения, Каскадируемые (CASCADES) изменения, и Пустые (NULL) изменения. Фактические возможности вашей системы должны быть в строгом стандарте ANSI - это эффекты модификации и удаления, оба, автоматически ограниченные - для более идеальной ситуации, описанной выше. В качестве иллюстрации, мы покажем несколько примеров того, что вы можете делать с полным набором эффектов модификации и удаления. Конечно, эффекты модификации и удаления, являющиеся нестандартными средствами, испытывают недостаток в стандартном госинтаксисе. Синтаксис, который мы используем здесь, прост в написании и будет служить в дальнейшем для иллюстрации функций этих эффектов.

Для полноты эксперимента, позволим себе предположить, что вы имеете причину изменить поле snum таблицы Продавцов в случае, когда наша таблица Продавцов изменяет разделы. (Обычно изменение первичных ключей - это не то что мы рекомендуем делать практически. Просто это еще один из доводов для имеющихся первичных ключей, которые не умеют делать ничего другого кроме как, действовать как первичные ключи: они не должны изменяться.) Когда вы изменяете номер продавца, вы хотите, чтобы были сохранены все его заказчики. Однако, если этот продавец покидает свою фирму или компанию, вы можете не захотеть удалить его заказчиков, при удалении его самого из базы данных. Взамен, вы захотите убедиться, что заказчики назначены кому-нибудь еще. Чтобы сделать это вы должны указать UPDATE с Каскадируемым эффектом, и DELETE с Ограниченным эффектом.

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

city char(10),

rating integer,

snum integer REFERENCES Salespeople,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople RESTRICTED);

Если вы теперь попробуете удалить Peel из таблицы Продавцов, команда будет не допустима, пока вы не измените значение пол snum заказчиков Hoffman и Clemens для другого назначенного продавца. С другой стороны, вы можете изменить значение пол snum для Peel на 1009, и Hoffman и Clemens будут также автоматически изменены.

Третий эффект - Пустые (NULL) изменения. Бывает, что когда продавцы оставляют компанию, их текущие порядки не передаются другому продавцу. С другой стороны, вы хотите отменить все порядки автоматически для заказчиков, чьи счета вы удалите. Изменив номера продавца или заказчика можно просто передать их ему. Пример ниже показывает, как вы можете создать таблицу Порядков с использованием этих эффектов.

CREATE TABLE Orders

(onum integer NOT NULL PRIMARY KEY,

amt decimal,

odate date NOT NULL

cnum integer NOT NULL REFERENCES Customers

snum integer REFERENCES Salespeople,

UPDATE OF Customers CASCADES,

DELETE OF Customers CASCADES,

UPDATE OF Salespeople CASCADES,

DELETE OF Salespeople NULLS);

Конечно, в команде DELETE с эффектом Пустого изменения в таблице Продавцов, ограничение NOT NULL должно быть удалено из пол snum.

Внешние ключи, которые ссылаются обратно к их подчиненным таблицам. Как было упомянуто ранее, ограничение FOREIGN KEY может представить им этой частной таблице, как таблицы родительского ключа. Далеко не будучи простой, эта особенность может пригодиться. Предположим, что мы имеем таблицу Employees с полем manager (администратор). Это поле содержит номера каждого из служащих, некоторые из которых являются еще и администраторами. Но так как каждый администратор - в то же врем остается служащим, то он естественно будут также представлен в этой таблице. Давайте создадим таблицу, где номер служащего (столбец с именем empno), объявляется как первичный ключ, а администратор, как внешний ключ, будет ссылаться на нее:

CREATE TABLE Employees

(empno integer NOT NULL PRIMARY KEY,

name char(10) NOT NULL UNIOUE,

manager integer REFERENCES Employees);

(Так как внешний ключ это ссылаемый первичный ключ таблицы, список столбцов может быть исключен.) Имеется содержание этой таблицы:

EMPNO NAME MANAGER

_____ ________ _______

1003 Terrence 2007

2007 Atali NULL

1688 McKenna 1003

2002 Collier 2007

Как вы можете видеть, каждый из них (но не Atali), ссылается на другого служащего в таблице как на своего администратора. Atali, имеющий наивысший номер в таблице, должен иметь значение, установленное в NULL. Это дает другой принцип справочной целостности. Внешний ключ, который ссылается обратно к частной таблице, должен позволять значения = NULL. Если это не так, как бы вы могли вставить первую строку? Даже если эта первая строка ссылается к себе самой, значение родительского ключа должно уже быть установлено, когда вводится значение внешнего ключа. Этот принцип будет верен, даже если внешний ключ ссылается обратно к частной таблице не напрямую, а с помощью ссылки к другой таблице, которая затем ссылается обратно к таблице внешнего ключа. Например, предположим, что наша таблица Продавцов имеет дополнительное поле, которое ссылается на таблицу Заказчиков, так, что каждая таблица ссылается на другую, как показано в следующем операторе CREATE TABLE:

CREATE TABLE Salespeople

(snum integer NOT NULL PRIMARY KEY,

sname char(10) NOT NULL,

city char(10),

comm declmal,

cnum integer REFERENCES Customers);

CREATE TABLE Customers

(cnum integer NOT NULL PRIMARY KEY,

cname char(10) NOT NULL,

city char(10),

rating integer,

snum integer REFERENCES Salespeople);

Это называется - перекрестной ссылкой. SQL поддерживает это теоретически, но практически это может составить проблему. Люба таблица из этих двух, созданная первой является ссылочной таблицей, которая еще не существует для другой. В интересах обеспечения перекрестной ссылки, SQL фактически позволяет это, но никакая таблица не будет пригодна для использования пока они обе находятся в процессе создания. С другой стороны, если эти две таблицы создаются различными пользователями, проблема становится еще более трудной. Перекрестна ссылка может стать полезным инструментом, но она не без неоднозначности и опасностей. Предшествующий пример, например, не совсем пригоден для использования: потому что он ограничивает продавца одиночным заказчиком, и кроме того совсем необязательно использовать перекрестную ссылку чтобы достичь этого. Мы рекомендуем чтобы вы были осторожны в его использовании и анализировали, как ваши программы управ- лют эффектами модификации и удаления, а также процессами привилегий и диалоговой обработки запросов перед тем как вы создаете перекрестную систему справочной целостности.



4. Управление доступом к базам MySQL

4.1 Добавление новых пользователей в MySQL

Пользователей можно добавлять двумя различными способами - при помощи команды GRANT или напрямую в таблицы назначения привилегий MySQL. Предпочтительнее использовать команду GRANT - этот способ проще и дает меньше ошибок.

Существует также большое количество программ (таких как phpmyadmin), которые служат для создания и администрирования пользователей.

В приведенных ниже примерах демонстрируется, как использовать клиент mysql для задания новых пользователей. В примерах предполагается, что привилегии установлены в соответствии с принятыми по умолчанию значениями, описанными в предыдущем разделе. Это означает, что для внесения изменений на том же компьютере, где запущен mysqld, необходимо подсоединиться к серверу как пользователь MySQL root, и у пользователя root должна быть привилегия INSERT для базы данных mysql, а также административная привилегия RELOAD. Кроме того, если был изменен пароль пользователя root, его необходимо указать здесь для команды mysql.

Новых пользователей можно добавлять, используя команду GRANT:

shell> mysql --user=root mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost

-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO monty" %"

-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

mysql> GRANT USAGE ON *.* TO dummy@localhost;

Эти команды GRANT создают трех новых пользователей:

· monty

Полноценный суперпользователь - он может подсоединяться к серверу откуда угодно, но должен использовать для этого пароль some_pass. Обратите внимание на то, что мы должны применить операторы GRANT как для monty@localhost, так и для monty@" %". Если не добавить запись с localhost, запись анонимного пользователя для localhost, которая создается при помощи mysql_install_db, будет иметь преимущество при подсоединении с локального компьютера, так как в ней указано более определенное значение для поля Host, и она расположена раньше в таблице user.

· admin

Пользователь, который может подсоединяться с localhost без пароля; ему назначены административные привилегии RELOAD и PROCESS. Эти привилегии позволяют пользователю запускать команды mysqladmin reload, mysqladmin refresh и mysqladmin flush-*, а также mysqladmin processlist. Ему не назначено никаких привилегий, относящихся к базам данных (их можно назначить позже, дополнительно применив оператор GRANT).

· dummy

Пользователь, который может подсоединяться к серверу без пароля, но только с локального компьютера. Все глобальные привилегии установлены в значение 'N'-тип привилегии USAGE, который позволяет создавать пользователей без привилегий. Предполагается, что относящиеся к базам данных привилегии будут назначены позже.

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

shell> mysql --user=root mysql

mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES(' %','monty',PASSWORD('some_pass'),

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin',

-> Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password)

-> VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

В зависимости от версии MySQL в примере, приведенном выше, может указываться различное количество значений 'Y' (в версиях до Version 3.22.11 было меньше столбцов привилегий). Для пользователя admin используется более удобочитаемый расширенный синтаксис команды INSERT, который доступен начиная с версии 3.22.11.

...