May 16, 2021 MariaDB
A good field definition is critical to optimizing the database. T he ideal approach requires you to specialize in fields of the type and size you want. F or example, if you use only one field, five characters wide, do not define a field that is 20 characters wide. A field (or column) type is also known as a data type for a given data type stored in a field.
MariaDB data types can be divided into numbers, dates and times, and string values.
The digital data types supported by MariaDB are as follows -
TINYINT - This data type represents a small integer falling into the signed range of -128 to 127, and an unsigned range of 0 to 255.
BOOLEAN - This data type associates a value of 0 with "false" and a value of 1 with "true".
SMALLINT - This data type represents an integer in the signed range of -32768 to 32768, and an unsigned range of 0 to 65535.
MEDIUMINT - This data type represents an integer in the signed range of -8388608 to 8388607, and an unsigned range of 0 to 16777215.
INT (also INTEGER) - This data type represents an integer of normal size. W hen marked as unsigned, the range spans from 0 to 4294967295. When signed (default), the range spans -2147483648 to 2147483647. When the column is set to ZEROFILL (unsigned state), all its values are added from zero to M numbers in the INT value.
BIGINT - This data type represents an integer in the signed range 9223372036854775808 to 9223372036854775807, and an unsigned range of 0 to 18446744073709551615.
DECIMAL (DEC, NUMERIC, FIXED) - This data type represents the exact fixed-point number, M specifies its number, and D specifies the number after the decimal. T he M value does not add "-" or "a few points." I f D is set to 0, no small or small parts appear, and the value is rounded to the nearest DECIMAL INSERT. T he maximum allowable bit is 65, the maximum number of scales is 30. The default value of M is 10, and D is 0 when omitted.
FLOAT - This data type represents a small floating point with a value of 0 or a number in the range below -
-3.402823466E - 38 to -1.175494351E-38
1.175494351E-38 to 3.402823466E plus 38
DOUBLE (also REAL and DOUBLE PRECISION) - This data type represents a floating point of the normal size of the value 0, or a value in the range below -
-1.7976931348623157E , 308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E and 308
BIT - This data type represents a bit field, and M specifies the number of bits for each value. W hen M is omitted, the default value of 1.bits can be applied by "b'value", where values represent bit values in 0 and 1. Z ero fill automatically occurs full length from the left; F or example, "10" becomes "0010".
MariaDB supports date and time data types as follows -
DATE - This data type represents the date range "1000-01-01" to "9999-12-31" and uses the "YYYY-MM-DD" date format.
TIME - This data type represents the time range from "-838:59:59.999999" to "838:59:59.999999".
DATETIME - This data type represents the range "1000-01-01 00:00:00.00000" to "9999-12-31 23:59:59.999999". I t uses the "YYYY-MM-DD HH:MM:SS" format.
TIMESTAMP - This data type represents a timestamp in the "YYYY-MM-DD HH:MM:DD" format. I t is primarily used to describe in detail when a database is modified, such as insertion or update.
YEAR - This data type represents the year in the 4-digit format. T he four-digit format allows values in the range of 1901 to 2155 and 0000.
The string type values supported by MariaDB are as follows -
String literals - This data type represents a sequence of characters enclosed in quotation marks.
CHAR - This data type represents a string of fixed lengths to the right that contains spaces of a specified length. M represents the column length of the character, with a value range of 0 to 255 and a default value of 1.
VARCHAR - This data type represents a variable length string with an M range (maximum column length) from 0 to 65535.
BINARY - This data type represents a binary byte string, with M as the column length in bytes.
VARBINARY - This data type represents a variable-length binary byte string with M as the column length.
TINYBLOB - This data type represents a blob column with a maximum length of 255 (28 - 1) bytes. I n storage, each uses a byte-length prefix that represents the number of bytes in the value.
BLOB - This data type represents a blob column with a maximum length of 65,535 (216 - 1) bytes. I n storage, each uses a prefix of two byte lengths, which represents the number of bytes in the value.
MEDIUMBLOB - This data type represents a blob column with a maximum length of 16,777,215 (224 - 1) bytes. I n storage, each uses a three-byte-length prefix that represents the number of bytes in the value.
LONGBLOB - This data type represents a blob column with a maximum length of 4,294,967,295 (232 - 1) bytes. I n storage, each uses a prefix of four byte lengths, which represents the number of bytes in the value.
TINYTEXT - This data type represents a text column with a maximum length of 255 (28 - 1). I n storage, each uses a byte-length prefix that represents the number of bytes in the value.
TEXT - This data type represents a text column with a maximum length of 65,535 (216 - 1). I n storage, each uses a prefix of two byte lengths, which represents the number of bytes in the value.
MEDIUMTEXT - This data type represents a text column with a maximum length of 16,777,215 (224 - 1). I n storage, each uses a three-byte-length prefix that represents the number of bytes in the value.
LONGTEXT - This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (232 - 1). I n storage, each uses a prefix of four byte lengths, which represents the number of bytes in the value.
ENUM - This data type represents a string object with only one value in a list.
SET - This data type represents a string object with zero or more values in a list with a maximum of 64 members. S et values exist internally as integer values.