Расширения стандартного SQL в MySQL

Мы стараемся, чтобы MySQL в основном следовал требованиям стандартов ANSI SQL и ODBC SQL, но в приведенных ниже случаях некоторые операции MySQL выпол­няет иначе:

1. В столбцах типа VARCHAR завершающие пробелы удаляются при сохранении зна­ чения (см. раздел Известные ошибки и недостатки дизайна MySQL).
2. В некоторых случаях столбцы типа CHAR скрыто преобразуются в VARCHAR, когда определяется либо изменяется структура таблицы.
3. Привилегии для таблицы при удалении таблицы автоматически не удаляются. Для этого необходимо явно вызвать оператор REVOKE.

Подзапросы

MySQL 4.1 поддерживает подзапросы и вторичные таблицы. Подзапрос - это опера­тор SELECT, вложенный в другой оператор. Вторичная таблица (неименованное пред­ставление) - это подзапрос в конструкции FROM другого оператора. Для более старых версий MySQL большинство подзапросов могут быть переписаны в виде объединений или с использованием других методов.

Оператор SELECT INTO TABLE

В сервере MySQL не реализована поддержка следующего расширения SQL от Sybase: SELECT... INTO TABLE... Вместо этого MySQL поддерживает стандартный SQL-синтаксис INSERT into ... select ..., который в основном делает то же самое. INSERT INTO tbl_temp2 (fldjLd) SELECT tbl_templ.fld_order_id FROM tbl_templ WHERE tbl_templ.fld_order__id ; 100; В качестве альтернативы можно воспользоваться SELECT INTO OUTFILE... или CREATE TABLE SELECT... Начиная с версии 5.0, MySQL поддерживает SELECT... INTO с пользовательскими пе­ременными.

Транзакции и атомарные операции

Сервер MySQL (старшие выпуски версий 3.23 и все версии, начиная с 4.0) поддержи­вает транзакции в механизмах хранения InnoDB и BDB. InnoDB обеспечивает полную со­вместимость с ACID. Остальные нетранзакционные механизмы хранения MySQL (такие, как MyISAM) сле­дуют различным парадигмам обеспечения целостности данных, которые называются ;атомарными операциями;. В терминологии транзакций таблицы MyISAM всегда работа­ют в режиме AUTOCOMMIT=1. Атомарные операции часто предлагают сопоставимую цело­стность с более высокой производительностью. Поскольку сервер MySQL поддерживает обе парадигмы, вы сами решаете, будут ли ваши приложения лучше работать со скоростью атомарных операций или с использова­нием средств управления транзакциями. Этот выбор осуществляется на уровне таблиц. Как упоминалось ранее, различия в работе между транзакционными и нетранзакци-онными таблицами отражаются в основном на производительности. Транзакционные таблицы требуют значительно больших затрат памяти, дискового пространства и на­грузки на центральный процессор. С другой стороны, транзакционные таблицы, подоб­ные InnoDB, также предлагают много существенных дополнительных возможностей. Модульная архитектура сервера MySQL допускает одновременное использование раз­ных механизмов хранения для удовлетворения различным требованиям и достижения оптимальной производительности во всех ситуациях. Но как использовать средства сервера MySQL для поддержки строгих требований целостности данных даже на нетранзакционных таблицах MyISAM, и как эти средства сравнить с работой с транзакционными таблицами?

1. Если ваше приложение написано таким образом, что оно зависит от возможности вызывать ROLLBACK вместо COMMIT в критических ситуациях, транзакции более удобны. Транзакции также гарантируют, что незавершенные обновления или ре­ зультаты сбоев не будут записаны в базу данных. Сервер имеет возможность вы­ полнить автоматический откат и сохранить базу данных. Если же вы применяете нетранзакционные таблицы, сервер MySQL почти во всех случаях предоставляет вам возможность разрешить потенциальные проблемы, включив простые провер­ ки перед обновлением или, запуская простые сценарии, которые проверяют базу данных на непротиворечивость и автоматически вносят исправления либо выдают предупреждения, если обнаружены какие-то противоречия. Стоит отметить, что даже просто включая протоколирование работы MySQL или добавляя дополни­ тельный протокол, вы можете нормально исправить таблицы без потери целост­ ности.
2. В большинстве случаев критические транзакционные обновления могут быть пе реписаны как атомарные операции. Вообще говоря, все проблемы целостности, которые решают транзакции, могут быть предотвращены блокировкой таблиц LOCK table или атомарными обновлениями, гарантирующими, что вы никогда не будете автоматически прерваны сервером, что является общей проблемой тран закционных систем управления базами данных.
3. Даже транзакционные системы могут терять данные, если сервер отключается. Разница между системами состоит только в том, насколько мал промежуток вре мени, в течение которого возможна потеря данных. Нет систем, безопасных на 100%, а есть только ;достаточно безопасные;. Даже СУБД Oracle, имеющая репу тацию наиболее безопасной из транзакционных систем, периодически сообщает об утере данных в ситуациях подобного рода

