MariaDB - Data Types

Good field definitions are essential for the optimization of your database. The ideal approach requires that you exclusively use a field of the type and size needed. For example, if you will only use a field, five-characters wide, do not define a field, 20-characters wide. Field (or column) types are also known as data types given the data types stored within the field.

MariaDB data types can be categorized as numeric, date and time, and string values.

Numeric Data Types

The numeric data types supported by MariaDB are as follows −

TINYINT − This data type represents small integers falpng within the signed range of -128 to 127, and the unsigned range of 0 to 255.

BOOLEAN − This data type associates a value 0 with “false,” and a value 1 with “true.”

SMALLINT − This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.

MEDIUMINT − This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.

INT(also INTEGER) − This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.

BIGINT − This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.

DECIMAL( also DEC, NUMERIC, FIXED)− This data type represents precise fixed-point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.

FLOAT − This data type represents a small, floating-point number of the value 0 or a number within the following ranges −

-3.402823466E+38 to -1.175494351E-38

1.175494351E-38 to 3.402823466E+38

DOUBLE (also REAL and DOUBLE PRECISION) − This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges −

-1.7976931348623157E+308 to -2.2250738585072014E-308

2.2250738585072014E-308 to 1.7976931348623157E+308

BIT − This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be appped with “ b’[value]’” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

The date and time data types supported by MariaDB are as follows −

DATE − This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.

TIME − This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”

DATETIME − This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.

TIMESTAMP − This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detaipng the time of database modifications, e.g., insertion or update.

YEAR − This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String DataTypes

The string type values supported by MariaDB are as follows −

String pterals − This data type represents character sequences enclosed by quotes.

CHAR − This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.

VARCHAR − This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.

BINARY − This data type represents binary byte strings, with M as the column length in bytes.

VARBINARY − This data type represents binary byte strings of variable length, with M as column length.

TINYBLOB − This data type represents a blob column with a maximum length of 255 (28 - 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

BLOB − This data type represents a blob column with a maximum length of 65,535 (216 - 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

MEDIUMBLOB − This data type represents a blob column with a maximum length of 16,777,215 (2²⁴ - 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

LONGBLOB − This data type represents a blob column with a maximum length of 4,294,967,295(2³² - 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

TINYTEXT − This data type represents a text column with a maximum length of 255 (2⁸ - 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

TEXT − This data type represents a text column with a maximum length of 65,535 (2¹⁶ - 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

MEDIUMTEXT − This data type represents a text column with a maximum length of 16,777,215 (2²⁴ - 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

LONGTEXT − This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (2³² - 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

ENUM − This data type represents a string object having only a single value from a pst.

SET − This data type represents a string object having zero or more values from a pst, with a maximum of 64 members. SET values present internally as integer values.