Отличия MySQL от ANSI SQL92
Наши усилия направлены на то, чтобы сервер MySQL соответствовал стандартам ANSI SQL и ODBC SQL, но в некоторых случаях сервер MySQL функционирует по-другому. Ниже приведен перечень таких отличий:
Для столбцов VARCHAR при хранении величины концевые пробелы удаляются.
В некоторых случаях столбцы CHAR без уведомления изменяются на столбцы VARCHAR.
Привилегии для таблицы не аннулируются автоматически при удалении таблицы; чтобы удалить привилегии для таблицы, необходимо явно вызвать REVOKE (см. раздел Синтаксис команд GRANT и REVOKE).
NULL AND FALSE будет трактоваться как NULL, а не как FALSE. Причина здесь в том, что мы не считаем необходимым оценивать множество дополнительных условий для этого случая.
Если вас интересует, когда к серверу MySQL будут добавляться новые расширения, необходимо обратиться к онлайновому списку перспективных задач
к выполнению, в котором дан их перечень в порядке приоритетности. Он находится по адресу http://www.mysql.com/doc/en/TODO.html. Это самая последняя версия списка задач к выполнению (TODO list) в данном руководстве (см. раздел MySQL и будущее (что предстоит сделать)).
Вложенные SELECTы
В сервер MySQL поддерживает вложенные запросы вида INSERT ... SELECT ... и REPLACE ... SELECT .... В других контекстах можно использоватьи функцию IN().
Вложенные операции выборки реализованы в версии 4.1.
Между тем, во многих случаях можно переписать запрос, чтобы не использовать вложенную выборку. Например, запрос:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
можно переписать следующим образом:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
Запросы:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
эквивалентны следующему:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
Для более сложных подзапросов часто можно создать временные таблицы, содержащие данный подзапрос. Иногда, однако, этот способ не годится, чаще всего для команд DELETE, для которых в стандарте SQL не поддерживаются объединения (за исключением вложенных выборок). В этой ситуации возможны два временных (пока вложенные запросы не поддерживаются сервером MySQL) варианта решения проблемы.
Первый вариант следующий: при помощи какого-либо процедурно-ориентированного языка программирования (такого как Perl или PHP) делается запрос SELECT для получения первичных ключей тех записей, которые должны быть удалены, а затем полученные величины используются для
составления команды DELETE (DELETE FROM ... WHERE ... IN (key1, key2, ...)).
Второй вариант предполагает применение диалогового SQL для автоматического создания набора команд DELETE с использованием расширения MySQL CONCAT() (вместо стандартного оператора ||). Например:
SELECT CONCAT("DELETE FROM tab1 WHERE pkid = ", """, tab1.pkid, """, ";")
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
Можно поместить этот запрос в файл скрипта, перенаправить стандартный вход клиента командной строки с этого файла, а стандартный выход - на еще один экземпляр клиента командной строки:
shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
Сервер версии MySQL 4.0 поддерживает многотабличные удаления - эту функцию можно использовать для эффективного удаления строк как из одной таблицы,так и из нескольких одновременно
Оператор SELECT INTO TABLE
Для сервера MySQL пока не реализована поддержка расширения Oracle SQL: SELECT ... INTO TABLE .... Вместо этого сервер MySQL поддерживает синтаксис ANSI SQL INSERT INTO ... SELECT ..., который, по существу, представляет собой то же самое (см. раздел Синтаксис оператора INSERT ... SELECT).
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
Можно также использовать выражения SELECT INTO OUTFILE... или CREATETABLE ... SELECT.
Транзакции и атомарные операции
Поддержка транзакций в сервере MySQL реализуется при помощи обработчиков транзакционных таблиц типов InnoDB и BDB (см. раздел Типы таблиц MySQL). Таблицы InnoDB обеспечивают соответствие требованиям ACID.
Однако для таблиц нетранзакционных типов, таких как MyISAM, в MySQL используется иная парадигма обеспечения целостности данных, получившая название "атомарные операции". Атомарные операции в сравнении с транзакциями часто обеспечивают такую же или даже лучшую целостность при более высокой производительности. Поскольку сервер MySQL поддерживает обе парадигмы, пользователь может выбирать между скоростью, которую обеспечивают атомарные операции, и транзакционными возможностями для своих приложений. Такой выбор может быть сделан для каждой таблицы отдельно.
Рассмотрим, как используются возможности сервера MySQL для обеспечения строгой целостности и каковы эти возможности в сравнении с транзакционной парадигмой.
Транзакционная парадигма обеспечивает следующие возможности: если приложения написаны таким образом, что в критических ситуациях зависят
от вызова ROLLBACK вместо COMMIT, то транзакции предпочтительней атомарных операций. Транзакции также обеспечивают гарантию того, что незаконченные обновления или искаженные действия не будут фиксироваться в базе данных; серверу предоставляется возможность
выполнить автоматический откат, и база данных будет сохранена. Почти во всех случаях при работе с сервером MySQL решить возможные проблемы можно путем включения простых проверок перед обновлениями и запуска простых скриптов, которые выполняют проверку баз данных на нарушение целостности с автоматическим исправлением повреждений или выдачей предупреждения, если такое нарушение возникает. Отметим, что полноценное выявление и устранение ошибок в таблицах без потери целостности данных можно обеспечить, просто используя системный журнал MySQL или добавив еще один дополнительный журнал.
Во многих случаях транзакционные обновления можно переписать как атомарные. В общем случае все проблемы, которые решаются с помощью транзакций, можно решить с помощью LOCK TABLES или атомарных UPDATE, при гарантии того, что в базе данных никогда не произойдет автоматического прерывания (что является часто встречающейся проблемой для транзакционных баз данных).
Даже в транзакционной системе возможна потеря данных в случае внезапной остановки сервера (если сервер "упадет"). Разница между различными системами состоит только в том, насколько мал промежуток времени, в течение которого данные могут быть потеряны. Ни одна система не является надежной на 100%, только "достаточно надежной". Даже для сервера Oracle (эта база данных считается наиболее надежной транзакционной базой данных), по сообщениям, в подобных ситуациях иногда возможна потеря данных. Что же касается использования сервера MySQL, то в любом случае, независимо от того, применяются или нет транзакционные таблицы, для обеспечения безопасности необходимо только иметь резервные копии и включенную регистрацию обновлений. Благодаря этим мерам в MySQL, так же как и в других транзакционных базах данных, можно восстановить информацию в любой ситуации. Резервные копии вообще хорошо иметь всегда, независимо от того, какая база данных используется.
Транзакционная парадигма имеет свои достоинства и свои недостатки. Для многих пользователей и разработчиков приложений решающее значение имеет
простота кодирования в проблемных ситуациях, в которых может произойти или неизбежно аварийное прерывание. Однако даже если парадигма атомарных
операций для вас нова или вы привыкли к транзакциям, все же следует принимать во внимание выигрыш в скорости, который могут обеспечить нетранзакционные таблицы (порядка от трех до пяти раз по сравнению со скоростью наиболее быстрых и оптимально настроенных транзакционных таблиц).
В ситуациях, где целостность данных чрезвычайно важна, сервер MySQL обеспечивает даже для нетранзакционных таблиц надежность и целостность данных уровня транзакций или лучше. При блокировании таблиц с помощью LOCK TABLES все обновления останавливаются до тех пор, пока не будут выполнены все проверки на целостность. При наличии только блокировки чтения (в противоположность блокировке записи) операции чтения и вставки, тем не менее, производятся. Новые внесенные записи не будут видны никому из имеющих блокировку чтения клиентов до освобождения этих блокировок. С помощью INSERT DELAYED вставки становятся в очередь и находятся там до тех пор, пока не будут сняты все блокировки. При этом клиент не вынужден ждать, пока отработает INSERT (см. раздел Синтаксис оператора INSERT DELAYED).
То, что мы подразумеваем под термином "атомарные", не означает ничего сверхъестественного. Имеется в виду лишь следующее: гарантируется, что при выполнении каждого конкретного обновления никакой другой пользователь не может повлиять на него и никогда не произойдет автоматического отката(который возможен на транзакционных таблицах, если не приняты должные меры предосторожности). Сервер MySQL также гарантирует, что не случится грязного чтения (dirty read).
Ниже описаны некоторые технические приемы работы с нетранзакционными таблицами:
Циклы, для которых требуются транзакции, обычно могут кодироваться с помощью LOCK TABLES, причем нет необходимости в казателях при динамическом обновлении записей.
Чтобы избежать применения ROLLBACK, можно использовать следующую стратегию:
Применить LOCK TABLES ... для блокирования всех таблиц, к которым необходим доступ.
Проверить условия.
Обновить, если все в порядке.
Использовать UNLOCK TABLES для освобождения произведенных блокировок.
Обычно этот метод обеспечивает намного более высокую скорость, чем использование транзакций с возможными откатами, хотя и не всегда. Это решение не годится только для одной ситуации - когда кто-либо уничтожает потоки посреди обновления. В этом случае все блокировки будут сняты, но некоторые обновления могут не выполниться.
Для обновления записей в рамках одиночной операции можно также использовать функции. Применяя приведенные ниже технические приемы, вы получите очень эффективное приложение:
Поля модифицируются относительно их текущей величины.
Обновляются только те поля, которые действительно изменились.
Например, при выполнении обновлений информации некоторого заказчика мы обновляем только те данные этого заказчика, которые изменялись, и делаем проверку только на предмет того, модифицировались ли изменяемые данные или зависящие от них по сравнению с исходной строкой. Проверка на то,
изменялись или нет данные, выполняется с помощью выражения WHERE в команде UPDATE. Если данную запись обновить не удалось, то клиент получает сообщение: "Некоторые данные, которые вы изменяли, были модифицированы другим пользователем". После этого в окне выводится старая версия, чтобы пользователь мог решить, какую версию записи заказчика он должен использовать. Такой алгоритм обеспечивает нечто похожее на блокирование столбцов, но реально он даже лучше, поскольку мы обновляем только часть столбцов, используя величины, соответствующие их текущим значениям. Это означает, что типичные команды UPDATE выглядят примерно как приведенные ниже:
UPDATE tablename SET pay_back=pay_back+"relative change";
UPDATE customer
SET
customer_date="current_date",
address="new address",
phone="new phone",
money_he_owes_us=money_he_owes_us+"new_money"
WHERE
customer_id=id AND address="old address" AND phone="old phone";
Как можно видеть, этот способ очень эффективно и работает, даже если другой клиент изменит величины в столбцах pay_back или money_he_owes_us.
Во многих случаях пользователи хотят применять ROLLBACK и/или LOCK TABLES для управления уникальными идентификаторами для разных таблиц. Того же результата можно добиться намного более эффективно, используя столбец AUTO_INCREMENT и либо SQL-функцию LAST_INSERT_ID(), либо функцию C API mysql_insert_id() (см. раздел mysql_insert_id()). В общем случае можно написать код и для блокирования на уровне строк. Для некоторых ситуаций это действительно необходимо, но таких случаев очень мало. Блокировка на уровне строк поддерживается в таблицах InnoDB. Для типа MyISAM можно использовать флаговые столбцы в таблице и выполнять запросы, подобные следующему:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL возвращает 1 в качестве количества подвергнутых воздействию строк, если данная строка была найдена, а row_flag в исходной строке не был уже равен 1. Это можно себе представить так, как будто сервер MySQL изменяет предшествующий запрос на:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;
Хранимые процедуры и триггеры
Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций.
Триггер представляет собой хранимую процедуру, которая активизируется при наступлении определенного события. Например, можно задать хранимую процедуру, которая срабатывает каждый раз при удалении записи из транзакционной таблицы - таким образом обеспечивается автоматическое удаление соответствующего заказчика из таблицы заказчиков, когда все его транзакции удаляются.
Возможность работы с хранимыми процедурами будет обеспечивать планируемый язык обновлений. Наша цель - ввести хранимые процедуры приблизительно в версию сервера MySQL 5.0. Мы работаем также и над триггерами.
Внешние ключи
Следует учитывать, что в SQL внешние ключи используются не для объединения таблиц, а главным образом для проверки целостности ссылочных данных(ограничения внешних ключей). Если необходимо получить результаты из большого количества таблиц от команды SELECT, следует делать это через объединение таблиц:
SELECT * FROM table1,table2 WHERE table1.id = table2.id;
См. разделы Синтаксис оператора JOIN и Использование внешних ключей.
В версии сервера MySQL 3.23.44 и выше таблицы InnoDB поддерживают проверку ограничений внешних ключей (см. раздел Таблицы InnoDB). Для таблиц других типов сервер MySQL производит анализ синтаксиса FOREIGN KEY в командах CREATE TABLE, но без выполнения дальнейших действий.
Синтаксис FOREIGN KEY без ON DELETE ... главным образом применяется для целей документирования. В некоторых ODBC-приложениях его можно использовать для автоматического создания выражений WHERE, но обычно это легко сделать вручную. FOREIGN KEY иногда используется в качестве проверки ограничений, но на практике такая проверка не является необходимой, если строки вносятся в таблицу в правильном порядке.
В сервере MySQL можно обойти проблему отсутствия реализации ON DELETE ... добавлением соответствующей команды DELETE в приложение, когда удаляются записи из таблицы, имеющей внешний ключ. На практике при этом достигается почти такая же скорость (в некоторых случаях еще быстрее), как и при использование внешних ключей, и намного большая переносимость.
В версии сервера MySQL 4.0 можно использовать многотабличное удаление, чтобы удалить строки из многих таблиц одной командой (см. раздел Синтаксис оператора DELETE).
В ближайшем будущем мы расширим реализацию FOREIGN KEY таким образом, что информация будет сохраняться в специальном файле таблицы и ее можно будет извлечь с помощью mysqldump и ODBC. На следующем этапе мы внедрим ограничения внешних ключей для приложений, в которых не так просто обойтись без них.
Следует иметь в виду, что внешние ключи часто применяются неправильно, что может вызывать большие проблемы. Даже если они использованы соответствующим образом, то не являются магическим решением для проблемы целостности ссылочных данных, хотя в некоторых случаях действительно
упрощают ситуацию.
Некоторые преимущества внедрения внешних ключей:
При условии, что связи спроектированы правильно, ограничения внешних ключей сделают более сложным для программиста введение противоречивости в базу данных.
Использование каскадных обновлений и удалений может упростить код клиента.
Должным образом разработанные правила внешних ключей помогают в документировании отношений между таблицами.
Недостатки:
Ошибки, которые легко сделать в проектировании отношений ключей, могут вызывать сложные проблемы: например, зацикленные правила или ложные комбинации каскадных удалений.
Правильно написанное приложение будет само по себе обеспечивать отсутствие нарушения целостности ссылочных данных перед началом работы запроса. Таким образом, дополнительные проверки на уровне базы данных только понизят производительность для такого приложения.
Администраторы баз данных часто создают такую сложную топологию связей, при которой затруднительно, а зачастую и вовсе невозможно получить резервную копию или восстановить единичные таблицы.
Представления
Представления планируется реализовать примерно в версии сервера MySQL 5.0.
Представления полезны в основном для случая, когда требуется предоставлять пользователям доступ к набору связей как к одной таблице (только в режиме чтения). Во многих базах данных SQL не обеспечивается возможность обновлять какие-либо строки в представлении - такие обновления необходимо выполнять в отдельных таблицах.
Поскольку сервер MySQL применяется в основном в приложениях и веб-системах, где разработчик приложения имеет полный контроль над использованием базы данных, большинство из наших пользователей не считают представления достаточно важной функциональной возможностью (по крайней мере, никто не заинтересовался ими настолько, чтобы выразить готовность финансировать реализацию представлений).
Для сервера MySQL нет необходимости в применении представлений для ограничения доступа к столбцам, так как в нем реализована хорошо продуманная система привилегий (см. раздел Общие проблемы безопасности и система привилегий доступа MySQL)
Символы "--" как начало комментария
В некоторых отличных от MySQL базах данных SQL символы "--" используются как начальные символы комментариев. В сервере MySQL символом начала комментариев является "#". Для сервера MySQL можно также использовать стиль комментирования из C: /* this is a comment */ (см. раздел Синтаксис комментариев).
В версии сервера MySQL 3.23.3 и выше поддерживается комментирование с помощью символов "--" - при условии, что за комментарием следует пробел. Это объясняется тем, что данный стиль комментирования вызвал много проблем при автоматической генерации SQL-запросов, в которых присутствовал код, подобный приведенному ниже (величина платежа вставляется в выражение !payment! автоматически):
UPDATE tbl_name SET credit=credit-!payment!
Давайте представим себе, что произойдет в случае, если величина payment окажется отрицательной. Поскольку выражение 1--1 в SQL является допустимым, то просто страшно себе вообразить последствия в случае, если будут разрешены комментарии, начинающиеся с "--",
Использование нашей реализации этого метода комментирования в версии сервера MySQL 3.23.3 и выше - в форме 1-- This is a comment - является действительно безопасным.
Существует еще один безопасный способ решения этой проблемы. Он заключается в том, что клиент командной строки mysql удаляет все строки, начинающиеся с "--". Приведенная ниже информация относится только к работе более ранних, чем 3.23.3, версий MySQL.
Если ваша SQL-программа представлена в виде текстового файла, содержащего комментарии "--", необходимо использовать:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
вместо обычного:
shell> mysql database < text-file-with-funny-comments.sql
Можно также отредактировать сам командный файл, заменив комментарии "--" комментариями "#":
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Привести эти комментарии к первоначальному виду можно с помощью следующей команды:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
1 2 3 4
8 8 8
| |
*
Обратная связь