MySQL datatypes

Integer data types

Type      Storage Required Signed Range                           Unsigned Range
TINYINT   1 byte           –128 to 127                          0 to 255    
SMALLINT  2 bytes          –32,768 to 32,767                    0 to 65,535
MEDIUMINT 3 bytes          –8,388,608 to 8,388,607              0 to 16,777,215
INT       4 bytes          –2,147,683,648 to 2,147,483,647                          0 to 4,294,967,295
BIGINT    8 bytes          –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807                                       0 to 18,446,744,073,709,551,615

Floating point data types

FLOAT represents single-precision floating-point values that require four bytes each for storage.

DOUBLE represents double-precision floating-point values that require eight bytes each
for storage.

Fixed point data types


DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same
number of decimal places and are stored exactly as given when possible. DECIMAL values are
not processed quite as efficiently as FLOAT or DOUBLE values (which use the processor’s native
binary format), but DECIMAL values are not subject to rounding error, so they are more accurate.
The NUMERIC data type in MySQL is a synonym for DECIMAL.

BIT data type


The BIT data type represents bit-field values. BIT column specifications take a width indicating
the number of bits per value, from 1 to 64 bits.

String data types


Type Description
CHAR Fixed-length non-binary string
VARCHAR Variable-length non-binary string
TEXT Variable-length non-binary string
BINARY Fixed-length binary string
VARBINARY Variable-length binary string
BLOB Variable-length binary string
ENUM Enumeration consisting of a fixed set of legal values
SET Set consisting of a fixed set of legal values

Non Binary sting data types


Type Storage Required Maximum Length
CHAR(M) M characters 255 characters
VARCHAR(M) L characters plus 1 or 2 bytes 65,535 characters (subject to
limitations)
TINYTEXT L characters + 1 byte 255 characters
TEXT L characters + 2 bytes 65,535 characters
MEDIUMTEXT L characters + 3 bytes 16,777,215 characters
LONGTEXT L characters + 4 bytes 4,294,967,295 characters

Binary string data types


Type Storage Required Maximum Length
BINARY(M) M bytes 255 bytes
VARBINARY(M) L bytes plus 1 or 2 bytes 65,535 bytes (subject to limitations)
TINYBLOB L + 1 bytes 255 bytes
BLOB L + 2 bytes 65,535 bytes
MEDIUMBLOB L + 3 bytes 16,777,215 bytes
LONGBLOB L + 4 bytes 4,294,967,295 bytes

ENUM and SET data types


ENUM is an enumeration type. An ENUM column definition includes a list of allowable values;
each value in the list is called a “member” of the list.
The SET data type, like ENUM, is declared using a comma-separated list of quoted strings that

define its valid members. But unlike ENUM, a given SET column may be assigned a value consisting
of any combination of those members.

Temporal data types


Type Storage Required Range
DATE 3 bytes ‘1000-01-01’ to ‘9999-12-31’
TIME 3 bytes ‘-838:59:59’ to ‘838:59:59’
DATETIME 8 bytes ‘1000-01-01 00:00:00’ to
‘9999-12-31 23:59:59’
TIMESTAMP 4 bytes ‘1970-01-01 00:00:00’ to
mid-year 2037
YEAR 1 byte 1901 to 2155 (for YEAR(4)),
1970 to 2069 (for YEAR(2))