Types of data in MySQL (compressed reference for a PHP programmer)

What and how and how much does MySQL store?

This material was created specifically for programmers who can quickly determine which type of data is best to choose to store values ​​in the MySQL database.

For starters, an interesting quote from the manual for MySQL:
“The maximum size of an entry in MyISAM is 65534 bytes. Each BLOB or TEXT column is counted here as 5-9 bytes. ”- it is not clear how to interpret this. BUT it is clear that many-primal columns in the table on poke. "Perverted programmers" - be careful (you can not exactly create 66000 columns).
')
UPD: If you find an error, or I hide something somewhere - write in the comments, I’ll add with pleasure.

UPD1 First of all (and the best option for beginners) to read the OFFICIAL MANUAL dev.mysql.com/doc/refman/5.0/en/data-types.html (thanks to Psyh for the direct link), and here is a cut for META data handlers ( both in the face of programmers and in the face of machine processing).

UPD2 In principle, all that is written below can be read at www.mysql.ru/docs/man/Column_types.html (for the link of the “Russian translation”, thanks artuska ).

UPD3 Another good link provided 4all : newcontinent.ru/h/mysqlc (material in Russian)

UPD4 Quote from comments from egorF :
# 14 “As the chief editor of the Russian translation of the docks in MySQL, I recommend not to look at it - it is fabulously morally obsolete.”



The following array is quite understandable to PHP programmers.
Anyway, any self-respecting programmers will understand this.

For example:
'int' => Array ('byte' => 4, 'min' => - 2147483648, 'max' => 2147483647, 'umin' => 0, 'umax' => 4294967295),

It is treated as follows:
The INT field type uses 4 bytes to store the value. The minimum value is -2147483648, the maximum is 2147483647. Unsigned INT (unsigned) stores values ​​from 0 to 4294967295.

Used as follows:
I need to keep the maximum numeric value of 234,259,000,000,000 in the field.
INT - not suitable for this. We look at other types and find that BIGINT is fine.