.

Для безопасной работы с сервером MySQL, независимо от того используются или нет транзакционные таблицы, нужно иметь резервные копии и держать включен­ным бинарное протоколирование. В этом случае вы сможете восстановить данные после любой ситуации, в которую можно попасть, имея дело с другими система­ми. Вообще говоря, располагать актуальными резервными копиями полезно при работе с любой СУБД. Транзакционная парадигма обладает своими преимуществами и недостатками. Мно­гие пользователи и разработчики приложений зависят от того, насколько просто можно написать код, моделирующий систему, для которой прерывание работы возможно или необходимо. Однако, даже если вы новичок в парадигме атомарных операций, либо лучше знакомы с транзакционной моделью, согласитесь, что выигрыш в производитель­ности в 3-5 раз, который дает применение нетранзакционных таблиц по сравнению с наиболее быстрыми и оптимизированными транзакционными, весьма существенен. В ситуациях, когда целостность данных чрезвычайно важна, сервер MySQL демонст­рирует надежность уровня транзакционных систем даже при работе с нетранзакционны-ми таблицами. Если выполняется блокировка таблицы командой LOCK TABLE, все обнов­ления приостанавливаются до тех пор, пока не выполнятся все проверки целостности. Если применяется блокировка READ LOCAL (в отличие от блокировки записи) для табли­цы, допускающей параллельную вставку в конец, чтение разрешено, равно как и вставка другими клиентами. Вновь добавленные записи не будут видимы клиентом, установив­шим блокировку чтения, до тех пор, пока он не снимет блокировку. Применяя INSERT DELAYED, вы можете вставлять записи в локальную очередь до тех пор, пока не будет снята блокировка, не заставляя клиента ожидать завершения операции вставки. Слово ;атомарный; в том смысле, в каком мы его понимаем, не несет в себе ничего сверхъестественного. Оно означает только то, что вы можете быть уверены, что пока специфическое обновление идет, никакой другой пользователь не может взаимодейство­вать с ним, и поэтому не будет никакого автоматического отката (что может случиться с транзакционными таблицами, если вы не проявите достаточную осторожность). Сервер MySQL также гарантирует, что не будет никаких недействительных результатов чтения (dirty read). Ниже перечислены некоторые приемы работы с нетранзакционными таблицами.

1. Циклы, которые нуждаются в транзакциях, обычно могут быть закодированы с помощью LOCK TABLES; необходимости иметь дело с курсорами для обновления записей на лету нет.
2. Для того чтобы избежать использования ROLLBACK, можно прибегнуть к следую­ щей стратегии:

1. Используйте LOCK TABLES для блокировки всех таблиц, к которым нужен доступ.
2. Проверяйте все условия, которые должны быть истинными до начала обнов­ лений.
3. Выполняйте обновления только если все в порядке.
4. Используйте UNLOCK TABLES для разблокирования таблиц.

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

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

1. Модифицировать столбцы в соответствии с их текущими значениями.
2. Обновлять только те столбцы, которые изменились.

Например, когда выполняется обновление информации о заказчиках, мы обновля­ем только те данные, что изменились, либо данные, зависящие от измененных данных, сравнив их новые значения с исходными. Проверка измененных данных делается в конструкции WHERE оператора UPDATE. Если в результате запись не из­менилась, потребуется выдать клиенту сообщение наподобие: ;Некоторые из из­меняемых вами данных изменены другим пользователем;. Затем следует отобра­зить старую и новую версии записи о клиенте, чтобы пользователь решил, какую из них принять. Это обеспечивает механизм, подобный блокировке столбца, но на самом деле да­же лучше, потому что мы обновляем только некоторые из столбцов, используя новые значения, зависящие от их текущих значений. Это означает, что типовые операторы UPDATE должны выглядеть, как показано ниже: UPDATE tablename SET pay_back=pay_back+125; UPDATE customer SET customer_date='current_date', address='new address1, phone='new phone', money_owed_to us=money owed to us-125 WHERE customer__id=id AND address='old address1 AND phone='old phone'; Такой подход весьма эффективен и работает, даже если другой клиент изменяет значения столбцов pay__back и money_owed_to__us.

