What are MariaDB Data Types (Numeric, Date, String) Explained

What are MariaDB Data Types Explained. It is imperative for you to design a database that is highly effective and perfectly optimized. And for designing a perfect database, the defined tables, and the structures must be normalized, and the columns of the table must have the perfect data type. The data type must be specified to carry all required values. To achieve all this, it is essential for you to choose the kind of value and the field length perfectly. 

Also Read 

MariaDB vs PostgreSQL – What’s the Difference? (Pros and Cons)

What is MariaDB

 MariaDB is a fork of MySQL, which is an SQL database. It is a relational database, that manages relationships between data items in a set of tables, columns and rows. It is used for inventory, transaction management and customer information.

Also Read 

How to Create User in MariaDB/MySQL in Linux and Windows Tutorial

MariaDB Benefits

1. Tighter security with PAM, LDAP authentication and Database encryption.
2. Faster Performance with column storage and  better storage engines.
3. Supports Big Data Operations with SQL included.
4. Cloud based Storage options. 
5. Data Imports with Kafka, Python or Java.
6. Easy to install as stand alone server or alongside Hybrid processing platform.

Data Types in MariaDB

The datatypes in MariaDB are mainly classified into three major kinds.

• Numeric.
• Date and time.
• String values.

Prior to designing the database in MariaDB, you must take into consideration all available data kinds so that you can choose the most ideal ones for data storage. The offer with MariaDB is various data types, and each data types have following properties:

• The value that it represents.
• The size of the storage.
• The values that can be indexed or not.
• How values can be compared.

Let’s find out about MariaDB data types. We can see the maximum capacity that any field can have when when specific data type is assigned and when can we use that data based on the requirement. Our database is optimized when we design fields and offer them the required size and kind of data.

Also Read

How to setup MariaDB on Windows in Azure/AWS/GCP

MariaDB Numeric Datatypes

MariaDB consist of the following kind of numeric data type:

TINYINT

We can keep a small integer value in such kind of data type. The range of tinyint data type is between -128 to 127 for the signed data type and 0 – 255 for the unsigned data type.

BOOLEAN

The type of numeric data mainly associates a value 0 with false and a value 1 with true.

SMALLINT

This data type mainly specifies integers of -32768 to 32768 for the signed range and 0 – 65535 for the unsigned range.

MEDIUMINT

This data kind mainly specifies integers in a range of -8388608 – 8388607 for the signed range and 0- 16777215 for the unsigned range.

INT or INTEGER

This type of data mainly signifies an integer of normal Size. Coming to ranges, for unsigned, the range is between 0 – 4294967295. For signed, the range is between -2147483648 to 2147483647. When a column is set at ZEROFILL, all its values are represented by zeroes for placing M digits in the INT value.

BIGINT

Here, for the signed range, the range of the integer is between 9223372036854775808 to 9223372036854775807, and for the unsigned one, the range is between 0 to 18446744073709551615.

DECIMAL – (m,d) also denoted as DEC, NUMERIC, FIXED

This data kind mainly specifies fixed point numbers with M representing digits and D representing the digits after the decimal. The M value will not add “-“or any decimal point. When D is set to zero, there is no appearance of a decimal or fraction part, and the value is rounded to the nearest decimal on INSERT. The maximum digit permitted is 65, and for decimals, it is 30. The default value for M on omission is 10, and 0 for D on omission.

Numeric –(m,d)

It is an unpacked fixed point number where m defaults to 10 when nothing is specified and d defaults to 0 when nothing is specified. Here m is the total number of digits and d is the total number of digits after the decimal. This is the synonym for the decimal datatype.

Also Read 

How to Setup MariaDB Ubuntu Cloud in Azure/AWS/GCP

FLOAT

This data kind mainly represents a minor floating point number of the value 0 or any number in the following range:

• 3.402823466E+38 to -1.175494351E-38
• 1.175494351E-38 to 3.402823466E+38

DOUBLE, also called REAL and DOUBLE PRECISION

This type of data mainly specifies the normal size, with a floating point number of value 0 or in the following range

• -1.7976931348623157E+308 to -2.2250738585072014E-308
•  2.2250738585072014E-308 to 1.7976931348623157E+308

BIT

This data type mainly represents the bit field where M specifies the number of bits per value. When M is omitted, the default is 1. Bit values are applied with “b,” where value represents bit value in 0s and 1s. ZERO padding mainly occurs inevitably from the left for full length. One of the examples is “102” is “00102”.

Also Read 

MariaDB vs MySQL Performance Differences (Pros and Cons)

Date and Time Data Types with MariaDB

MariaDB consist of the following data and time datatypes:

DATE

This is a data type in MariaDB that mainly specifies a date range of 1000-01-01 to “9999-12-31” and makes use of the “YYYY-MM-DD” format for the date.

TIME

This data type signifies a time that ranges between “-838:59:59.999999” to “838:59:59.999999.”

DATETIME

This data category denotes the series “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the format “YYYY-MM-DD HH:MM: SS” to denote the date.

TIMESTAMP

This data type characterizes a timestamp format of the “YYYY-MM-DD HH:MM:DD” format. It is mainly used in specifying the time of database alterations, e.g., any kind of insertion or any kind of update.

YEAR

This data type denotes a year in a 4 digit format. This four digit format permits values that range between 1901 to 2155 and 0000.

Also Read 

How to Setup MariaDB Replication Tutorial (Step by Step)

MariaDB String Data Types

MariaDB supports the following string data type values

String literals

The type of under MariaDB mainly signifies character sequences bounded by quotes.

CHAR

With this data type it signifies a right padded, with a string having a fixed length and containing space of some specified length. M here represents the column length of certain characters that fall in the range 0 – 255, and the default value is 1.

VARCHAR

Mainly signifies a string with varying lengths, having an M range or maximum column length of 0 to 65535 of this data.

BINARY

A strings of binary bytes with M as the column length in bytes is represented with this data type in MariaDB.

VARBINARY

It is binary byte strings of various lengths, with M denoting the column length.

TINYBLOB

Signifies blob columns with a maximum length of 255, that is, 20 – 1 bytes. In storage, each of them makes use of a two byte length prefix that indicates the number of bytes in the value.

BLOB

A blob column with a maximum length of about 65535 bytes. While in storage, each of them can use a 2 byte length prefix that indicates the byte quantity in the value.

MEDIUMBLOB

An extreme length of 16777215 bytes characterizes a blob column with this data type. In storage, each of them makes use of a three byte length prefix that indicates the quantity of byte in value.

LONGBLOB

A data type that represents a blob column having a maximum length of about 4,294,967,295(232 – 1) bytes. Each of them in storage makes use of a prefix that is of length four bytes, thereby indicating the byte quantity in value.

TINY TEXT

With MariaDB represents a text column with a maximum length of about 255 characters. In storage, each column utilizes a one byte length prefix that indicates the number of bytes in the value.

Also Read 

How to Install MariaDB on Docker Container (Step by Step)

TEXT

Specifies a text column having a maximum length of about 65,535 characters. In storage, each of them makes use of the prefix of two byte that indicates the quantity of byte in value.

MEDIUM TEXT

Signifies a text column having a maximum length of about 16777215 characters. In storage, each of them makes use of a prefix having a length of three bytes which indicates the number of bytes in the value.

LONGTEXT

In here, this data type mainly signifies a text column having a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. In storage, each uses a four byte length prefix that indicates the byte quantity in the value.

ENUM

A string object possessing only a single value from the list is signified in this data type.

SET

This is a data type that signifies a string object with zero or more values from the list with a maximum of 64 members. SET values represent internally as integer values.

Besides all these data types, MariaDB also consists of Large Object Datatypes.

TINYBLOB

A data typethat possesses a maximum size of about 255 bytes.

BLOB

Here a maximum size of 65,535 bytes is present in this data type. Here size mainly denotes the total number of characters it can store.

MEDIUMBLOB

It has a maximum size of 16,777,215 bytes.

LONGTEXT

It possesses a maximum size of 4 GB or 4294967295 characters.

Also Read 

Top 20 Best MySQL Alternatives for Databases (Pros and Cons)

What are MariaDB Data Types Explained Conclusion

In this guide, we explained what are MariaDB data types and various kinds of MariaDB data types with their maximum capacity. I hope this will help you in your application development for context of gathering data.

Thus, we can see that MariaDB has various categories of numeric, date and time, and string based data types supported and possesses varied ranges, sizes, and utilization purposes.

Take a look at our MariaDB content here.