Data Types in MySQL

Data Types in MySQL

Some developers(including me) do not remember by heart all types of commonly used fields in MySQL, so I decided to make this memo, really useful information.

Integer data types

Type	Byte	Range
TINYINT	1	-128 ... +127
SMALLINT	2	-32 768 ... +32 767
MEDIUMINT	3	-8 388 608 ... +8 388 607
INT	4	-2 147 483 648 ... +2 147 483 647
BIGINT	8	-9 223 372 036 854 775 808 ... +9 223 372 036 854 775 807

(Important) Many developers set type as INT(11), and think that if you write INT(2), the field will occupy less space and can contain a maximum of two-digit number, IT IS NOT TRUE!
The number in parentheses after the type field indicates only the number of characters that will be returned from the database if the field attribute is specified ZEROFIL or UNSIGNED ZEROFILL, this attribute adds zeros to the return number to the value indicated in parentheses after the field type, for example, if a field is declared as INT (8) ZEROFIL and added value of 666, then the SELECT this field returns the value "00000666".

Remember that if you specify a numeric field attribute [UNSIGNED], then the field can not contain negative values, but the maximum value is TWICE as much!

Floating-point numbers

Type	Description	Range
FLOAT	Single precision. Can not be unsigned.	–3.402823466E+38 ... –1.175494351E-38
DOUBLE, DOUBLE PRECISION	Double precision	-1.7976931348623157E+308 ... -2.2250738585072014E-308
REAL	Same as DOUBLE
DECIMAL	Fractional number stored as a string	Works like a data type CHAR, the number is stored as a string, using one character for each digit value. Symbol of the decimal point and a negative number symbol "-" is not counted in length. If a decimal value is 0, the value will not have a decimal point or fractional part. The maximum size for DECIMAL value is the same as for DOUBLE, but the actual range for the DECIMAL column may be limited in the choice of the length and decimals.
NUMERIC	Same as DECIMAL

Following format: TYPE(LENGTH, SIGNS). Length - the number of digits before the decimal point, Signs - number of digits after the decimal point.

Strings

Type	Range	Description
VARCHAR	0 ... 255 (since 5.0.3 is 0 ... 65 535)	Occupies exactly as many bytes as characters stored in the value of +1 byte to store the number of characters occupied. In parenthesis indicates the maximum number of characters stored.
CHAR	0 ... 255	Occupies exactly as many bytes as indicated in parentheses when creating the field. Unlike VARCHAR, always takes the same number of bytes, and does not take one extra byte to store the length value.
TINYTEXT	255 characters	TINYBLOB - case-sensitive alternative.
TEXT	65 535 characters	BLOB - case-sensitive alternative.
MEDIUMTEXT	16 777 215 characters	MEDIUMBLOB - case-sensitive alternative.
LONGTEXT	4 294 967 295 characters	LONGBLOB - case-sensitive alternative.

When searching for these types of string data is not case sensitive, to insensitive need during field declaration set attribute BINARY (for types VARCHAR and CHAR), or use of alternative from descriptions are case sensitive.

Date and time

Type	Description
DATE	Date in the format YYYY-MM-DD
TIME	Time in the format HH:MM:SS
TIMESTAMP	Date and time (stored in the format of timestamp), is displayed in the format YYYY-MM-DD HH:MM:SS
DATETIME	Date and time in the format YYYY-MM-DD HH:MM:SS

If you have questions - write in the comments, I will answer all!