Во многих случаях пользователи хотят применять LOCK TABLES и/или ROLLBACK для целей управления уникальными идентификаторами. Это также можно обра­ ботать гораздо более эффективно без блокировок и откатов, используя столбцы AUTO__INCREMENT И либо SQL-функцию LAST_INSERT__ID(), либо функцию С API С именем mysql_insert_id (). Обычно можно написать код, обслуживающий ситуации, когда требуется блоки­ровка уровня строки. В некоторых ситуациях это действительно необходимо, и таблицы InnoDB поддерживают такие блокировки. Для таблиц MylSAM можно вос­пользоваться столбцами-флагами и поступить примерно так: UPDATE имя_таблицьг SET row__flag = 1 WHERE id = ID; MySQL вернет 1 в качестве количества обновленных записей, если строка найде­на и row__f lag не был равен 1 в исходной строке. Можете думать об этом, как если бы сервер MySQL изменил предыдущий запрос на такой: UPDATE имя_таблицыSET row_flag = 1 WHERE id = ID AND row_flag; 1;

Хранимые процедуры и триггеры

Хранимые процедуры реализованы в MySQL 5.O. Триггеры запланированы к реализации в версии 5.1. Триггер - это разновидность хра­нимой процедуры, которая вызывается при наступлении какого-то события. Например, можно написать процедуру, которая отрабатывает каждый раз, когда запись удаляется из транзакционной таблицы, и эта процедура автоматически удаляет соответствующего за­казчика из таблицы заказчиков, когда все его финансовые транзакции удалены.

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

В сервере MySQL 3.23.44 и последующих версий механизм хранения InnoDB поддер­живает проверку ограничений на внешние ключи, включая CASCADING, ON DELETE и ON UPDATE. Для других механизмов хранения MySQL анализирует синтаксис FOREIGN KEY в опе­раторе CREATE TABLE, но не использует и не хранит его. В будущих реализациях плани­руется сохранять эту информацию в файле спецификаций таблиц, чтобы она могла быть извлечена с помощью mysqldump и через ODBC. На более поздней стадии ограничения внешних ключей будут реализованы и для таблиц Myl SAM. Применение внешних ключей дает разработчикам баз данных некоторые преимущества:

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

Однако следует помнить, что эти выгоды достигаются за счет большей нагрузки на сервер баз данных, которому приходится выполнять все проверки. Это приводит к неко­торому снижению производительности, что для ряда приложений может оказаться на­столько нежелательным, что лучше обойтись без этого вообще. (Некоторые важные коммерческие программы по этой причине имеют встроенную проверку логики внешних ключей на уровне приложения.) MySQL дает возможность разработчикам выбирать требуемый подход. Если вам не нужны внешние ключи, и вы хотите избежать лишней нагрузки, связанной с проверками ссылочной целостности, вы можете выбрать другой тип таблиц, такой как MyI SAM. На­пример, механизм хранения MyISAM обеспечивает очень высокую производительность для приложений, которые выполняют только операции INSERT и SELECT, поскольку вставки могут выполняться одновременно с выборками. Если вы предпочитаете обойтись без преимуществ проверки ссылочной целостности, вам следует иметь в виду следующее:

• При отсутствии проверки отношений внешних ключей со стороны сервера этим должно заниматься само приложение.

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

1. Если приложению требуется только ссылочная целостность типа ON DELETE, сле­ дует отметить, что в MySQL 4.0 имеется возможность выполнять многотабличные операции DELETE для удаления записей из многих таблиц одним оператором.
2. Обходной путь, позволяющий компенсировать отсутствие ON DELETE, заключается в том, чтобы добавить соответствующие дополнительные операторы DELETE в приложение для удаления записей из таблиц, имеющих внешние ключи. На прак­ тике часто это работает так же быстро, как и автоматические внешние ключи, но при этом значительно более переносимо.

Не забывайте, что применение внешних ключей иногда порождает проблемы:

