MySQL очень быстрый, многопоточный, многопользовательский и поддерживающий SQL (Structured Query Language) сервер баз данных.
MySQL является free software. Он лицензируется по GNU GENERAL PUBLIC LICENSE http://www.gnu.org.
Сайт MySQL предоставляет последнюю информацию касательно MySQL.
Следующий перечень описывает наиболее интересные места руководства:
ВАЖНО:
Сообщения об ошибках также как вопросы и комментарии, должны быть посланы
списку рассылки
mysql@lists.mysql.com. Подробности в разделе
"Как сообщать о проблемах и сбоях".
Скрипт mysqlbug должен использоваться, чтобы генерировать отчеты
об ошибках. Для дистрибутивов исходных текстов скрипт mysqlbug
может быть найден в каталоге scripts. Для двоичных дистрибутивов
mysqlbug находится в каталоге bin. Если Вы нашли ошибку
защиты в MySQL, Вы должны послать e-mail на
security@mysql.com.
Если Вы имеете любые предложения относительно добавлений или исправлений этого руководства, пожалуйста, пошлите их на docs@mysql.com.
MySQL представляет собой очень популярную систему управления базами данных с открытыми исходными текстами, разрабатываемую MySQL AB. MySQL AB является коммерческой компанией, строящей свой бизнес на сервисах, сосредоточенных на базе данных MySQL. Подробности в разделе "1.1.2 Что такое MySQL AB".
Официально MySQL произносится как "Май-Эс-Ку-Эль", а не как MY-SEQUEL.
MySQL AB является шведской компанией, которая владеет правами на исходные тексты сервера и марку MySQL. Она занимается разработкой, распространением и поддержкой пакета MySQL.
Авторы ищут партнеров, которые хотели бы поддерживать разработку MySQL так, чтобы они могли бы ускорить темп разработки. Если Вы заинтересованы в этом, напишите на e-mail partner@mysql.com!
MySQL AB имеет в настоящее время свыше 20 разработчиков ( http://www.mysql.com/development/team.html) в платежной ведомости, и это число возрастает быстро.
Основные источники дохода:
Авторы пакета хотят, чтобы MySQL всегда был:
MySQL AB и команда MySQL AB:
Началось все с попыток добавить к mSQL драйвер низкого уровня
для связи с только что разработанным форматом таблиц (ISAM). Однако, после
вдумчивого тестирования, было установлено, что mSQL недостаточно
быстр и гибок для этого дела. Это закончилось созданием нового интерфейса SQL
к нашей базе данных, но почти с тем же самым интерфейсом API, что и у
mSQL. Этот API был выбран, чтобы облегчить перенос кодов для
других разработчиков программ.
Название возникло из сокращения (а вернее, слияния) слов My SQL, что на английском языке значит "мой SQL". Названию около десяти лет, оно прижилось еще в те времена, когда пакет не был коммерческой разработкой.
Следующий перечень описывает наиболее важные возможности MySQL:
FLOAT, DOUBLE, CHAR,
VARCHAR, TEXT, BLOB,
DATE, TIME, DATETIME,
TIMESTAMP, YEAR, SET и
ENUM. Подробности приведены в разделе
"5 Типы столбцов". Там все рассказано.
SELECT и WHERE. Например:
mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
WHERE income/dependents > 10000 AND age > 30;
GROUP BY и ORDER
BY. Поддержка групповых функций (COUNT(),
COUNT(DISTINCT ...), AVG(), STD(),
SUM(), MAX() и MIN()).
LEFT OUTER JOIN и RIGHT OUTER JOIN с
синтаксисами ANSI SQL и ODBC.
CHAR или VARCHAR.
INSERT, чтобы вставить подмножество столбцов таблицы. Те
столбцы, которым явно не заданы значения, будут автоматически установлены к
их значениям по умолчанию.
myisamchk, очень быстрая утилита для проверки таблицы,
оптимизации и ремонта. Все функциональные возможности myisamchk
также доступны через интерфейс SQL.
DELETE, INSERT, REPLACE и
UPDATE возвращают число строк, которые были изменены
(обработаны). Можно взамен вернуть число согласованных строк, устанавливая
флажок при соединении с сервером.
ABS представляет собой имеющее силу имя столбца. Единственное
ограничение: для обращения к функции никакие пробелы не позволяются между
именем функции и символом скобки (`('), который следует за ним.
--help или -? для выдачи справки о параметрах
запуска конкретной программы.
SHOW может использоваться, чтобы
получить информацию относительно баз данных, таблиц и индексов. Команда
EXPLAIN может использоваться, чтобы определить, как именно
оптимизатор решает запрос.Этот раздел сводится к вопросам о том, насколько можно доверять пакету, и сколько шансов, что он разнесет на кусочки важный проект, зависящий от него. Строго говоря, MySQL очень надежен.
Попробую разъяснить некоторые проблемы и ответить на некоторые из наиболее важных вопросов, которые, кажется, касаются многих. Этот раздел был собран из информации, собранной из списка рассылки (который является очень активным по части сообщений об ошибках и сбоях).
В TcX MySQL работал без любых проблем в проектах, начиная с середины 1996. Когда MySQL был выпущен на публику, авторы отметили, что имелись некоторые части непроверенного кода, которые были быстро найдены новыми пользователями, делавшими запросы иными способами, чем авторы. Каждый новый выпуск имел меньшее количество проблем мобильности, чем предыдущий (даже при том, что каждый имел много новых свойств).
Каждый выпуск MySQL был пригоден для использования, и имелись проблемы только, когда пользователи начинали использовать код из серых зон. Естественно, пользователи снаружи не видят то, чем являются серые зоны, этот раздел пытается указать, которые зоны в настоящее время известны. Описания имеют дело с MySQL Version 3.23. Все известные и сообщенные ошибки выправлены в последней версии, за исключением ошибок, перечисленных в отдельном разделе, которые являются проблемами, связанными с проектом. Подробности в разделе "1.2.7 Известные ошибки и проблемы".
MySQL написан на нескольких уровнях и различных независимых модулях. Эти модули перечислены ниже с индикацией относительно того, как хорошо проверен каждый из них (сравните с MS SQL Server!):
mysql, mysqladmin,
mysqlshow, mysqldump и mysqlimport.
fcntl()). В
этих случаях Вы должны выполнить MySQL с опцией --skip-locking.
Проблемы, как известно, происходят на некоторых Linux-системах и на SunOS при
использовании файловых систем по NFS.
fcntl(),
которое исправлено, используя опцию --skip-locking для
mysqld. Некоторые пользователи сообщали о проблемах тупика в
Version 0.5. LinuxThreads должен быть перетранслирован, если Вы планируете
использовать свыше 1000 параллельных подключений. Хотя можно выполнить много
подключений с LinuxThreads по умолчанию (однако, Вы никогда не будете иметь
более, чем 1021 подключение), заданный по умолчанию лимит стека в 2 МБ делает
прикладную программу ненадежной, и она способна свалиться в дамп ядра после
создания 1021 неактивных подключений.
SELECT
обычно выполняются в одном пакете.
LOAD DATA..., INSERT...SELECT:
стабильно.
ALTER TABLE: стабильно.
mysqlaccess: стабильно.
GRANT: стабильно.
MySQL. Они работают хорошо и
могут использоваться после начального тестирования.
MERGE все еще не
оттестировано как следует. Другая часть кода MERGE проверена.
MySQL AB обеспечивает поддержку по электронной почте для покупателей соответствующей услуги, но список рассылки MySQL обычно обеспечивает ответы на общие вопросы. Ошибки обычно исправляются сразу же с помощью патча, для серьезных ошибок почти всегда имеется новый выпуск.
MySQL Version 3.22 имеет лимит в 4G на размер таблицы. С новым кодом
MyISAM в MySQL Version 3.23 максимальный размер таблицы увеличен
до 8 миллионов терабайт (2^63 байт).
Обратите внимание, однако, что операционные системы имеют их собственные ограничения размера файла. Имеются некоторые примеры:
| Операционная система | Ограничение размера файла |
| Linux-Intel 32 bit | 2G, 4G или больше, зависит от версии Linux |
| Linux-Alpha | 8T (?) |
| Solaris 2.5.1 | 2G (возможно, до 4G с патчем) |
| Solaris 2.6 | 4G |
| Solaris 2.7 Intel | 4G |
| Solaris 2.7 ULTRA-SPARC | 8T (?) |
В Linux 2.2 Вы можете получать таблицы больше, чем 2G, используя заплату LFS для файловой системы ext2. В Linux 2.4 существует также заплата для ReiserFS, чтобы получить поддержку для больших файлов.
Это означает, что размер таблицы для MySQL обычно ограничивается операционной системой, а не самим пакетом.
По умолчанию таблицы MySQL имеют максимальный размер около 4G. Вы можете
проверять максимальный размер таблицы для каждой конкретной таблицы с помощью
команды SHOW TABLE STATUS или утилитой myisamchk -dv
table_name. Подробности приведены в разделе
"4.10 Синтаксис вызова SHOW".
Если Вы нуждаетесь в таблицах, больших, чем 4G (и Ваша операционная
система поддерживает это), Вы должны установить параметры
AVG_ROW_LENGTH и MAX_ROWS, когда Вы создаете Вашу
таблицу. Подробности в разделе "7.3
Синтаксис CREATE TABLE". Вы можете установить их и позже с
помощью ALTER TABLE. Подробности в разделе
"7.4 Синтаксис ALTER TABLE
".
Если Ваша большая таблица нужна только для чтения, Вы могли бы
использовать myisampack, чтобы объединить и сжать много таблиц в
одну. Утилита myisampack обычно сжимает таблицу по крайней мере
на 50%, так что Вы можете иметь намного большие таблицы.
Вы можете обойти ограничения размера файла операционной системы для
файлов данных MyISAM, используя опцию RAID.
Подробности в разделе "7.3 Синтаксис
CREATE TABLE".
Другое решение может быть реализовано с помощью библиотеки MERGE, которая позволяет Вам обрабатывать совокупность идентичных таблиц как одну.
MySQL непосредственно не имеет никаких трудностей с проблемой 2000 (Y2K):
2069. Все годы с 2 цифрами расценены в интервале от
1970 до 2069, это означает, что, если Вы сохраняете
01 в столбце типа year, MySQL обрабатывает это как
2001.
YEAR может
сохранять годы 0 и в интервале от 1901 до
2155 в 1 байте, а также отображать их, используя 2 или 4 цифры.
Вы можете сталкиваться с проблемами в прикладных программах, которые
используют MySQL, но сами несовместимы с проблемой Y2K. Например, много
старых прикладных программ сохраняют или управляют значениями лет,
используя числа с 2 цифрами (которые являются неоднозначными). Эта проблема
также может быть составлена прикладными программами, которые используют
00 или 99 как значения для индикатора "пропустить".
В свое время пришлось столкнуться с программой, которая помечала удаленные
записи, выставляя им год 00...
К сожалению, эти проблемы могут быть трудными в исправлении потому, что различные прикладные программы могут быть написаны различными программистами, каждый из которых может использовать различный набор соглашений и обрабатывающих даты функций.
Имеется простой пример, иллюстрирующий, что MySQL не имеет любых проблем с датами до года 2030:
mysql> DROP TABLE IF EXISTS y2k;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE y2k (date date, date_time datetime,
time_stamp timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO y2k VALUES
-> ("1998-12-31","1998-12-31 23:59:59",19981231235959),
-> ("1999-01-01","1999-01-01 00:00:00",19990101000000),
-> ("1999-09-09","1999-09-09 23:59:59",19990909235959),
-> ("2000-01-01","2000-01-01 00:00:00",20000101000000),
-> ("2000-02-28","2000-02-28 00:00:00",20000228000000),
-> ("2000-02-29","2000-02-29 00:00:00",20000229000000),
-> ("2000-03-01","2000-03-01 00:00:00",20000301000000),
-> ("2000-12-31","2000-12-31 23:59:59",20001231235959),
-> ("2001-01-01","2001-01-01 00:00:00",20010101000000),
-> ("2004-12-31","2004-12-31 23:59:59",20041231235959),
-> ("2005-01-01","2005-01-01 00:00:00",20050101000000),
-> ("2030-01-01","2030-01-01 00:00:00",20300101000000),
-> ("2050-01-01","2050-01-01 00:00:00",20500101000000);
Query OK, 13 rows affected (0.01 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM y2k;
+------------+---------------------+----------------+
| date | date_time | time_stamp |
+------------+---------------------+----------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 |
+------------+---------------------+----------------+
13 rows in set (0.00 sec)
Это показывает, что типы DATE и DATETIME не
будут давать никаких проблем с будущими датами (они легко обрабатывают даты
вообще до 9999 года).
Тип TIMESTAMP, который используется, чтобы сохранить текущее
(актуальное) время, имеет диапазон только до 2030-01-01.
TIMESTAMP имеет диапазон от 1970 до
2030 на 32-разрядных машинах (значение со знаком). На
64-разрядных машинах это обрабатывает времена до 2106 года
(значение без знака).
Даже при том, что MySQL Y2K-совместим, Вы отвечаете за то, чтобы обеспечить однозначный ввод. Подробности в разделе "5.2.1 Проблема Y2K и типы Date", там описаны правила MySQL для ввода дат с неоднозначными данными (данные, содержащие значения года с 2 цифрами).
Этот раздел описывает, как MySQL соответствует стандартам ANSI SQL. MySQL имеет много расширений для них, здесь Вы выясните, что они из себя представляют, и как использовать их. Вы также найдете информацию относительно функциональных возможностей, отсутствующих в MySQL, и как обойти проблемы.
MySQL включает некоторые расширения, которые Вы, вероятно, не будете
находить в других базах данных SQL. Предупреждаю, что, если Вы используете
их, Ваш код не будет переносимым на другие SQL-серверы. В некоторых случаях
Вы можете писать код, который включает MySQL-расширения, но все же является
переносимым за счет комментариев формы /*! ... */. В этом случае
MySQL анализирует и выполнит код внутри комментария, но другие SQL-серверы
игнорируют расширения. Например:
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
Если Вы добавляете номер версии после '!', синтаксис будет
выполнен только, если версия MySQL равна или больше, чем этот номер версии:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
Это означает, что, если Вы имеете Version 3.23.02 или более новую, MySQL
использует ключевое слово TEMPORARY.
MySQL-расширения перечислены ниже:
MEDIUMINT, SET,
ENUM и разные типы BLOB и TEXT.
AUTO_INCREMENT, BINARY,
NULL, UNSIGNED и ZEROFILL.
BINARY или использовать ключевое слово BINARY,
которое заставляет сравнения быть выполненными согласно порядку ASCII,
используемому на сервере MySQL.
db_name.tbl_name. Некоторые
SQL-серверы обеспечивают те же самые функциональные возможности, но называют
это User space. MySQL не поддерживает пространство таблиц как
in: create table ralph.my_table...IN my_tablespace.
LIKE позволяется на числовых столбцах.
INTO OUTFILE и STRAIGHT_JOIN
допустимо в инструкции SELECT.
SQL_SMALL_RESULT в инструкции SELECT.
EXPLAIN SELECT, чтобы получить описание
того, как таблицы соединены.
INDEX или KEY в инструкции
CREATE TABLE.
TEMPORARY или IF NOT EXISTS с
CREATE TABLE.
COUNT(DISTINCT list), где 'list' включает больше,
чем один элемент.
CHANGE col_name, DROP col_name или
DROP INDEX, IGNORE или RENAME в вызове
команды ALTER TABLE.
RENAME TABLE.
ADD, ALTER,
DROP или CHANGE в одном
вызове ALTER TABLE.
DROP TABLE с ключевым словом IF EXISTS
.
DROP
TABLE.
LIMIT в инструкции DELETE.
DELAYED в инструкциях INSERT и
REPLACE.
LOW_PRIORITY в инструкциях INSERT,
REPLACE, DELETE и UPDATE.
LOAD DATA
INFILE. Во многих случаях этот синтаксис совместим с Oracle
LOAD DATA INFILE.
ANALYZE TABLE, CHECK TABLE,
OPTIMIZE TABLE и REPAIR TABLE.
SHOW.
SET OPTION.
GROUP BY.
Это дает лучшую эффективность для некоторых очень специфических, но
совершенно нормальных запросов.
ASC и DESC с вызовом
GROUP BY. Очень полезно.
|| и && в качестве
OR и AND, как в языке программирования C. В MySQL || и
OR синонимы, так же как && и AND.
Из-за этого хорошего синтаксиса MySQL не поддерживает оператор ANSI SQL
|| для конкатенации строк, используйте вместо него
CONCAT(). Поскольку CONCAT() берет любое число
параметров, просто преобразовать использование || в MySQL.
CREATE DATABASE или DROP DATABASE.
%
является синонимом для MOD(). То есть N%M
равносильно MOD(N,M). % поддержан для
C-программистов и для совместимости с PostgreSQL.
=, <>, <=,
<, >=,>,
<<, >>, <=>,
AND, OR или LIKE могут использоваться
в сравнениях столбца слева от FROM в инструкции
SELECT. Например, так:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
LAST_INSERT_ID().
REGEXP и
NOT REGEXP в операторах.
CONCAT() или CHAR() с одним
параметром или больше, чем с двумя параметрами. В MySQL эти функции могут
брать любое число параметров.
BIT_COUNT(), CASE, ELT(),
FROM_DAYS(), FORMAT(), IF(),
PASSWORD(), ENCRYPT(), md5(),
ENCODE(), DECODE(), PERIOD_ADD(),
PERIOD_DIFF(), TO_DAYS() и WEEKDAY().
TRIM() для подрезания подстрок. ANSI SQL
поддерживает удаление только одиночных символов.
GROUP BY можно использовать STD(),
BIT_OR() и BIT_AND().
REPLACE вместо связки
DELETE+INSERT.
FLUSH flush_option.
:=:
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table; SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
Авторы пробуют заставить MySQL следовать стандартам ANSI SQL и ODBC SQL, но в некоторых случаях MySQL обрабатывает некоторые дела по-другому:
-- является комментарием, только если сопровождается
незаполненным пространством. Подробности чуть ниже.
VARCHAR конечные пробелы будут удалены, когда
значение сохранено. Подробности в разделе "1.2.7
Известные ошибки и проблемы".
CHAR тихо меняются на столбцы
типа VARCHAR.
REVOKE, чтобы отменить все
привилегии для таблицы.
NULL AND FALSE вернет NULL вместо
FALSE, чтобы не возиться долго с оценкой выражений.Если Вы запустили mysqld с опцией --ansi,
поведение MySQL изменится следующим образом:
|| является конкатенацией строк, а не OR.
REAL превратится в синоним для FLOAT вместо
синонима для DOUBLE.
SERIALIZABLE. Подробности в разделе
"9.2.3 Синтаксис
SET TRANSACTION".Этого также можно достичь опцией --sql-mode=REAL_AS_FLOAT,
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,SERIALIZE,ONLY_FULL_GROUP_BY.
Следующие функциональные возможности отсутствуют в текущей версии MySQL. Есть список, указывающий, когда новые расширения могут быть добавлены к MySQL (с их приоритетами), его можно посмотреть в Интернете по адресу http://www.mysql.com/documentation/manual.php?section=TODO.
MySQL в настоящее время поддерживает sub-selects только в виде
INSERT ... SELECT ... и REPLACE ... SELECT ....
Вы можете, однако, использовать функцию IN() в других контекстах.
Во многих случаях Вы можете переписать запрос без sub-select:
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 не поддерживает объединения (за исключением sub-selects). Для
этой ситуации имеются два решения, доступные пока подзапросы не поддержаны.
Первое должно использовать процедурный язык программирования (типа 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;
Вы можете помещать этот запрос в файл скрипта и переназначать ввод на
интерпретатор командных строк mysql, отправив вывод на его
вторую копию клиента:
prompt> mysql --skip-column-names mydb < myscript.sql|mysql mydb
MySQL 4.0 поддерживает многотабличное удаление, которое может использоваться, чтобы эффективно удалить строки, основанные на информации из одной таблицы (или даже из многих таблиц) в то же самое время.
SELECT INTO TABLEMySQL не поддерживает Oracle SQL-расширение SELECT ... INTO
TABLE .... MySQL вместо него поддерживает синтаксис ANSI SQL
INSERT INTO ... SELECT ..., который является в основном той же
самой функциональностью. Подробности в разделе
"8.3.1 Синтаксис INSERT ... SELECT
".
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1
WHERE tblTemp1.fldOrder_ID > 100;
Альтернативно, Вы можете использовать SELECT INTO OUTFILE...
или CREATE TABLE ... SELECT, чтобы решить Вашу проблему.
Поскольку MySQL в настоящее время поддерживает транзакции, следующее
обсуждение имеет силу, только если Вы используете не транзакционно-безопасные
типы таблицы. Подробности в разделе "9.2.1
Синтаксис BEGIN/COMMIT/ROLLBACK".
Часто спрашивают, почему MySQL не транзационная база данных?
MySQL сделал сознательное решение поддерживать другую парадигму для целостности данных: атомные операции. Дело в том, что атомные операции предлагают равную или даже лучшую целостность с намного лучшей эффективностью. Однако, авторы пакета тем не менее оценивают и понимают транзакционную парадигму базы данных и планируют в следующих версиях представить транзакционно-безопасные таблицы. Пользователям будет предоставлена возможность решить, нуждаются ли они в быстродействии атомных операций, или они должны использовать свойства транзакций в своих программах.
Давайте разберемся в том, как MySQL поддержает строгую целостность, и сравним эти свойства с транзакциями.
Перво-наперво в транзакционной системе, если Ваши программы в критических ситуациях вызывают rollback вместо commit, транзакционная схема удобней. Кроме того, транзакции гарантируют, что незаконченные модификации или разрушительные действия не будут применены к базе данных немедленно, сервер дает возможность сделать автоматическую обратную перемотку, и Ваша база данных будет сохранена.
MySQL почти во всех случаях позволяет Вам обойти проблемы включением простых проверок перед модификациями и запуском простых скриптов, которые проверяют целостность базы данных, а также автоматически проводят ремонт. Обратите внимание, что только используя файл регистрации MySQL или даже добавляя один дополнительный файл регистрации, обычно можно востанавливать таблицы без потери целостности данных.
Кроме того, фатальные модификации в транзакционной схеме могут быть
переделаны так, чтобы стать атомными. Фактически все проблемы целостности,
которые решают транзакции, могут быть выполнены с помощью LOCK
TABLES или атомными модификациями, гарантируя, что Вы никогда не
получите автоматическое аварийное прекращение работы базы данных, что
является общей проблемой для транзакционных баз данных.
Далеко не все транзакции могут предотвращать потерю данных, если сервер рушится. В таких случаях даже транзакционная система может терять данные. Никакая система не 100%-но безопасна, речь идет лишь о минимизации потерь. Даже Oracle, как сообщают, иногда теряет данные в таких ситуациях, хоть и считается самой безопасной из транзакционных баз данных.
Чтобы обеспечить безопасность в MySQL, Вы должны только иметь копии и регистрацию модификаций. С этим Вы можете восстановить фактически любое повреждение базы данных.
Транзакционная парадигма имеет выгоды и недостатки. Много пользователей и
разработчиков прикладных программ зависят от легкости, с которой они могут
обойти проблемы, где аварийное прекращение работы появляется или необходимо,
и им, вероятно, придется делать немного больше работы с MySQL, чтобы думать
по-другому или писать больше. Если Вы плохо знакомы с атомной парадигмой
операций или более знакомы с транзакциями, не считайте, что MySQL не знаком с
этими проблемами. Надежность и целостность у авторов этого пакета стоят на
первом месте! Недавние оценки указывают, что имеется больше, чем 1000000
серверов mysqld, многие из которых находятся в промышленных
средах. Очень редко можно услышать от пользователей, что они потеряли данные,
и почти во всех случаях виноваты были сами пользователи. Это самое лучшее
доказательство стабильности и надежности MySQL.
Наконец, в ситуациях, где целостность имеет самую высокую важность,
текущие свойства MySQL учитывают уровень транзакции или лучшую надежность и
целостность. Если Вы блокируете таблицы с помощью LOCK TABLES,
все модификации остановятся, пока любые проверки целостности не будут
сделаны. Если Вы только получаете блокировку чтения (в противоположность
блокировке записи), то чтение и вставки продолжают работать. Новые
вставленные записи не будут замечены клиентами, имеющими блокировку
READ, пока они не освободят их блокировки чтения. С помощью
INSERT DELAYED Вы можете поместить вставки в локальную очередь,
где они останутся до тех пор, пока блокировки не будут освобождены. Таким
образом, сервер не будет иметь пользователя, который ждет завершения вставки.
Подробности в разделе "8.4 Синтаксис
INSERT DELAYED".
"Атомная" означает, что Вы можете убедиться в том, что в то время как
каждая специфическая модификация выполняется, никакой другой пользователь не
может сталкиваться с ней, и никакой автоматической обратной перемотки не
будет никогда (хотя это может случаться на транзакционных системах, если Вы
не очень осторожны). MySQL также гарантирует, что не будет иметься лишних
чтений. Вы можете найти пример того, как писать атомные модификации в разделе
"1.2.6 Как справиться без
COMMIT/ROLLBACK".
Использование атомной парадигмы позволяет применять много оптимизаций быстродействия, которые иначе не будут возможны. К тому же, при грамотном подходе такая схема ускорит работу в 3-5 раз по сравнению с лучшими транзакционными базами данных при той же надежности.
Для тех случаев, где безопасность более важна, чем быстродействие, я
советую применять транзакционные таблицы типов BDB или
InnoDB для всех критических данных.
Одно заключительное примечание: в настоящее время авторы пакета работают над безопасной схемой репликации, которая должна быть лучше, чем любая известная на сегодняшний день поддержка репликации. Эта система будет работать наиболее надежно при атомных операциях, а не транзакциях.
Хранимая процедура представляет собой набор команд SQL, который может компилироваться и храниться на сервере. Как только это было выполнено, клиент не должен хранить весь запрос, а может обратиться к сохраненной процедуре. Это обеспечивает лучшую эффективность потому, что запрос должен анализироваться только однажды, и меньшее количество информации должно быть послано между клиентом и сервером. Вы можете также поднимать концептуальный уровень при наличии библиотек функций.
Триггер представляет собой сохраненную процедуру, которая вызывается, когда специфическое событие происходит. Например, Вы можете устанавливать сохраненную процедуру, которая будет вызвана каждый раз, когда запись удалена из таблицы transaction. Эта процедура автоматически удаляет соответствующего заказчика из таблицы customer, когда все его транзакции удалены.
Запланированный язык модификаций будет способен обработать сохраненные процедуры, но без триггеров. Триггеры обычно замедляют все, даже запросы, к которым не имеют отношения.
Обратите внимание, что внешние ключи в SQL не используются, чтобы
соединить таблицы, но используются обычно для проверки справочной
целостности. Если Вы хотите получить результат из нескольких таблиц командой
SELECT, Вы делаете это, соединяя таблицы так:
SELECT * from table1,table2 where table1.id = table2.id;
Подробности есть в разделах
"8.1.1 Синтаксис JOIN" и
"2.5.6
Использование внешних ключей".
Синтаксис FOREIGN KEY в MySQL существует только для
совместимости с другими версиями SQL-команды CREATE TABLE, это
не делает ничего. Синтаксис FOREIGN KEY без ON DELETE ...
обычно используется для документационных целей. Некоторые прикладные
программы стандарта ODBC могут использовать это, чтобы произвести
автоматические предложения WHERE, но это обычно просто, чтобы
перекрыть. FOREIGN KEY иногда используется как проверка
ограничения, но эта проверка практически не нужна, если строки вставлены в
таблицы в правильном порядке. MySQL поддерживает эти предложения только
потому, что некоторые прикладные программы требуют, чтобы они существовали
(независимо от того, работают они или нет).
В MySQL Вы можете обойти проблему неработающей конструкции
ON DELETE ... добавляя соответствующую инструкцию
DELETE к прикладной программе, когда Вы удаляете записи из
таблицы, которая имеет внешний ключ. Практически это иногда быстрее и намного
более переносимо, чем использование внешних ключей в таблице.
В ближайшем будущем мы расширим реализацию FOREIGN KEY так,
чтобы по крайней мере информация была сохранена в файле спецификации таблицы
и могла быть получена mysqldump и ODBC. На более поздней стадии
мы выполним ограничения внешних ключей для прикладной программы, которая не
может легко быть перекодирована, чтобы избежать их.
Много ученых по теории базы данных и программистов чувствуют, что справочная целостность должна быть предписана внутри сервера базы данных. Действительно, во многих случаях этот подход очень полезен. Однако, в разговоре со многими пользователями баз данных авторы наблюдали, что внешние ключи часто неправильно используются, что может вызывать серьезные проблемы. Даже когда все используется правильно, это не волшебное решение для проблемы справочной целостности, хотя это делает все проще в некоторых случаях.
Из-за вышеупомянутых наблюдений авторы не назначали реализации внешних ключей высокий приоритет. Однако, в последние годы ядро пользователей расширилось, и теперь авторы пакета имеют много пользователей, кто хотел бы иметь предписанную поддержку справочной целостности внутри MySQL. Так что в ближайшем будущем внешние ключи все-таки будут реализованы.
Некоторые преимущества применения внешних ключей:
Противопоказания:
MySQL не поддерживает views, но это планируется исправить примерно к 4.1.
Views обычно полезны для разрешения пользователям обращаться к набору отношений как к одной таблице (в режиме только для чтения). Многие базы данных SQL не позволяют модифицировать любые строки в таком представлении: Вы должны делать все модификации в отдельных таблицах.
MySQL обычно используется в прикладных программах и на web-системах, где автор прикладной программы имеет полное управление над применением базы данных. По этой причине views не сочтены очень важными.
Чтобы ограничить доступ к столбцам в MySQL views тоже не требуются: MySQL имеет очень сложную систему предоставления привилегий. Подробности в разделе "10 Общие проблемы защиты и система привилегий доступа MySQL".
Некоторые базы данных SQL применяют -- как начало
комментария. MySQL имеет # как символ начала комментария, даже
если инструмент командной строки mysql удаляет все строки,
начинающиеся с --. Вы можете также использовать стиль
комментариев языка C (/* это комментарий */) в MySQL.
MySQL Version 3.23.3 и выше поддерживает стиль комментариев
--, только если комментарий сопровождается пробелом. Это потому,
что стиль комментария вызвал много проблем с автоматически сгенерированными
запросами SQL, которые использовали нечто вроде следующего кода, где мы
автоматически вставляем значение payment вместо !payment!:
UPDATE tbl_name SET credit=credit-!payment!
Как Вы думаете, что случится, когда значение payment
отрицательное? А вот что. Поскольку 1--1 допустимо в SQL, пакет
думает, что начался комментарий типа --. Вряд ли это
входит в Ваши планы...
В MySQL Version 3.23 Вы можете использовать:
1-- Это был комментарий
Следующее обсуждение касается Вас, только если Вы управляете MySQL Version 3.23 или ранее:
Если Вы имеете программу 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
Entry level SQL92. ODBC levels 0-2.
COMMIT/ROLLBACKСледующее обычно применяется только для таблиц ISAM,
MyISAM и HEAP. Если Вы используете только
транзакционно-безопасные таблицы (BDB или InnoDB) в
модификации, Вы можете также делать COMMIT и
ROLLBACK в MySQL. Подробности в разделе
"9.2.1 Синтаксис
BEGIN/COMMIT/ROLLBACK".
Проблема с эффективной обработкой
COMMIT-ROLLBACK с вышеупомянутыми типами таблиц
требует полностью иного размещения таблицы, чем используемое MySQL сегодня.
Тип таблицы также нуждался бы в дополнительных потоках, которые вели бы
автоматические очистки на таблицах, да и использование дисков было бы намного
выше. Это сделало бы эти типы таблицы приблизительно в 2-4 медленнее, чем
они есть сейчас.
Текущей проблемой является ROLLBACK. Без
ROLLBACK Вы можете делать любой вид COMMIT с
помощью LOCK TABLES. Для поддержки ROLLBACK с
вышеупомянутыми типами таблицы MySQL должен быть изменен так, чтобы сохранять
все старые записи, которые модифицировались, и иметь возможность быстро
вернуться к отправной точке, если была выдана команда ROLLBACK.
Для простых случаев это довольно просто (можно приспособить сюда
isamlog), но будет намного трудней выполнить
ROLLBACK для ALTER/DROP/CREATE TABLE.
Чтобы избежать использования ROLLBACK, Вы можете использовать
следующую стратегию действий:
LOCK TABLES ..., чтобы блокировать все
таблицы, к которым Вы хотите обращаться.
UNLOCK TABLES, чтобы снять блокировки.Это обычно намного более быстрый метод, чем использование транзакций с
возможностью ROLLBACK, хотя и не всегда. Единственная ситуация,
которую это решение не обрабатывает, состоит в том, что кто-то уничтожает
поток в середине модификации. В этом случае все блокировки будут сняты, но
некоторые из модификаций, возможно, не будут выполнены.
Вы можете также использовать функции, чтобы модифицировать записи в одиночной операции. Вы можете получать очень эффективную прикладную программу, применяя следующие методы:
Например, когда мы делаем модификации некоторой информации заказчика, мы
модифицируем только данные заказчика, которые изменились, и проверяем, что ни
один из измененных данных или других данных, которые зависят от измененных
данных, не изменился по сравнению с первоначальной строкой. Тест для
измененных данных выполнен с предложением 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 AB авторы пакета никогда не имели никакой потребности в блокировке уровня строки потому, что всегда могли ее обойти. Некоторые случаи и в самом деле нуждаются в блокировке строки, но они очень немногочисленны. Если Вы хотите иметь блокировку уровня строки, Вы можете использовать столбец флажка в таблице и делать нечто вроде:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL вернет для числа обработанных строк, если строка была найдена, и
row_flag не был 1 в первоначальной строке.
Перечисленные ниже проблемы известны авторам пакета, и их устранение имеет очень высокий приоритет.
ANALYZE TABLE на таблицах BDB может в некоторых случаях
делать таблицу непригодной, пока Вы не перезапустите mysqld.
Когда это выполнено, Вы будете видеть ошибки подобные следующим в файле
регистрации ошибок MySQL:
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
ALTER TABLE на таблице BDB, на
которой Вы управляете незавершенными многооператорными транзакциями.
Транзакция будет, вероятно, игнорироваться.
ANALYZE TABLE, OPTIMIZE TABLE и REPAIR
TABLE могут вызывать проблемы на таблицах, для которых Вы используете
вызов INSERT DELAYED.
LOCK TABLE ... и FLUSH TABLES ...
еще не гарантирует, что не имеется наполовину выполненной транзакции.
mysql на этой базе данных, если Вы не используете опцию
-A или применяете rehash. Это особенно важно, когда
Вы имеете большой кэш таблицы.
LOAD DATA
INFILE и выравнивать символы признака конца строки, которые сами
занимают больше, чем 1 символ.Следующие проблемы известны и будут устранены в назначенное время:
MATCH работает только с инструкциями
SELECT.
SET CHARACTER SET не могут быть применены
транслируемые символы в базе данных, таблице и столбцах.
DELETE FROM merge_table, используемый без
WHERE, только очистит отображение для таблицы, не удаляя ничего
в самих отображенных таблицах.
BLOB не могут надежно использоваться в GROUP
BY, ORDER BY или DISTINCT. Только первые
max_sort_length байт (по умолчанию 1024) используются при
сравнении BLOB в этих случаях. Это может быть изменено с помощью
опции -O max_sort_length при запуске mysqld. Обход
для большинства случаев должен использовать подстроку: SELECT DISTINCT
LEFT(blob,2048) FROM tbl_name.
BIGINT или DOUBLE
(оба обычно длиной в 64 бита). Это зависит от функции, которую обрабатывает
пакет. Общее правило: битовые функции выполнены с точностью
BIGINT, IF и ELT() с
BIGINT или DOUBLE, а все прочие с
DOUBLE. Нужно пробовать избегать использовать большие длинные
значения без знака (9223372036854775807)!
BLOB и TEXT,
автоматически удаляют все конечные пробелы, когда сохраняются. Для типа
CHAR это разрешено и может быть расценено как свойство согласно
ANSI SQL92. Ошибка в том, что в MySQL столбцы VARCHAR
обрабатываются тем же самым путем.
ENUM и
SET для каждой таблицы.
safe_mysqld переназначает все сообщения mysqld
в файл регистрации mysqld. Одна проблема с этим состоит в том,
что, если Вы выполняете mysqladmin refresh, чтобы закрыть и
вновь открыть файл регистрации, stdout и stderr
все еще переназначаются к старому файлу регистрации. Если Вы используете
опцию --log, Вы должны редактировать safe_mysqld,
чтобы регистрировать данные в файле 'hostname'.err вместо
'hostname'.log, чтобы у Вас не было крупных проблем с запуском и
работой с mysqladmin refresh.
UPDATE столбцы модифицируются слева направо.
Если Вы обращаетесь к модифицируемому столбцу, Вы получите модифицируемое
значение вместо первоначального значения. Например:
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;Это модифицирует
KEY с 2 вместо 1.
select * from temporary_table, temporary_table as t2;
RENAME не работает с таблицами TEMPORARY.
DISTINCT по-разному, если Вы
используете скрытые столбцы в объединении или нет. В объединении скрытые
столбцы рассчитаны как часть результата (даже если они не показаны) в то
время, как в нормальном запросе они не участвуют в сравнении
DISTINCT. Мы, вероятно, изменим это в будущем, чтобы никогда не
сравнить скрытые столбцы при выполнении DISTINCT. Например:
SELECT DISTINCT mp3id FROM band_downloads WHERE userid=9 ORDER BY id DESC;и
SELECT DISTINCT band_downloads.mp3id, FROM band_downloads,band_mp3
WHERE band_downloads.userid=9 AND band_mp3.id=band_downloads.mp3id
ORDER BY band_downloads.id DESC;
Во втором случае Вы можете в MySQL 3.23.x получить две идентичных строки в
наборе результатов (потому, что скрытый столбец 'id' может
отличаться). Обратите внимание, что это случается только для запросов, где Вы
не имеете ORDER BY в результате, что вообще-то неправильно с точки зрения
стандарта ANSI SQL.
rollback), некоторые вещи ведут себя немного иначе, чем в других
серверах SQL. Это только должно гарантировать, что MySQL никогда не должен
делать обратную перемотку для команд SQL. Это может быть иногда немного
неуклюже, поскольку значения столбца должны проверяться прикладной
программой, но это фактически даст Вам хорошее увеличение быстродействия,
поскольку это позволяет MySQL делать некоторые оптимизации, которые иначе
были бы невозможны или очень трудны. Если Вы устанавливаете столбец к
неправильному значению, MySQL будет, вместо того, чтобы делать обратную
перемотку, сохранять самое лучшее возможное значение в столбце.
NULL, который не берет
значения NULL, MySQL сохранит 0 или '' (пустую
строку). Это поведение может, однако, быть изменено с опцией компиляции
-DDONT_USE_DEFAULT_FIELDS.
DATE и DATETIME. Например, 2000-02-31 или
2000-02-00. Если дата полностью неправильна, MySQL сохранит в столбце
специальное значение даты 0000-00-00.
enum к неподдерживаемому значению,
это будет установлено к значению ошибки 'empty string' с числовым значением 0.
PROCEDURE на запросе, который возвращает
пустой набор, в некоторых случаях PROCEDURE не будет
трансформировать столбцы.
MERGE не проверяет, имеют ли основные
таблицы совместимые типы.
NaN,
-Inf и Inf в double. Использование их вызовет
проблемы при попытке экспортировать и импортировать данные. Вы должны как
промежуточное решение изменить NaN на NULL (если
возможно), а -Inf и Inf соответственно к
минимальному и максимальному возможным значениям double.
LIMIT на отрицательных числах превращается в очень большие
положительные числа, что ошибочно.
ALTER TABLE, чтобы сначала добавить
индекс UNIQUE к таблице, используемой в таблице типа
MERGE, и затем использовать ALTER TABLE, чтобы
добавить нормальный индекс уже на таблице MERGE, порядок ключей
будет иным для таблиц, если имелся старый не уникальный ключ в таблице. Это
потому, что ALTER TABLE помещает ключи UNIQUE перед
нормальными ключами, чтобы обнаружить двойные ключи как можно раньше.Следующее представляет известные ошибки в более ранних версиях MySQL:
DROP
TABLE на таблице, которая является одной среди многих таблиц, которые
блокированы с помощью LOCK TABLES.
LOCK table с WRITE
FLUSH TABLESUPDATE, который модифицировал ключ
с помощью WHERE на том же самом ключе, возможно, потерпел
неудачу потому, что та же самая строка использовалась для поиска:
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;Обойти это можно так:
mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;Это будет работать потому, что MySQL не будет использовать индекс на выражениях в предложении
WHERE.
Для изучения ошибок, специфических для конкретной платформы, изучите разделы по компиляции и портированию.