Data Types in MariaDB

MariaDB Data Types

A data type represents the type of the data that can be stored and processed. Some common data types include integer, floating point, string, boolean, etc. A data type also specifies the type of operations that can be performed on that type.

MariaDB supports a lot of the standard data types of SQL. These data types are divided into various categories, but most of them fall into three main categories viz. Numeric data type, date and time data type, large object data type and string data type.

 

Numeric Data Types:

There are mainly eighteen numeric data types that MariaDB supports. These are listed below:

Data Type Range Maximum width Description
BIT -128 to 127(signed)

0 to 255(unsigned)

4 digits Very small integer values.
INT -2147483648 to 2147483647 (signed)

0 to 4294967295 (unsigned)

11 digits Normal sized integer value.
INTEGER -2147483648 to 2147483647 (signed)

0 to 4294967295 (unsigned)

11 digits Normal sized integer value.
TINYINT -128 to 127(signed)

0 to 255(unsigned)

4 digits Very small integer values.
SMALLINT -32768 to 32767 (signed)

0 to 65535 (unsigned)

5 digits Small integer value.
MEDIUMINT -8388608 to 8388607 (signed)

0 to 16777215 (unsigned)

9 digits Medium sized integer value.
BIGINT -9223372036854775808 to 9223372036854775807 (signed)

0 to 18446744073709551615 (unsigned)

20 digits Large integer value.
FLOAT Single precision floating point number.
DOUBLE Double precision floating point number.
DOUBLE

PRECISION

Double precision floating point number.
DECIMAL Unpacked fixed point number.
DEC Unpacked fixed point number.
NUMERIC Unpacked fixed point number.
FIXED Unpacked fixed point number.
REAL Double precision floating point number.
FLOAT Floating point number.
BOOL Equivalent to TINYINT(1)
BOOLEAN Equivalent to TINYINT(1)

 

Date and Time Data Types:

There are mainly five date and time data types that MariaDB supports. These are listed below:

Data Type Range Format
DATE ‘1000-01-01’ to ‘9999-12-31’ yyyy-mm-dd
DATETIME ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ yyyy-mm-dd hh:mm:ss
TIMESTAMP(m) ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC YYYY-MM-DD HH:MM:SS
TIME ‘-838:59:59’ to ‘838:59:59’ HH:MM:SS
YEAR[(2|4)] 2 digits or 4 digits value 4 digits by default

 

 

String Data Types:

There are mainly eight string data types that MariaDB supports. These are listed below:

Data Type Maximum Size Description
CHAR(size) 255 characters Here, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
VARCHAR(size) 255 characters Here, size is equal to the number of characters to store. It is used for variable-length strings.
TINYTEXT(size) 255 characters Here, size is equal to the number of characters to store.
TEXT(size) 65,535 characters Here, size is equal to the number of characters to store.
MEDIUMTEXT(size) 16,777,215 characters Here, size is equal to the number of characters to store.
LONGTEXT(size) 4GB or 4,294,967,295 characters Here, size is equal to the number of characters to store.
BINARY(size) 255 characters Here, size is equal to the number of binary characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
VARBINARY(size) 255 characters Here, size is equal to the number of binary characters to store. It is used for variable-length strings.

 

 

Large Object (LOB) Data Types:

There are mainly four large object or LOB data types that MariaDB supports. These are listed below:

Data Type Maximum Size
TINYBLOB 255 bytes
BLOB(size) 65,535 bytes
MEDIUMBLOB 16,777,215 bytes
LONGTEXT 4GB or 4,294,967,295 characters

 

Please Share