MySQL 5 — что нового предлагают нам разработчики?

КИРИЛЛ СУХОВ

MySQL 5 – что нового предлагают нам разработчики?

10 лет, с 1995 года, нас критиковали за отсутствие возможностей, присущих «настоящим» СУБД

 Пятой версией мы ответили на все вопросы сразу.

 

Дэвид Аксмарк, один из основателей MySQL

На лето запланирован выход новой, пятой версии популярнейшего сервера баз данных – MySQL. Как правило, смена первой цифры в номере версии означает довольно радикальные перемены. Это удивительно – возможности четвёртой ветки, появившейся чуть более года назад, освоены и приняты ещё далеко не всеми разработчиками. И тем не менее уже сейчас MySQL 5 доступна для тестирования в виде бета-версии. Что действительно нового предлагают её создатели?

«Просто набор индексированных файлов!», «недо-база данных» – такие слова в адрес СУБД MySQL нередко можно найти на форумах разработчиков. И приходится признать – они имели под собой основание. MySQL при всех её достоинствах (нетребовательность к ресурсам, быстродействие, высокая производительность, на простых запросах, низкая стоимость владения) имела очевидные недостатки. Отсутствовали элементарные средства контроля целостности данных, не было механизма транзакций, да что там, не было даже вложенных запросов, а о хранимых процедурах и триггерах приходилось только мечтать.

Положение изменилось с выходом версий 4.0 и 4.1. Помимо вышеупомянутых вложенных запросов и транзакций было осуществлено ещё множество нововведений. Появилась возможность задания кодировок на уровне базы данных, таблиц и столбцов, поддержка Unicode (utf8 и ucs2), включён геометрический тип данных – GIS, введёно понятие «подготовленных выражений» (prepared statements).

С выходом MySQL 5 нас ждут ещё более радикальные изменения. Разработчики из MySQL AB установили довольно высокую планку – вплотную приблизиться к стандарту SQL:2003. Большая часть новых возможностей уже доступна в бета-версии пятой ветки, и основные из них мы с вами рассмотрим.

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

Данной функциональности, пожалуй, больше всего не хватало разработчикам. Смысл применения хранимых процедур (stored procedures) и функций – сохранять на сервере скомпилированные блоки SQL-кода. После единожды написанной, отлаженной и скомпилированной процедуры или функции, её можно вызывать из вашего приложения, передавая на сервер вместо обычного запроса, только вызов процедуры и аргументы. При этом не только снижаются затраты на трафик и повышается производительность. Главное преимущество состоит в том, что бизнес-логика приложения, возложенная на базу данных, становится прозрачней и переносимей.

Как процедуры, так и функции могут возвращать значения (в виде набора записей). Различие состоит в том, что функция вызывается из запроса, а процедура из отдельной команды.

На настоящий момент реализация хранимых процедур не поддерживает никаких внешних языков, но (по крайней мере, так заявляется) соответствует стандарту SQL:2003, позволяющему применять условные конструкции, итерации и обработку ошибок.

Пример создания хранимой процедуры в MySQL 5:

CREATE PROCEDURE p ()

LANGUAGE SQL

NOT DETERMINISTIC

SQL SECURITY DEFINER

COMMENT "A Procedure" <--

SELECT CURRENT_DATE, RAND() FROM t

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

mysql> call p2()

+--------------+-----------------+ | CURRENT_DATE | RAND()          | +--------------+-----------------+ | 2005-06-27   | 0.7822275075896 | +--------------+-----------------+ 1 row in set (0.26 sec) Query OK, 0 rows affected (0.26 sec)

Чуть более сложный пример создания и использования функции:

CREATE FUNCTION factorial (n DECIMAL(3,0))

RETURNS DECIMAL(20,0)

DETERMINISTIC

BEGIN

DECLARE factorial DECIMAL(20,0) DEFAULT 1;

DECLARE counter DECIMAL(3,0);

SET counter = n;

factorial_loop: REPEAT

SET factorial = factorial * counter;

SET counter = counter - 1;

UNTIL counter = 1

END REPEAT;

RETURN factorial;

END

В приложении:

INSERT INTO t VALUES (factorial(pi)) 

SELECT s1, factorial (s1) FROM t 

UPDATE t SET s1 = factorial(s1)

WHERE factorial(s1) < 5

Разумеется эфективность применения хранимых пороцедур существенно возрастает при вызове их с параметрами (аргументами). Ниже дан пример процедуры с обработкой переданых ей параметров:

CREATE PROCEDURE p1 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1; <--

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END;

Вызов процедуры теперь будет таким:

mysql> CALL p2(0) // Query OK, 2 rows affected (0.28 sec)

и в результате запроса мы получим:

mysql> SELECT * FROM t

+----+ | s1 | +----+ | 6  | | 6  | +------+ 2 rows in set (0.01 sec)

Кроме условных, возможны и любые циклические конструкции:

CREATE PROCEDURE p3 ()

BEGIN

DECLARE v INT;

SET v = 0;

WHILE v < 5 DO

INSERT INTO t VALUES (v);

SET v = v + 1;

END WHILE;

END;

Вызов процедуры:

mysql> CALL p3()

+------+ | s1   | +------+ …………   |    0 | |    1 | |    2 | |    3 | |    4 | +------+ Query OK, 1 row affected (0.00 sec)

Также применимы итерации, переходы, словом, всё, что предполагает стандарт.

