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.
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.
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.
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”.
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.
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.
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.
In this guide, we explained what are MariaDBdata 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.
I am a fan of open source technology and have more than 10 years of experience working with Linux and Open Source technologies. I am one of the Linux technical writers for Cloud Infrastructure Services.
00votes
Article Rating
Subscribe
Login and comment with
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
DisagreeAgree
Login and comment with
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.