SQL Server and MariaDB Types Comparison
Contents
This page helps to map each SQL Server type to the matching MariaDB type.
Numbers
In MariaDB numeric types can be declared as SIGNED
or UNSIGNED
. By default numeric columns are SIGNED
, so not specifying either will not break compatibility with SQL Server.
For more information see Numeric Data Type Overview.
Integer Numbers
SQL Server Types | Size (bytes) | MariaDB Types | Size (bytes) | Notes |
---|---|---|---|---|
tinyint | 1 | TINYINT | 1 | |
smallint | 2 | SMALLINT | 2 | |
MEDIUMINT | 3 | Takes 4 bytes in memory | ||
int | 1 | INT / INTEGER | 4 | |
bigint | 8 | BIGINT | 8 |
Real Numbers (approximated)
SQL Server Types | Precision | Size | MariaDB Types | Precision | Size | Notes |
---|---|---|---|---|---|---|
float(1-24) | 7 digits | 4 | FLOAT(0-23) | 4 | ||
float(25-53) | 15 digist | 8 | FLOAT(24-53) | 8 |
MariaDB supports an alternative syntax: FLOAT(M, D)
. M is the total number of digits, and D is the number of digits after the decimal point.
See also: Floating-point Accuracy.
Aliases
In SQL Server real
is an alias for float(24)
.
In MariaDB DOUBLE
, and DOUBLE PRECISION
are aliases for FLOAT(24-53)
.
Normally, REAL
is also a synonym for FLOAT(24-53)
. However, the sql_mode variable can be set with the REAL_AS_FLOAT
flag to make REAL
a synonym for FLOAT(0-23)
.
Real Numbers (exact)
SQL Server Types | Precision | Size (bytes) | MariaDB Types | Precision | Size (bytes) |
---|---|---|---|---|---|
decimal | 0 - 38 | Up to 17 | DECIMAL | 0 - 38 | See table |
MariaDB supports this syntax: DECIMAL(M, D)
. M and D are both optional. M is the total number of digits (10 by default), and D is the number of digits after the decimal point (0 by default). In SQL Server, defaults are 18 and 0, respectively. The reason for this difference is that SQL standard imposes a default of 0 for D, but it leaves the implementation free to choose any default for M.
SQL Server DECIMAL
is equivalent to MariaDB DECIMAL(18)
.
Aliases
The following aliases for DECIMAL
are recognized in both SQL Server and MariaDB: DEC
, NUMERIC
. MariaDB also allows to use FIXED
.
Money
SQL Server money
and smallmoney
types represent real numbers guaranteeing a very low level of approximation (five decimal digits are accurate), optionally associated to one of the supported currencies.
MariaDB doesn't have monetary types. To represent amounts of money:
- Store the currency in a separate column, if necessary. It's possible to use a foreign key to a currencies table, or the
ENUM
type. - Use a non-approximated type:
DECIMAL
is very convenient, as it allows to store the number as-is. But calculations are potentially slower.- An integer type is faster for calculations. It is possible to store, for example, the amount of money multiplied by 100.
Bits
The BIT
type is supported in MariaDB. Its maximum size is BIT(64)
. The BIT
type has a fixed length. If we insert a value which requires less bits than the ones that are allocated, zero-bits are padded on the left.
In MariaDB, binary values can be written in one of the following ways:
b'value'
0value
wherevalue
is a sequence of 0 and 1 digits. Hexadecimal syntax can also be used. For more details, see Binary Literals and Hexadecimal Literals.
MariaDB and SQL Server have different sets of bitwise operators. See Bit Functions and Operators.
BOOLEAN pseudo-type
In SQL Server, it is common to use bit
to represent boolean values. In MariaDB it is possible to do the same, but this is not a common practice.
A column can also be defined as BOOLEAN
or BOOL
, which is just a synonym for TINYINT
. TRUE
and FALSE
keywords also exist, but they are synonyms for 1 and 0. To understand what this implies, see Boolean Literals.
In MariaDB 'True'
and 'False'
are always strings.
Date and Time
SQL Server Types | Range | Precision | Size (bytes) | MariaDB Types | Range | Size (bytes) | Precision | Notes |
---|---|---|---|---|---|---|---|---|
date | 0001-01-01 - 9999-12-31 | 3 | / | DATE | 0001-01-01 - 9999-12-31 | 3 | / | They cover the same range |
datetime | 1753-01-01 - 9999-12-31 | 8 | 0 to 3, rounded | DATETIME | 001-01-01 - 9999-12-31 | 8 | 0 to 6 | MariaDB values are not approximated, see below. |
datetime2 | 001-01-01 - 9999-12-31 | 8 | 6 to 8 | DATETIME | 001-01-01 - 9999-12-31 | 8 | 0 to 6 | MariaDB values are not approximated, see below. |
smalldatetime | DATETIME | |||||||
datetimeoffset | DATETIME | |||||||
time | TIME |
You may also consider the following MariaDB types:
TIMESTAMP
has little to do with SQL Server'stimestamp
. In MariaDB it is the number of seconds elapsed since the beginning of 1970-01-01, with a decimal precision up to 6 digits (0 by default). The value can optionally be automatically set to the current timestamp on insert, on update or both. It is not meant to be a unique row identifier.YEAR
is a 1-byte type representing years between 1901 and 2155, as well as 0000.
Zero values
MariaDB allows a special value where all the parts of a date are zeroes: '0000-00-00'
. This can be disallowed by setting sql_mode=NO_ZERO_DATE
.
It is also possible to use values where only some date parts are zeroes, for example '1994-01-00'
or '1994-00-00'
. These values can be disallowed by setting sql_mode=NO_ZERO_IN_DATE
. They are not affected by NO_ZERO_DATE
.
See sql_mode for more information about this server variable.
Syntax
Several different date formats are understood. Typically used formats are 'YYYY-MM-DD'
and YYYYMMDD
. Several separators are accepted.
The syntax defined in standard SQL and ODBC are understood - for example, DATE '1994-01-01'
and {d '1994-01-01'}
. Using these eliminates possible ambiguities in contexts where a temporal value could be interpreted as a string or as an integer.
See Date and Time Literals for the details.
Precision
For temporal types that include a day time, MariaDB allows a precision from 0 to 6 (microseconds), 0 being the default. The subsecond part is never approximated. It adds up to 3 bytes. See Data Type Storage Requirements for the details.
String and Binary
Binary Strings
SQL Server Types | Size (bytes) | MariaDB Types | Notes |
---|---|---|---|
binary | 1 to 8000 | VARBINARY or BLOB | See below for BLOB types |
varbinary | 1 to 8000 | VARBINARY or BLOB | See below for BLOB types |
image | 2^31-1 | VARBINARY or BLOB | See below for BLOB types |
The VARBINARY
type is similar to VARCHAR
, but stores binary byte strings, just like SQL Server binary
does.
For large binary strings, MariaDB has four BLOB
types, with different sizes. See BLOB and TEXT Data Types for more information.
Character Strings
One important difference between SQL Server and MariaDB is that in MariaDB character sets do not depend on types and collations. Character sets can be set at database, table or column level. If this is not done the default character sets applies, which is specified by the character_set_server system variable.
To create a MariaDB table that is identical to a SQL Server table, it may be necessary to specify a character set for each string column. However, in many cases using UTF-8 will work.
SQL Server Types | Size (bytes) | MariaDB Types | Size (bytes) | Character set |
---|---|---|---|---|
char | 1 to 8000 | CHAR | 0 to 255 | utf8mb4 (1) |
varchar | 1 to 8000 | VARCHAR | 0 to 65,532 (2) | utf8mb4 (1) |
text | ||||
nchar | ||||
nvarchar | ||||
tnext |
Notes:
1) If SQL Server uses a non-unicode collation, a subset of UTF-8 is used. So it is possible to use a smaller character set on MariaDB too.
2) InnoDB has a maximum row length of 65,535 bytes. TEXT
columns do not contribute to the row size, because they are stored separately (except for the first 12 bytes).
Special Types
rowversion
MariaDB does not have the rowversion
type.
If the only purpose is to check if a row has been modified since its last read, a TIMESTAMP
column can be used instead. Its default value should be ON UPDATE CURRENT_TIMESTAMP
. In this way, the timestamp will be updated whenever the column is modified.
A way to preserve much more information is to use a temporal table. Past versions of the row will be preserved.
sql_variant
MariaDB does not support the sql_variant
type.
MariaDB is quite flexible about implicit and explicit type conversions. Therefore, for most cases storing the values as a string should be equivalent to using sql_variant
.
uniqueidentifier
MariaDB does not support the uniqueidentifier
type.
uniqueidentifier
columns contain 16-bit GUIDs. MariaDB can generate unique values with the UUID()
or UUID_SHORT()
functions, and stored them in BIT(128)
or BIT(64)
columns, respectively.
xml
MariaDB does not support the xml
type.
XML data can be stored in string columns. MariaDB supports several XML functions.