Updated April 19, 2023
Introduction to MariaDB Data Types
It is necessary that the database that you are designing should be optimized and efficient. For creating a perfect database, the tables and the structure that is defined should be normalized and most importantly the fields of the tables that are columns should have the proper data type. The data type of the field should be defined in such a manner that it should be able to carry the values that are necessary. For this, the type of the value and the length of the field should be chosen properly. The datatypes in Maria DB are broadly classified into three different types numeric, date and time, and string values.
In this article, we will study different types of numeric, date and time and string related data types, their capacity, and the maximum capacity that the field can have when assigned that particular data type and when we can use those data types depending on the requirement. When we design the fields and give them the appropriate size and type of data type, our database will be optimized.
Types of MariaDB Data Types
Let us discuss types of MariaDB Data Types.
Numeric Datatypes
In MariaDB we have the following data types of numeric type that are supported –
- TINYINT– We can store small integer values in this type of data type. The range of the tiny int data type is -128 to 127 for the signed tiny int datatype and 0 to 255 for an unsigned datatype.
- BOOLEAN– We can store a value that can have any one of the two values in this datatype of values where 0 corresponds to false while 1 corresponds to true.
- SMALLINT− We can store small integer values in this type of data type. The range of the small int data type is -32768 to 32768 for the signed small int datatype and 0 to 65535 for an unsigned datatype.
- MEDIUMINT− We can store integer values in this type of data type where a range of the data type is -8388608 to 8388607 for the signed datatype and 0 to 16777215 for unsigned datatype.
- INT(also INTEGER)− We can store normal integer values in this type of data type. The range of the int data type is -2147483648 to 2147483647 for the signed int datatype and 0 to 4294967295 for the unsigned datatype.
- BIGINT− We can store big integer values in this type of data type. The range of the int data type is -9223372036854775808 to 9223372036854775807 for the signed int datatype and 0 to 18446744073709551615 for the unsigned datatype.
- DECIMAL – This type of datatype helps us to store the fixed-point numbers of precision which is also called NUMERIC, DEC, or FIXED. The M stands for the number of the digits that are allowed in the number while D helps to specify the number of places allowed after the decimal point. The default values of M will be 10 while that of D is kept as 0 when not specified or ommitted. The maximum permissible values for M and D are 65 digits before the decimal point and 30 places after the decimal point.
- FLOAT− We can store small floating-point values in this type of data type where allowed values of the data type are 0,-3.402823466E+38 to -1.175494351E-38, and 1.175494351E-38 to 3.402823466E+38.
- DOUBLE– This type of datatype helps us to store the fixed-point numbers of precision which is also called as REAL and DOUBLE PRECISION. Allowed values of the data type are 0, -1.7976931348623157E+308 to -2.2250738585072014E-308 or 2.2250738585072014E-308 to 1.7976931348623157E+308
- BIT– We can store bit values in this type of datatypes. The M that is given while specifying the BIT datatype stands for the number of bit fields. The default value of M for BIT data type is 1. The values of this data type are represented in the format b’[value]’” where values are presented in the format of 0s and 1s. When used for a full length, there is automatically zero-padding being made from the left side. For example when we specify 101 then it automatically converts to 0101.
Date and Time Data Types
In MariaDB we have the following data types of Date and Time type that are supported –
- DATE– The format supported by this data type is “YYYY-MM-DD” and the range of the values supported by this datatype “1000-01-01” to “9999-12-31.
- TIME− The range of the values supported by this datatype “-838:59:59.999999” to “838:59:59.999999.” and is used for storing the time.
- DATETIME− The format supported by this data type is “YYYY-MM-DD HH:MM:SS” and the range of the values supported by this datatype “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.”
- TIMESTAMPThe format supported by this data type is “YYYY-MM-DD HH:MM:SS” and is mainly used to keep the time details for all the modifications made to the database which includes the insertion and updation of the values of the database.
- YEAR− The format supported by this data type is “YYYY” and the range of the values supported by this datatype 0000 and 1901 to 2155.
String DataTypes
In MariaDB, we have the following data types of String type that are supported –
- String literals– We can store the sequence of characters closed between the two double quotes in this type of value.
- CHAR− The string values stored in this type of datatype includes spaces in it and are of fixed length and usually right padded. The length of the column characters is represented by the M whose range is between 0 to 255. When not specified the default value of M is 1.
- VARCHAR– We can store variable-length strings in this datatype in which the column length can have a range of values between 0 to 65535.
- BINARY– The column length specified in M for this datatype is in bytes and this datatype is mostly used for storing the binary byte strings.
- BLOB– The maximum length of this type of data type is 65,535 (2^16 – 1) bytes and is used to store the BLOB values.
- TEXT− The maximum length of this type of datatype is 65,535 (2^16 – 1) bytes
- ENUM– The datatype can be used when we want to have only a single value is stored in the column from the list of the specified values.
- SET– We can store zero or more values from the list of the values with a maximum number of 64 members being stored at a time in the string format. However, these values are internally represented as integer values.
Conclusion
In MariaDB, we have different categories of numeric, date, and time and string-related datatypes that are being supported and have a different range, size, and purpose of use.
Recommended Articles
This is a guide to MariaDB Data Types. Here we discuss the Introduction, types of MariaDB Data Types respectively. You may also have a look at the following articles to learn more –
