Overview of all MySQL data types

Posted on 01.08.2014 | Last modified on 20.11.2016
The following information is for MySQL version 5.6

Data type Disk space Description
Numerical
TINYINT 1 Byte
  • Intergers
  • Range with sign: -128 to 127
  • Range without sign: 0 to 255
SMALLINT 2 Bytes
  • Intergers
  • Range with sign: -32.768 to 32.767
  • Range without sign: 0 to 65.535
MEDIUMINT 3 Bytes
  • Intergers
  • Range with sign: -8.388.608 to 8.388.607
  • Range without sign: 0 to 16.277.215
INT 4 Bytes
  • Intergers
  • Range with sign: -2.147.483.648 to 2.147.483.647
  • Range without sign: 0 to 4.294.967.295
BIGINT 8 Bytes
  • Intergers
  • Range with sign: -9.223.372.036.854.775.808 to 9.223.372.036.854.775.807
  • Range without sign: 0 to 18.446.744.073.709.551.615
DECIMAL varies
(more Info)
  • Fixed-point numbers (M, D)
  • The maximum number of digits (M) is 65 (default: 10), the maximum number of decimal places (D) is 30 (default: 0)
FLOAT 4 Bytes
  • Floating-point numbers
  • Allowed values are -3,402823466 x 1038 to -1,175494351 x 10-38, 0 and 1,175494351 x 10-38 to 3,402823466 x 1038
DOUBLE 8 Bytes
  • Floating-point numbers
  • Allowed values are -1,7976931348623157 x 10308 to -2,2250738585072014 x 10-308, 0 and 2,2250738585072014 x 10-308 to 1,7976931348623157 x 10308
REAL 8 Bytes
  • Synonym for DOUBLE
  • Exception: in SQL-Mode REAL_AS_FLOAT a synonym for FLOAT
BIT ca. (M+7)/8 Bytes
  • A bitfield type (M), which saves M Bits per value (default is 1, maximum is 64)
BOOLEAN 1 Byte
  • A synonym for TINYINT(1), a zero value is considered false, non-zero values are considered true
SERIAL 8 Bytes
  • An alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
Date and Clock
DATE 3 Bytes
  • Date
  • Supported range is 1000-01-01 to 9999-12-31
DATETIME 8 Bytes
  • Combination of date and clock
  • Supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP 4 Bytes
  • Timestamp
  • Supported range is 1970-01-01 00:00:01 UTC to 2038-01-09 03:14:07 UTC
  • Stored as the number of seconds since the UNIX epoch (1970-01-01 00:00:00 UTC)
TIME 3 Bytes
  • Clock
  • Supported range is -838:59:59 to 838:59:59
YEAR 1 Byte
  • Year
  • Four- (4, default) or two-digit (2) format
  • Allowed values are 70 (1970) to 69 (2069) or 1901 to 2155 and 0000
Strings
CHAR L Byte(s)
  • Strings with fixed length (0 to 255, default: 1) L
  • The right digits are filled up to the specified length with spaces
VARCHAR L+1 Byte(s)
  • Strings with variable length (0 to 65.535) L
  • The maximum length depends on the maximum number of rows
TINYTEXT L+1 Byte(s)
  • TEXT column with a maximum length of 255 characters
  • Stored with a one-byte prefix that specifies the length of the value in bytes
TEXT L+2 Byte(s)
  • TEXT column with a maximum length of 65.535 characters
  • Stored with a two-byte prefix that specifies the length of the value in bytes
MEDIUMTEXT L+3 Byte(s)
  • TEXT column with a maximum length of 16.777.215 characters
  • Stored with a three-byte prefix that specifies the length of the value in bytes
LONGTEXT L+4 Byte(s)
  • TEXT column with a maximum length of 4.294.967.295 or 4 GiB characters
  • Stored with a four-byte prefix that specifies the length of the value in bytes
BINARY L Byte(s)
  • Similar to the CHAR type, but stores binary byte strings instead of non-binary character strings
VARBINARY L+1 Byte(s)
  • Similar to the VARCHAR type, but stores binary byte strings instead of non-binary character strings
TINYBLOB L+1 Byte(s)
  • BLOB column with a maximum length L of 255 characters
  • Stored with a one-byte prefix that specifies the length of the value in bytes
BLOB L+2 Byte(s)
  • BLOB column with a maximum length of 65.535 characters
  • Stored with a two-byte prefix that specifies the length of the value in bytes
MEDIUMBLOB L+3 Byte(s)
  • BLOB column with a maximum length of 16.777.215 characters
  • Stored with a three-byte prefix that specifies the length of the value in bytes
LONGBLOB L+4 Byte(s)
  • BLOB column with a maximum length of 4.294.967.295 or 4 GiB characters
  • Stored with a four-byte prefix that specifies the length of the value in bytes
ENUM 1-2 Byte(s)
  • Enumeration
  • Selected from a list with a maximum of 65.535 values
SET 1, 2, 3, 4 or 8 Byte(s)
  • String objekt
  • A single value from a set with a maximum of 64 entries
Three-dimensional
GEOMETRY
  • Type which can save the geometry of any type
POINT
  • Point in the two-dimensional space
LINESTRING
  • Curve with linear interpolation between points
POLYGON
  • Polygon
MULTIPOINT
  • Points collection
MULTILINESTRING
  • Curve collection with linear interpolation between points
MULTIPOLYGON
  • Polygon collection
GEOMETRYCOLLECTION
  • Collection of geometry objects
Share this post