MySQL Column Types and Storage Capabilities
on Sunday 24th May, 2009 Gabe speculated thusly…I am making a note of this for ease of future reference. I can never quite remember whether a medium blob is 16MB or whatever it is, the capabilities are on the MySQL site but I am going to write it in plain english here so I don’t have to keep getting my calculator out to remind me what 2^24 is in terms I am more familiar with…
TinyInt: -128 to 127 (0 to 255 if unsigned).
SmallInt: -32768 to 32767 (0 to 65535 if unsigned).
MediumInt: -8588608 to 8388607 (0 to 16777215 if unsigned).
Int: -2147483648 to 2147483647 (0 to 4294967295 if unsigned).
BigInt: -9223372936854775808 to 9223372036854775807 (0 to18446744073709551615).
Float: 0 and +-1.175494351E-38 to +-3.402823466E+38.
Double: 0 and +-2.2250738585072014-308 to +-1.7976931348623157E+38.
Decimal[(M,D)]: As for DOUBLE but constrained by M and D.
Char(M): M may take any integer value from 0 to 255, with a CHAR(0) column able to store only two values: NULL and ” (empty string), which occupy a single bit.
VarChar(M): 1 to 255 (number of characters to store). Trailing spaces are stripped before storage.
Text type columns do case insensitive comparisons and sorts, whereas blobs are case sensitive.
TinyBlob/TinyText: Max. length 255 characters. Very similar to VarChar but trailing spaces are not stripped before storage.
Text/Blob: Max. length 65535 characters (65KB).
MediumBlob/MediumText: Max. length 6777215 characters (16.8MB).
LongBlob/LongText: Max. length 4294967295 characters (4.3GB).
Enum: One value chosen from up to 65535 possibilities.
Set: Up to 64 values in a given set column.
Date: ‘1000-01-01′ to ‘9999-12-31′, and ‘0000-00-00′.
Time: ‘-838:59:59′ to ‘838:59:59′.
DateTime: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′.
Year: 1901 to 2155, and 0000.
TimeStamp: 19700101000000 to sometime in 2037 on current systems.