Внутри функций и хранимых процедур осуществлена реализация курсоров, но, к сожалению, она пока ограничена (ASESITIVE, READ ONLY и NONSCROLL):

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;

Триггеры

Триггеры похожи на хранимые процедуры. Это тоже скомпилированные SQL-запросы, хранимые на сервере. Отличие состоит в том, что триггер начинает работать в ответ на определённое событие, а именно на запросы UPDATE и DELETE.

Пример создания и работы триггера:

CREATE TABLE t22 (s1 INTEGER)

CREATE TRIGGER t22_bi

BEFORE INSERT ON t22

FOR EACH ROW

BEGIN

SET @x = ‘Trigger was activated!’;

SET NEW.s1 = 55;

END;

После этого при выполнении запросов получим:

mysql> INSERT INTO t22 VALUES (1)

mysql> SELECT @x, t22.* FROM t22 // вызывается триггер

+------------------------+------+ | @x                     | s1   | +------------------------+------+ | Trigger was activated! | 55   | +------------------------+------+ 1 row in set (0.00 sec)

К сожалению, на данный момент триггеры в MySQL могут обращаться только к своей таблице. Это, конечно, не делает данный механизм совсем бесполезным, но существенно сужает его возможности. Тради Пелзер (Trudy Pelzer) из MySQL AB заявляет, что разработчики трудятся сейчас над снятием этого ограничения, но достигнут ли они результатов к моменту релиза пятой версии, не сообщается.

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

Представления (views) можно сравнить с временными таблицами, наполненными динамически формируемым содержимым. В общем-то, неименованные представления (те самые вложенные запросы) были доступны ещё в версии 4.1. В настоящей реализации есть две возможности создания представлений: с использованием алгоритма временных таблиц MySQL и с созданием самостоятельной таблицы. Нас интересует именно второй способ (первый был реализован, скорее всего, исходя из соображений совместимости и унификации). Такие представления позволяют значительно снизить объём кода, в котором часто повторялись простые объединения таблиц. К ним (после создания) применимы любые запросы, возвращающие результат в виде набора строк. То есть команды SELECT, UPDATE, DELETE, можно применять так же, как и к реальным таблицам. Важно и то, что посредством представлений можно более гибко распоряжаться правами пользователей базы данных, так как в этом случае есть возможность предоставлять доступ на уровне отдельных записей различных таблиц. Пример создания и работы простейшего (и потому абсолютно бесполезного) представления:

mysql> CREATE VIEW v AS SELECT column1 FROM t;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO v VALUES (1);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;

+---------+ | column1 | +---------+ | 1       | +---------+ 1 row in set (0.00 sec)

Словарь данных

Иметь доступ к значениям метаданных – совершенно необходимое требование к современной СУБД. Ранее такая возможность в MySQL достигалась различными SHOW-командами, но такой подход имеет очевидные недостатки. Эти команды нельзя использовать в простых запросах с соединениями, и, что существенно, они не соответствовали стандартам, будучи специфичными для MySQL.

В новой версии СУБД появилась новая служебная база данных – INFORMATION_SCHEMA. Её наличие продиктовано тем же стандартом SQL:2003, и именно она решает задачу реализации словаря данных (data dictionary). INFORMATION_SCHEMA содержит таблицы, описывающие состояние и параметры сервера, в том числе определения и сущности таблиц. Это виртуальная база данных – физически (в виде файлов на диске) она не существует, вся информация динамически предоставляется сервером. Пример использования этой таблицы:

mysql> SELECT table_name, table_type, engine

    -> FROM INFORMATION_SCHEMA.tables

    -> WHERE table_schema = ‘tp’

    -> ORDER BY table_type ASC, table_name DESC;

+------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | t2         | BASE TABLE | MyISAM | | t1         | BASE TABLE | InnoDB | | v1         | VIEW       | NULL   | +------------+------------+--------+

Другой пример работы со словарём данных – просмотр привелегий:

mysql> SELECT * FROM

    -> INFORMATION_SCHEMA.COLUMN_PRIVILEGESG

************************ 1. row ************************ GRANTEE: "peter"@"%" TABLE_CATALOG: NULL TABLE_SCHEMA: tp TABLE_NAME: t1 COLUMN_NAME: col1 PRIVILEGE_TYPE: UPDATE IS_GRANTABLE: NO ************************ 2. row ************************ GRANTEE: "trudy"@"%" TABLE_CATALOG: NULL TABLE_SCHEMA: tp TABLE_NAME: t2 COLUMN_NAME: col1 PRIVILEGE_TYPE: SELECT IS_GRANTABLE: YES

Заключение

Темп, взятый командой MySQL AB два года назад, впечатляет. Полным ходом идёт работа над MySQL 5.1, в которой планируется осуществить такие возможности, как поддержка внешних ключей (foreign key) для всех типов таблиц (в настоящий момент она существует только для таблиц InnoDB), новые схемы репликации (online backup) и кэширования таблиц и многое другое. Более того, в разделе официального руководства по MySQL (MySQL Reference Manual), озаглавленном «Что не планируется реализовать?», прямо сказано, что таких задач просто нет – реализовать планируется всё! Существует, правда, мнение, что новые возможности MySQL нивелируют её основные преимущества, такие как быстродействие, простота и нетребовательность к ресурсам. Возможно, в чём-то критики и правы, но пока разработчикам удавалось этого избежать. Будем, надеяться, что удастся и далее.