Syntax Differences between MariaDB and SQL Server
This article is meant to show a non-exhaustive list of syntax differences between MariaDB and SQL Server, and it's written for SQL Server users that are unfamiliar with MariaDB.
Some features are meant to improve syntax and semantics compatibility between MariaDB versions, between MariaDB and MySQL, and between MariaDB and other DBMSs. This section focuses on the compatibility between MariaDB and SQL Server.
sql_mode and old_mode
The SQL semantics and syntax, in MariaDB, are affected by the
sql_mode variable. Its value is a comma-separated list of flags, and each of them if specified affects a different aspects of the SQL syntax and semantics.
A particularly important flag for users familiar with SQL Server is
sql_mode can be changed locally, in which case it only affects the current session; or globally, in which case it will affect all new connections (but not the connections already established). sql_mode must be assigned a comma-separated list of flags.
A usage example:
# 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 is very similar to sql_mode, but its purpose is to provide compatibility with older MariaDB versions. Its flags shouldn't affect the compatibility with SQL Server (though it is theoretically possible that some of them do, as a side effect).
MariaDB supports executable comments. These are designed to write generic queries that are only executed by MariaDB, and optionally only certain versions.
The following examples show how to insert SQL code that will be ignored by SQL Server but executed by MariaDB, or some of its versions.
- Executed by MariaDB and MySQL:
SELECT * FROM tab /*! FORCE INDEX (idx_a) */ WHERE a = 1 OR b = 2;
- Executed by MariaDB only:
SELECT * /*!M , @in_transaction */ FROM tab;
- Executed by MariaDB starting from version 10.0.5:
DELETE FROM user WHERE id = 100 /*!M100005 RETURNING email */;
Here we discuss some differences between MariaDB and SQL Server syntax that may affect any user, as well as some hints to make queries compatible with a reasonable amount of work.
SQL Server uses two different terminators:
- The batch terminator is the
gocommand. It tells Microsoft clients to send the text we typed to SQL Server.
- The query terminator is a semicolon (
;) and it tells SQL Server where a query ends.
It is rarely necessary to use
; in SQL Server. It is required for certain common table expressions, for example.
But the same cases don't apply to MariaDB. Normally, with MariaDB you only use
However, MariaDB also has some situations where you want to use a
; but you don't want the
mysql command-line client to send the query yet. This can be done in any situation, but it is particularly useful when creating
stored routines or using BEGIN NOT ATOMIC
The reason is better explained with an example:
CREATE PROCEDURE p() BEGIN SELECT * FROM t1; SELECT * FROM t2; END;
If we enter this procedure in this way in
mysql client, as soon as we type the first
; (after the first
SELECT) and press enter the statement will be sent. MariaDB will try to parse it, and will return an error.
To avoid this,
mysql implements the
DELIMITER statement. This client statement is never sent to MariaDB. Instead, the client uses it to find out when the typed query should be sent. Let's correct the above example:
DELIMITER || CREATE PROCEDURE p() BEGIN SELECT * FROM t1; SELECT * FROM t2; END; DELIMITER ;
In MariaDB, most names have a maximum length of 64 characters. When migrating an SQL Server database to MariaDB, check if some names exceed this limit (SQL Server maximum length is 128).
By default, MariaDB names are case-sensitive if the operating system has case-sensitive file names (Linux), and case-insensitive if the operating system is case-insensitive (Windows). SQL Server is case-insensitive by default on all operating systems.
When migrating an SQL Server database to MariaDB on Linux, to avoid problems you may want to set the lower_case_table_names system variable to 1, making table names, database names and aliases case-insensitive.
Names can be quoted inside backtick characters (
`). This character can be used in names, in which case it should be doubled. By default this is the only way to quote names.
To also enable the use of double quotes (
"), modify sql_mode adding the
ANSI_QUOTES flag. This is the equivalent of setting QUOTED_IDENTIFIER ON in SQL Server.
To also enable the use of SQL Server style quotes (
]), modify sql_mode adding the
The case-sensitivity of stored procedures and functions is never a problem, as they are case-insensitive in SQL Server.
In SQL Server, by default strings can only be quoted with single-quotes (
'), and to use a double quote in a string it should be doubled (
''). This also works by default in MariaDB.
SQL Server also allows to use double quotes (
") to quote strings. This works by default in MariaDB, but as mentioned before it won't work if sql_mode contains the
The default semantics of NULL in SQL Server and MariaDB is the same, by default.
Data Definition Language
Here we discuss some DDL differences that database administrators will want to be aware of.
Altering Tables Online
Altering tables online can be a problem, especially when the tables are big and we don't want to cause a disruption.
MariaDB offers the following solutions to help:
ALTER TABLE ... ALGORITHMclause allows to specify which algorithm should be used to run a certain operation. For example
INPLACEtells MariaDB not to create a table copy (perhaps because we don't have enough disk space), and
INSTANTtells MariaDB to execute the operation instantaneously. Not all algorithms are supported for certain operations. If the algorithm we've chosed cannot be used, the
ALTER TABLEstatement will fail with an error.
ALTER TABLE ... LOCKclause allows to specify which lock type should be used. For example
NONEtells MariaDB to avoid any lock on the table, and
SHAREDonly allows to acquire a share lock. If the operation requires a lock that is more strict than the one we are requesting, the
ALTER TABLEstatement will fail with an error. Sometimes this happens because the
LOCKlevel we want is not available for the specified
To find out which operations require a table copy and which lock levels are necessary, see InnoDB Online DDL Overview.
ALTER TABLE can be queued because a long-running statement (even a
SELECT) required a metadata lock. Since this may cause troubles, sometimes we want the operation to simply fail if the wait is too long. This can be achieved with the WAIT and NOWAIT clauses.
WITH ONLINE = ON is equivalent to MariaDB
LOCK = NONE. However, note that some operations support
ALGORITHM = INSTANT, which is non-blocking and much faster (almost instantaneous, as the syntax suggest).
IF EXISTS, IF NOT EXISTS, OR REPLACE
Most DDL statements, including
ALTER TABLE, support the following syntax:
DROP IF EXISTS: A warning (not an error) is produced if the object does not exist.
OR REPLACE: If the object exists, it is dropped and recreated; otherwise it is created. This operation is atomic, so in no point in time the object does not exist.
CREATE IF NOT EXISTS: If the object already exists, a warning (not an error) is produced. The object will not be replaced.
These statements are functionally similar (but less verbose) than SQL Server snippets similar to the following:
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'my_table' AND xtype = 'U') CREATE TABLE my_table ( ... ) go
SHOW statements to quickly list all objects of a certain type (tables, views, triggers...). Most
SHOW statements support a
LIKE clause to filter data. For example, to list the tables in the current database whose name begins with 'wp_':
SHOW TABLES LIKE 'wp\_%';
This is the equivalent of this query, which would work on both MariaDB and SQL Server:
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'wp\_';
SHOW CREATE Statements
In general, for each
CREATE statement MariaDB also supports a
SHOW CREATE statement. For example there is a
SHOW CREATE TABLE that returns the
CREATE TABLE statement that can be used to recreate a table.
Though SQL Server has no way to show the DDL statement to recreate an object,
SHOW CREATE statements are functionally similar to
Other uncategorized syntax differences.
MariaDB has no
BULK INSERT statement. Instead, it supports:
LOAD DATA INFILEto load data from files in CSV or similar formats;
LOAD XML INFILEto load data from XML files.