Синтаксические различия между MariaDB и SQL Server

В данной статье представлен неполный список синтаксических различий между MariaDB и SQL Server, написанный для пользователей SQL Server, незнакомых с MariaDB.

Функции совместимости

Некоторые функции предназначены для улучшения совместимости синтаксиса и семантики между версиями MariaDB, между MariaDB и MySQL, а также между MariaDB и другими СУБД. Этот раздел посвящен совместимости между MariaDB и SQL Server.

sql_mode и old_mode

Семантика и синтаксис SQL в MariaDB зависят от переменной sql_mode. Ее значение представляет собой список флагов, разделенных запятыми, и каждый из них, если указан, влияет на разные аспекты синтаксиса и семантики SQL.

Особо важным флагом для пользователей, знакомых с SQL Server, является MSSQL.

sql_mode можно изменить локально, в этом случае он повлияет только на текущую сессию; или глобально, в этом случае он повлияет на все новые подключения (но не на уже установленные). sql_mode должен быть задан в виде списка флагов, разделенных запятыми.

Пример использования:

# check the current global and local sql_mode values
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
# empty sql_mode for all usaers
SET GLOBAL sql_mode = '';
# add MSSQL flag to the sql_mode for the current session
SET SESSION sql_mode = CONCAT(sql_mode, ',MSSQL');

old_mode очень похож на sql_mode, но его цель — обеспечить совместимость со старыми версиями MariaDB. Его флаги не должны влиять на совместимость с SQL Server (хотя теоретически возможно, что некоторые из них влияют как побочный эффект).

Выполняемые комментарии

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

В следующих примерах показано, как вставить код SQL, который будет проигнорирован SQL Server, но выполнен MariaDB или некоторыми из его версий.

• Выполняется MariaDB и MySQL (см. ниже):

SELECT * FROM tab /*! FORCE INDEX (idx_a) */ WHERE a = 1 OR b = 2;

• Выполняется только MariaDB:

SELECT * /*M! , @in_transaction */ FROM tab;

• Выполняется MariaDB начиная с версии 10.0.5:

DELETE FROM user WHERE id = 100 /*!M100005 RETURNING email */;

Как объясняется на странице Архитектура MariaDB, MariaDB изначально была разветвлена от MySQL. В то время выполняемые комментарии уже поддерживались MySQL. Вот почему синтаксис /*! ... */ поддерживается как MariaDB, так и MySQL. Но так как MariaDB также поддерживает специфический синтаксис, не поддерживаемый MySQL, она добавила синтаксис /*M! ... */.

Общий синтаксис

Здесь рассматриваются некоторые различия в синтаксисе между MariaDB и SQL Server, которые могут повлиять на любого пользователя, а также некоторые советы по созданию запросов, совместимых с разумной затратой усилий.

Разделители

SQL Server использует два разных терминатора:

• Терминатор пакета — это команда go. Она сообщает клиентам Microsoft отправить текст, который мы набрали, в SQL Server.
• Терминатор запроса — это точка с запятой (;) и она указывает SQL Server, где заканчивается запрос.

Использование ; в SQL Server редко необходимо. Оно требуется для определённых общих выражений с таблицей, например.

Но это не относится к MariaDB. Обычно в MariaDB используется только ;.

Однако, в MariaDB также есть ситуации, когда вы хотите использовать ;, но не хотите, чтобы клиентская программа mariadb отправляла запрос. Это может быть полезно при создании хранимых процедур или использовании BEGIN NOT ATOMIC.

Причина лучше объяснима на примере:

CREATE PROCEDURE p()
BEGIN
    SELECT * FROM t1;
    SELECT * FROM t2;
END;

Если мы введём эту процедуру таким образом в клиент mariadb, как только мы наберём первую ; (после первой SELECT) и нажмём Enter, утверждение будет отправлено. MariaDB попытается его обработать и вернёт ошибку.

Чтобы избежать этого, mariadb реализует инструкцию DELIMITER. Эта клиентская инструкция никогда не отправляется в MariaDB. Вместо этого клиент использует её для определения того, когда следует отправить набранный запрос. Давайте исправим вышеуказанный пример:

DELIMITER ||

CREATE PROCEDURE p()
BEGIN
    SELECT * FROM t1;
    SELECT * FROM t2;
END;

DELIMITER ;

Имена

В MariaDB большинство имен имеют максимальную длину 64 символа. При миграции базы данных SQL Server в MariaDB проверьте, не превышают ли некоторые имена этот предел (максимальная длина в SQL Server — 128).

По умолчанию имена в MariaDB чувствительны к регистру, если имена файлов в операционной системе чувствительны к регистру (Linux), и нечувствительны к регистру, если операционная система нечувствительна к регистру (Windows). SQL Server по умолчанию нечувствителен к регистру во всех операционных системах.

