Beginners guide to MySQL and MariaDB

MySQL support different data types which is divided into three categories: string, date and time and numeric types. Data type descriptions has below conventions:

• M indicates maximum display width for integer types. For floating-point and fixed-point types, M is total number of digits that can be stored (the precision). For string types, M is maximum length. The maximum permissible value of M depends on data type.
• D applies to floating-point and fixed-point types and indicates number of digits following the decimal point (the scale). The maximum possible value is 30, but should be no greater than M–2.
• Square brackets (“[” and “]”) indicate optional parts of type definitions.

String Types:

The common string datatypes in MySQL:

• CHAR(M) - A fixed-length string between 1 and 255 characters in length. Defining a length is not required, but default is 1.
• VARCHAR(M) - A variable-length string between 1 and 255 characters in length. You must define a length when creating a VARCHAR field.
• BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files
• TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters.
• MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 16777215 characters.
• LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 4294967295 characters.
• ENUM - An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

Date and Time Types:

The common date and time datatypes in MySQL:

• DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31.
• DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.
• TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037.
• TIME - Stores the time in HH:MM:SS format.
• YEAR(M) - Stores a year in 2-digit or 4-digit format. If length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.

Numeric Data Types:

The common numeric datatypes in MySQL:

• INT - A normal-sized integer can be signed or unsigned. If signed, allowable range is from -2147483648 to 2147483647. If unsigned, allowable range is from 0 to 4294967295. A width of up to 11 digits.
• TINYINT - A very small integer can be signed or unsigned. If signed, allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. A width of up to 4 digits.
• SMALLINT - A small integer can be signed or unsigned. If signed, allowable range is from -32768 to 32767. If unsigned, allowable range is from 0 to 65535. A width of up to 5 digits.
• MEDIUMINT - A medium-sized integer can be signed or unsigned. If signed, allowable range is from -8388608 to 8388607. If unsigned, allowable range is from 0 to 16777215. A width of up to 9 digits.
• BIGINT - A large integer that can be signed or unsigned. If signed, allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, allowable range is from 0 to 18446744073709551615. A width of up to 20 digits.
• FLOAT(M,D) - A floating-point number that cannot be unsigned. This is not required and will default to 10,2, where 2 is number of decimals and 10 is total number of digits (including decimals).
• DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define display length (M) and number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals.
• DECIMAL(M,D) - An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte.