MySQL Data Types

MySQL uses all the standard ANSI SQL numeric data types. MySQL uses following datatypes.

  • Numeric Data Types
  • String Data Types
  • Date and Time Data Types
  • Spatial Data Types

Numeric Data Types

Type Name Range Storage(Bytes) Description
Numeric TINYINT -128 TO 127
[0 to 255 if UNSIGNED]
1 A very small integer.
Numeric SMALLINT -32,768 to 32,767
[0 to 65,535]
2 A small integer.
Numeric MEDIUMINT -8,388,608 to 8,388,607
[0 to 16,777,215]
3 A medium-sized integer.
Numeric INT -/+2.147E+9
[0 to 4.294E+9 if UNSIGNED]
4 A standard integer.
Numeric BIGINT -/+9.223E+18
[0 to 18.45E+18 if UNSIGNED]
8 A large integer.
Numeric FLOAT(p) p=0-24 --> "FLOAT"
p=25-53 --> "DOUBLE"
4 or 8 A single-precision floating-point number.
Numeric FLOAT[(M,D)] Min=+/-1.175E-38
Max=+/-3.403E+38
4 A single-precision floating-point number.
Numeric BIT Binary. Display by [add zero or converting with BIN()]. M=1-64 8 A bit field.

String Data Types

Type Name Range Description
String CHAR M=0-255 Characters, FIXED. Right padded with spaces A fixed-length non-binary (character) string.
String VARCHAR M=0-65,535 Characters
M=0-255
A variable-length non-binary string.
String BINARY M=0-255 Characters, FIXED. A fixed-length binary string.
String TINYBLOB 0-255 Characters A very small BLOB (binary large object).
String BLOB 0-65,535 Characters A small BLOB
String MEDIUMBLOB 0-16,777,215 Characters A medium-sized BLOB.
String LONGBLOB 0-4,294,967,295 Characters A large BLOB.
String TINYTEXT 0-255 Characters A very small non-binary string.
String TEXT 0-65,535 Characters A small non-binary string.
String MEDIUMTEXT 0-16,777,215 Characters A medium-sized non-binary string.
String LONGTEXT 0-4,294,967,295 Characters A large non-binary string.
String ENUM 1-65,5355 Characters Each column value may be assigned one enumeration member.
String SET Column is 0 or more values in list of 1-64 members Each column value may be assigned zero or more set members.

Date and Time Data Types

Type Name Range Storage(Bytes) Description
Date & Time DATE "1000-01-01" - "9999-12-31" 3 A date value in ‘CCYY-MM-DD’ format.
Date & Time TIME "-838:59:59"-
"838:59:59"
3 A time value in ‘hh:mm:ss’ format.
Date & Time DATETIME "1000-01-01 00:00:00" -
"9999-12-31 23:59:59"
8 A date and time value in ‘CCYY-MM-DD hh:mm:ss’ format.
Date & Time TIMESTAMP 19700101000000 -
2037+
4 A timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format.
Date & Time YEAR 1900 - 2155 1 A year value in CCYY or YY format.

Spatial Data Types

MySQL supports many spatial data types that contain various kind of geometrical and geographical values.

Type Name Description
Spatial GEOMETRY A spatial value of any type.
Spatial POINT A point (a pair of X Y coordinates).
Spatial LINESTRING A curve (one or more POINT values).
Spatial GEOMETRYCOLLECTION A collection of GEOMETRY values.
Spatial MULTILINESTRING A collection of LINESTRING values.
Spatial MULTIPOINT A collection of POINT values.