1. Поддержка внешних ключей применима ко многим случаям, когда нужно обеспе­ чить ссылочную целостность, но это не отменяет необходимости очень тщатель­ ного проектирования отношения ключей, чтобы избегать циклических зависимо­ стей или некорректных комбинаций каскадного удаления.
2. Не столь уж необычна ситуация, когда администратор баз данных создает такую топологию отношений между таблицами, которая затрудняет восстановление от­ дельных таблиц из резервной копии. (MySQL смягчает сложность ситуаций по­ добного рода, позволяя временно отключать проверки внешних ключей на время загрузки данных в таблицы, зависящие от других таблиц. В MySQL 4.1.1 утилита mysqldump генерирует файлы дампа, которые это делают автоматически при за­ грузке.)

Следует помнить, что внешние ключи в SQL применяются для проверки и поддержа­ния ссылочной целостности, но не для объединения таблиц. Если вам нужен результат запроса к множеству таблиц от одного оператора SELECT, вы делаете это за счет описа­ния объединения между ними: SELECT * FROM tl, t2 WHERE tl.id = t2.id; Синтаксис FOREIGN KEY без ON DELETE... часто применяется в ODBC-приложениях для автоматической генерации конструкций WHERE.

Представления

Представления в настоящее время реализованы и появятся в версии 5.0 сервера MySQL. Неименованные представления {вторичные таблицы, подзапросы в конструк­ции FROM оператора SELECT) уже реализованы в версии 4.1. Исторически сложилось так, что сервер MySQL больше всего использовался в при­ложениях и Web-системах, где разработчик имел полный доступ к используемой базе данных. Однако ситуация постепенно менялась, и теперь мы обнаружили, что все воз­растающее число пользователей считают представления очень важным и полезным средством. Представления применяются для того, чтобы предоставить пользователям доступ к группе таблиц таким образом, как будто это одна таблица и тем самым ограничить дос­туп к ним. Представления также можно использовать для ограничения доступа к стро­кам таблицы (выделяя, таким образом, подмножество записей). Для ограничения досту­па к столбцам представления не нужны, поскольку сервер MySQL обладает развитой системой привилегий.

>Многие СУБД не разрешают выполнять операции обновления представлений. Вме­сто этого необходимо обновлять отдельные таблицы. При разработке механизма под­держки представлений в MySQL нашей целью было, оставаясь, насколько возможно, в рамках стандарта SQL, достичь полной совместимости с шестым правилом Кодда для реляционных баз данных. Все представления, теоретически обновляемые, должны быть обновляемы на практике.

как начало комментария

В ряде других СУБД '-' используется для обозначения начала комментария. Сервер MySQL в качестве начального символа комментария использует '#'. Также поддержи­ваются комментарии в стиле языка С: /* пример комментария */. Сервер MySQL 3.23.3 и более поздние версии поддерживают комментарии, начи­нающиеся с '—', предполагая, что за комментарием следует пробел (или управляющий символ вроде перевода строки). Требование, касающееся пробелов, предотвращает про­блемы с автоматически сгенерированными SQL-запросами, которые используют что-нибудь наподобие показанного ниже кода, где вместо .'payment! автоматически под­ставляется значение оплаты: UPDATE account SET credit=credit-!payment! Подумайте, что случится, если значение оплаты будет отрицательным, например, -1: UPDATE account SET credit=credit-—l credit—1 - вполне корректное выражение на языке SQL, но если — интерпретиру­ется как начало комментария, то часть выражения после этого фрагмента теряется. В результате получаем оператор, имеющий совершенно другой смысл, чем ожидалось: UPDATE account SET credit=credit Он не выполняет никакого обновления вообще! Это иллюстрирует то обстоятельст­во, что разрешение использовать в качестве начала комментария '--' чревато довольно-таки неприятными последствиями. Используя реализацию метода задания комментариев, поддерживаемую в MySQL 3.23.3 и последующих версиях, получаем безопасное выражение credit—1. Другое безопасная возможность состоит в том, что интерпретатор командной строки mysql удаляет строки, начинающиеся с '-'. Приведенная далее информация справедлива, только если вы работаете с MySQL версий, предшествующих 3.23.3. Если у вас есть SQL-программа в текстовом файле, которая включает комментарии, начинающиеся с '-', вам придется с помощью утилита replace заменить их на коммен­тарии с символом '#': shell; replace ; —; ; #; text-file-with-funny-comments.sql | mysql имя_базы_данных вместо обычного: shell; mysql имя_базы__данных text-file-with-funny-comments.sql Можно также отредактировать командный файл на месте, заменив '--' на '#': shell; replace ; —; ; #; — text-file-with-funny-comments.sql Вернуться к исходному варианту можно следующим образом: shell; replace ; #; ; —; — text-file-with-funny-comments.sql