Data-types in MySQL

In MySQL a data type defines, what kind of value a column can hold, weather it could be string, numeric, boolean, date/time or large object datatypes. MySQL supports a number of SQL Standard data types in various categories :

  • Numeric
  • String
  • DATE/TIME
  • Boolean
  • Large Object (LOB)

1. Numeric Datatypes : 

MySQL supports all standard SQL numeric data types which include Integer, float, numeric, decimal etc. The list of supported numeric values in MySQL is as follows :


Numeric Types Description
TINYINT A vary small integer. Supports -128 to 127 signed and 0 to 255 UNSIGNED.
SMALLINT A small integer. Support -32768 to 32767 signed and 0 to 65535 UNSIGNED.
MEDIUMINT A medium-sized integer. Supports -8388608 to 8388607 signed and 0 to 16777215 UNSIGNED.
INT A standared integer. Supports -2147483648 to 2147483647 signed and 0 to 4294967295 UNSIGNED.
BIGINT A large integer. Supports -9223372036854775808 to 9223372036854775807 normal and 0 to 18446744073709551615 UNSIGNED.
DECIMAL A fixed-point number.
FLOAT A single-precision floating point number.
DOUBLE A double-prefloating point number.
BIT A bit field.

2. String Datatypes :

In MySQL, a string can hold anything from plain  text to binary data such as images or files. The list of supported String values in MySQL is as follows :

String Types Description
CHAR(size) A fixed-length nonbinary (character) string. Upto 8000 characters.
VARCHAR(size) A variable-length non-binary string. Upto 8000 characters.
BINARY A fixed-length binary string.
VARBINARY A variable-length binary string.
TEXT(size) A small non-binary string with a maximum size of 65,535 characters.
TINYTEXT(size) A very small non-binary string with a maximum size of 255 characters.
MEDIUMTEXT A medium-sized non-binary string with a maximum size of 16,777,215 characters.

3. DATE/TIME Datatypes :

MySQL also provides date and time datatype as well as the combination of date and time. In addition to this, MySQL also supports timestamp data type for tracking the changes in a row of a table. The list of DATE/TIME datatype is as follows :

DATE/TIME Types Description
DATE A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
TIME TIME stores the time in a HH:MM:SS format.
DATETIME A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.
TIMESTAMP A timestamp between midnight, January 1st, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
YEAR A year value in CCYY or YY format.

4. Boolean :

MySQL does not have the built-in BOOL or BOOLEAN data type. To represent Boolean values, MySQL uses the smallest integer type TINYINT.

Boolean Types Description
BOOL { Synonym for TINYINT(1) } Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.
BOOLEAN { Synonym for TINYINT(1) } Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.

5. Large Object :

BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. The LOB Datatypes in MySQL is as follows :

LOB Datatypes Description
BLOB A Standard BLOB with maximumlength of 65535 characters.
TINYBLOB A very small BLOB.
MEDIUMBLOB A medium-sized BLOB with a maximum length of 16777215 characters. Also note that we do not have to specify a length with MEDIUMBLOB or MEDIUMTEXT.
LONGBLOB A BLOB with a meximum length of 4294967295 characters.

Next Topic :