$ MYSQL_TYPES = Array (
// INTEGER
// byte - number of bytes in storage,
// max / min - limit values
// umax / umin - unsigned limit values
'int' => Array ( 'byte' => 4, 'min' => - 2147483648, 'max' => 2147483647, 'umin' => 0, 'umax' => 4294967295),
'bigint' => Array ( 'byte' => 8, 'min' => - 9223372036854775808, 'max' => 9223372036854775807, 'umin' => 0, 'umax' => 18446744073709551615),
'tinyint' => Array ( 'byte' => 1, 'min' => - 128, 'max' => 127, 'umin' => 0, 'umax' => 255),
'smallint' => Array ( 'byte' => 2, 'min' => - 32768, 'max' => 32767, 'umin' => 0, 'umax' => 65535),
'mediumint' => Array ( 'byte' => 3, 'min' => - 8388608, 'max' => 8388607, 'umin' => 0, 'umax' => 16777215),

// DECIMAL DECIMAL (M, D) m - number of digits (max 65 digits), d - how many of them can be after comma
// min_byte / max_byte - boundary values ​​of the field size in bytes,
// byte_formula - dimension calculation formula
// length - maximum number of digits
'decimal' => Array ( 'min_byte' => 2, 'max_byte' => 67, 'byte_formula' => '(D == 0? (M + 1) :( M + 2)' , 'length' = > 65)
'dec' => Array ( 'min_byte' => 2, 'max_byte' => 67, 'byte_formula' => 'D == 0? (M + 1) :( M + 2)' , 'length' => 65)
'numeric' => Array ( 'min_byte' => 2, 'max_byte' => 67, 'byte_formula' => 'D == 0? (M + 1) :( M + 2)' , 'length' => 65)

// FLOAT DOUBLE
// Attention! Do not store cash values ​​in these fields !!! Money must be stored - in DECIMAL
// FLOAT ACCURACY ONLY 7 DIGITS !!! (all other numbers are “smeared”)
// DOUBLE ACCURACY ONLY 15 DIGITS !!! (all other numbers are “smeared”)
// byte - number of bytes to store the field (by default)
// max_byte - the maximum number of bytes to store
// negative_min / negative_max - minmax for negative numbers
// positive_min / positive_max - minmax for positive numbers
'float' => Array ( 'byte' => 4, 'max_byte' => 8, 'negative_min' => - 3.402823466E + 38, 'negative_max' => - 1.175494351E-38, 'positive_min' => 1.175494351E -38, 'positive_max' => 3.402823466E + 38)
'double' => Array ( 'byte' => 8, 'negative_min' => - 1.7976931348623157E + 308, 'negative_max' => - 2.2250738585072014E-308, 'positive_min' => 2.2250738585072014E-308, 'positive_max' = > 1.7976931348623157E + 308),

// BOOLEAN
// you will understand everything
'bool' => Array ( 'byte' => 1, 'true' => 1, 'false' => 0),
'boolean' => Array ( 'byte' => 1, 'true' => 1, 'false' => 0),

// VARCHAR
// byte - number of bytes allocated for storage (you can set less)
// min_byte - the minimum number of bytes in which the field can be stored (if the length is 1)
// In MYSQL 5.0.3 and higher, VARCHAR can be up to 65,535 characters !!!
// length - the maximum length of characters in the field
'varchar' => Array ( 'byte' => 256, 'min_byte' => 2, 'length' => 255),
'char' => Array ( 'byte' => 256, 'min_byte' => 2, 'length' => 255),

// TEXT
// byte - number of bytes to store the field
// min_byte - the minimum number of bytes to store one character (if the field length is 1)
// length - the maximum number of characters in the field
'tinytext' => Array ( 'byte' => 256, 'min_byte' => 2, 'length' => 255),
'text' => Array ( 'byte' => 65537, 'min_byte' => 3, 'length' => 65535),
'mediumtext' => Array ( 'byte' => 16777218, 'min_byte' => 4, 'length' => 16777215),
'longtext' => Array ( 'byte' => 4294967300, 'min_byte' => 5, 'length' => 4294967296),
'tinyblob' => Array ( 'byte' => 256, 'min_byte' => 2, 'length' => 255),
'blob' => Array ( 'byte' => 65537, 'min_byte' => 3, 'length' => 65535),
'mediumblob' => Array ( 'byte' => 16777219, 'min_byte' => 4, 'length' => 16777215),
'longblob' => Array ( 'byte' => 4294967300, 'min_byte' => 5, 'length' => 4294967296),

// DATETIME
// byte - number of bytes to store the field value
// mask - standard input value mask (there are a lot of other options, read about them in the manual)
// min / max - the minimum maximum values ​​of dates that save fields
'datetime' => Array ( 'byte' => 8, 'mask' => 'YYYY-MM-DD HH: MM: SS' , 'min' => '1000-01-01 00:00:00' , 'max' => '9999-12-31 23:59:59' ),
'date' => Array ( 'byte' => 3, 'mask' => 'YYYY-MM-DD' , 'min' => '1000-01-01' , 'max' => '9999-12- 31 ' )
'time' => Array ( 'byte' => 3, 'min' => '-838: 59: 59' , 'max' => '838: 59: 59' ),
'year' => Array ( 'byte' => 1, 'min' => 1901, 'max' => 2155),
'timestamp' => Array ( 'byte' => 4, 'mask' => Array (14 => 'YYYYMMDDHHMMSS' , 12 => 'YYMMDDHHMMSS' , 10 => 'YYMMDDHHMM' , 8 => 'YYYMMDD' , 6 => 'YYMMDD' , 4 => 'YYMM' , 2 => 'YY' ), 'min' => 1970, 'max' => 2036),

// ENUM
// byte - number of bytes in the storage field
// max_byte - the maximum number of bytes that can be achieved with the maximum number of elements
// max_number_of_element - number of elements that the field can contain
'enum' => Array ( 'byte' => 1, 'max_byte' => 2, 'max_number_of_element' => 65535),
'set' => Array ( 'byte' => 1, 'max_byte' => 8, 'max_number_of_element' => 64)
); * This source code was highlighted with Source Code Highlighter .