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!

Comments (0)


Leave a comment