phpAsks.com

MySQL Datatype and storage requirements

Posted by: Asif D. Khalyani

MySQL Datatypes & storage requirments

TEXT TYPES
CHAR( )       A fixed section from 0 to 255 characters long.
VARCHAR( )   A variable section from 0 to 255 characters long.
TINYTEXT   A string with a maximum length of 255 characters.
TEXT       A string with a maximum length of 65535 characters.
BLOB       A string with a maximum length of 65535 characters.
MEDIUMTEXT   A string with a maximum length of 16777215 characters.
MEDIUMBLOB   A string with a maximum length of 16777215 characters.
LONGTEXT   A string with a maximum length of 4294967295 characters.
LONGBLOB   A string with a maximum length of 4294967295 characters.

NUMBER TYPES
TINYINT( )   -128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )   -32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )   -8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )   -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )   -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT   A small number with a floating decimal point.
DOUBLE( , )   A large number with a floating decimal point.
DECIMAL( , )   A DOUBLE stored as a string , allowing for a fixed decimal point.

DATE TYPES
DATE       YYYY-MM-DD.
DATETIME   YYYY-MM-DD HH:MM:SS.
TIMESTAMP   YYYYMMDDHHMMSS.
TIME       HH:MM:SS.

MISC TYPES
ENUM ( )   Short for ENUMERATION which means that each column may have one of a specified possible values.
SET       Similar to ENUM except each column may have more than one of the specified possible values.

Data Type     Storage Required
TINYINT     1 byte
SMALLINT     2 bytes
MEDIUMINT     3 bytes
INT, INTEGER     4 bytes
BIGINT         8 bytes
FLOAT(p)     4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT         4 bytes
DOUBLE [PRECISION],REAL 8 bytes
DECIMAL(M,D), NUMERIC(M,D)     Varies; see following discussion
BIT(M)         approximately (M+7)/8 bytes

Data Type     Storage Required
DATE           3 bytes
TIME              3 bytes
DATETIME    8 bytes
TIMESTAMP 4 bytes
YEAR             1 byte

Data Type                 Storage Required
CHAR(M)                  M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
BINARY(M)               M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT     L + 1 bytes, where L < 28
BLOB, TEXT         L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT     L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT     L + 4 bytes, where L < 232
ENUM             1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET             1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Back to Index Page