MySQL Data Types

Summary: in this tutorial, you will learn about MySQL data types and how to use them effectively in the MySQL database design.
Database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:

• Kind of values it can represent.
• The space that takes up and whether the values are fixed-length or variable-length.
• Does the values of the data type can be indexed.
• How MySQL compares the value of a specific data type.

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. The following table shows you the summary of numeric types in MySQL:

Numeric Types

Description

TINYINT

A very small integer

SMALLINT

A small integer

SMALLINT

A small integer

MEDIUMINT

A medium-sized integer

INT

A standard integer

BIGINT

A large integer

DECIMAL

A fixed-point number

FLOAT

A single-precision floating-point number

DOUBLE

A double-precision floating-point number

BIT

A bit field

String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the string data types in MySQL:

String Types

Description

CHAR

A fixed-length non-binary (character) string

VARCHAR

A variable-length non-binary string

BINARY

A fixed-length binary string

VARBINARY

A variable-length binary string

TINYBLOB

A very small BLOB (binary large object)

BLOB

A small BLOB

MEDIUMBLOB

A medium-sized BLOB

LONGBLOB

A large BLOB

TINYTEXT

A very small non-binary string

TEXT

A small non-binary string

MEDIUMTEXT

A medium-sized non-binary string

LONGTEXT

A large non-binary string

ENUM

An enumeration; each column value may be assigned one enumeration member

SET

A set; each column value may be assigned zero or more set members

Date and Time Data Types

MySQL provides types for date and time as well as a combination of date and time. In addition, MySQL also provides timestamp data type for tracking the changes of a row in a table. If you just want to store the year without date and month, you can use YEAR data type. The following table illustrates the MySQL date and time data types:

Date And Time Types

Description

DATE

A date value in ‘CCYY-MM-DD’ format

TIME

A time value in ‘hh:mm:ss’ format

DATETIME

A date and time value in ‘CCYY-MM-DD hh:mm:ss’ format

TIMESTAMP

A timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format

YEAR

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 as shown in the following table:

Spatial Types

Description

GEOMETRY

A spatial value of any type

POINT

A point (a pair of X Y coordinates)

LINESTRING

A curve (one or more POINT values)

POLYGON

A polygon

GEOMETRYCOLLECTION

A collection of GEOMETRY values

MULTILINESTRING

A collection of LINESTRING values

MULTIPOINT

A collection of POINT values

MULTIPOLYGON

A collection of POLYGON values