При миграции базы данных SQL Server в MariaDB на Linux, чтобы избежать проблем, вы можете установить системную переменную lower_case_table_names в значение 1, что сделает имена таблиц, имен баз данных и псевдонимов нечувствительными к регистру.

Имена могут быть заключены в обратные кавычки (`). Этот символ может использоваться в именах, в этом случае он должен быть удвоен. По умолчанию это единственный способ заключить имена в кавычки.

Чтобы также включить использование двойных кавычек ("), измените sql_mode, добавив флаг ANSI_QUOTES. Это эквивалентно установке QUOTED_IDENTIFIER ON в SQL Server.

Чтобы также включить использование кавычек в стиле SQL Server ([ и ]), измените sql_mode, добавив флаг MSSQL.

Чувствительность к регистру хранимых процедур и функций никогда не является проблемой, поскольку они нечувствительны к регистру в SQL Server.

Заключение строк в кавычки

В SQL Server по умолчанию строки могут быть заключены только в одинарные кавычки ('), а для использования двойной кавычки в строке она должна быть удвоена (''). Это также работает по умолчанию в MariaDB.

SQL Server также позволяет использовать двойные кавычки (" ) для заключения строк в кавычки. Это работает по умолчанию в MariaDB, но, как упоминалось ранее, это не сработает, если sql_mode содержит флаг ANSI_QUOTES.

NULL

По умолчанию семантика NULL в SQL Server и MariaDB одинакова.

Однако SQL Server позволяет изменять ее глобально с помощью SET ANSI_NULLS OFF или на уровне базы данных с помощью ALTER DATABASE.

Точно такого же результата в MariaDB достичь нельзя. Для выполнения сравнений с учетом NULL в MariaDB следует заменить оператор = на оператор <=>.

Также обратите внимание, что MariaDB не поддерживает псевдо-значение UNKNOWN. Выражение, такое как NULL OR 0, возвращает NULL в MariaDB.

LIKE

В MariaDB выражения LIKE имеют только два символа со специальным значением: % и _. Эти два символа имеют те же значения, что и в SQL Server.

Дополнительные символы, распознаваемые SQL Server ([, ] и ^ ), являются частью регулярных выражений. MariaDB поддерживает оператор REGEXP, который поддерживает полный синтаксис регулярных выражений.

Язык определения данных

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

Хотя этот раздел призван выделить наиболее заметные различия в языке определения данных между MariaDB и SQL Server, их гораздо больше, как в синтаксисе, так и в семантике. См. документацию по инструкции ALTER.

Изменение таблиц онлайн

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

MariaDB предлагает следующие решения для помощи:

• Оператор ALTER TABLE ... ALGORITHM позволяет указать алгоритм, который должен быть использован для выполнения определённой операции. Например, INPLACE говорит MariaDB не создавать копию таблицы (возможно, потому что у нас недостаточно места на диске), а INSTANT говорит MariaDB выполнить операцию мгновенно. Не все алгоритмы поддерживаются для определённых операций. Если выбранный алгоритм не может быть использован, инструкция ALTER TABLE завершится ошибкой.
• Оператор ALTER TABLE ... LOCK позволяет указать тип блокировки, который должен быть использован. Например, NONE говорит MariaDB избежать блокировки таблицы, а SHARED позволяет получить только общую блокировку. Если операция требует более жёсткую блокировку, чем запрошенная, инструкция ALTER TABLE завершится ошибкой. Иногда это происходит потому, что необходимый уровень LOCK недоступен для указанного ALGORITHM.

Чтобы узнать, какие операции требуют копирования таблицы и какие уровни блокировки необходимы, см. Обзор онлайн DDL InnoDB.

Операция ALTER TABLE может быть поставлена в очередь, потому что длительная инструкция (даже SELECT) потребовала блокировку метаданных. Поскольку это может вызвать проблемы, иногда мы хотим, чтобы операция просто завершилась ошибкой, если ожидание слишком долго. Это можно достичь с помощью операторов WAIT и NOWAIT, синтаксис которых немного отличается от SQL Server.

SQL Server WITH ONLINE = ON эквивалентно MariaDB LOCK = NONE. Однако обратите внимание, что большинство инструкций ALTER TABLE поддерживают ALGORITHM = INSTANT, что не блокирует и намного быстрее (почти мгновенно, как подсказывает синтаксис).

IF EXISTS, IF NOT EXISTS, или REPLACE

Большинство инструкций языка определения данных, включая ALTER TABLE, поддерживают следующий синтаксис:

• DROP IF EXISTS: Если объект не существует, выводится предупреждение (а не ошибка).
• OR REPLACE: Если объект существует, он удаляется и воссоздается; в противном случае создаётся. Эта операция атомарна, поэтому в какой-то момент объект не существует.
• CREATE IF NOT EXISTS: Если объект уже существует, выводится предупреждение (а не ошибка). Объект не будет заменён.

Эти утверждения функционально похожи (но менее подробны) на фрагменты кода SQL Server, похожие на следующие:

IF NOT EXISTS (
        SELECT name
            FROM sysobjects
            WHERE name = 'my_table' AND xtype = 'U'
    )
    CREATE TABLE my_table (
        ...
    )
go

Изменение столбцов

В SQL Server единственный синтаксис для изменения столбца таблицы — ALTER TABLE ... ALTER COLUMN. MariaDB предоставляет больше команд ALTER TABLE для достижения того же результата:

• CHANGE COLUMN позволяет выполнить любое изменение, указав новое определение столбца, включая имя.
• MODIFY COLUMN позволяет произвести любое изменение, кроме переименования столбца. Это немного более простой синтаксис, который можно использовать, если не нужно изменять имя столбца.
• ALTER COLUMN позволяет изменить или удалить значение DEFAULT.
• RENAME COLUMN позволяет только изменить имя столбца.

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

Слово COLUMN обычно необязательно, за исключением случая RENAME COLUMN.

Команды SHOW

MariaDB поддерживает команды SHOW для быстрого перечисления всех объектов определённого типа (таблицы, представления, триггеры...). Большинство команд SHOW поддерживают LIKE-оператор для фильтрации данных. Например, чтобы перечислить таблицы в текущей базе данных, чьё имя начинается с 'wp_':

SHOW TABLES LIKE 'wp\_%';

Это эквивалентно этому запросу, который будет работать как в MariaDB, так и в SQL Server:

SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE 'wp\_';

Команды SHOW CREATE

В общем случае для каждой команды CREATE MariaDB также поддерживает команду SHOW CREATE. Например, существует команда SHOW CREATE TABLE, которая возвращает оператор CREATE TABLE, который можно использовать для повторного создания таблицы.

Хотя в SQL Server нет способа показать оператор DDL для повторного создания объекта, команды SHOW CREATE функционально аналогичны sp_helptext().

Комментарии к базе данных

MariaDB не поддерживает расширенные свойства. Вместо этого она поддерживает COMMENT-оператор для большинства команд CREATE и ALTER.

Например, чтобы создать, а затем изменить комментарий к таблице:

CREATE TABLE counter (
    c INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
)
    COMMENT 'Monotonic counter'
;
ALTER TABLE counter COMMENT
    'Counter. It can contain many values, we only care about the max';

Комментарии можно увидеть с помощью команд SHOW CREATE или обращаясь к таблицам information_schema. Например:

SELECT TABLE_COMMENT
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'counter';
+-----------------------------------------------------------------+
| TABLE_COMMENT                                                   |
+-----------------------------------------------------------------+
| Counter. It can contain many values, we only care about the max |
+-----------------------------------------------------------------+

Обработка ошибок

Команды MariaDB SHOW ERRORS и SHOW WARNINGS могут использоваться для отображения ошибок или предупреждений и ошибок. Это удобно для клиентов, но хранимые процедуры не могут работать с выводом этих команд.

Вместо этого внутри хранимых процедур вы можете:

• Использовать команду GET DIAGNOSTICS для присвоения свойств ошибок переменным. Это эквивалентно использованию функций SQL Server, таких как ERROR_NUMBER() или ERROR_STATE().
• Добавить блок DECLARE HANDLER для обработки всех ошибок, класса ошибок или конкретной ошибки. Это эквивалентно SQL Server TRY ... CATCH.
• Ошибку или предупреждение можно сгенерировать преднамеренно с помощью SIGNAL. Внутри блока DECLARE HANDLER, RESIGNAL можно использовать для повторного выдачи ошибки и прерывания выполнения блока. Это эквиваленты SQL Server RAISERROR().

Администрирование

Команды администрирования и обслуживания в MariaDB используют другой синтаксис, чем SQL Server.

• OPTIMIZE TABLE перестраивает данные и индексы таблицы. Его можно рассматривать как эквивалент SQL Server ALTER INDEX REBUILD в MariaDB. Более подробную информацию см. в разделе Дефрагментация табличных пространств InnoDB. Эта команда всегда блокирует. Она поддерживает синтаксис WAIT и NOWAIT,
• MariaDB имеет команду ANALYZE TABLE, которая является эквивалентом UPDATE STATISTICS.

BULK INSERT

В MariaDB нет команды BULK INSERT. Вместо этого она поддерживает:

• LOAD DATA INFILE для загрузки данных из файлов в формате CSV или подобных;
• LOAD XML INFILE для загрузки данных из XML-файлов.

См. также Как быстро вставить данные в MariaDB.

См. также

• Сравнение типов данных SQL Server и MariaDB
• Транзакции и уровни изоляции MariaDB для пользователей SQL Server
• SQL_MODE=MSSQL