Summary: In this chapter, we will learn about MySQL data types and how to use them effectively in designing database in MySQL.

MySQL Data Types

A 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:

The kind of values it represents.
The space that takes up and whether the values is a fixed-length or variable length.
The values of the data type can be indexed or not.
How MySQL compares the values 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 tables show you the summary of numeric types in MySQL:

Integer Types (Exact Value)

Numeric Types	Storage(Bytes)	Description
TINYINT	1	A very small integer
SMALLINT	2	A small integer
MEDIUMINT	3	A medium-sized integer
INT	4	A standard integer
BIGINT	8	A large integer

Floating-Point Types (Approximate Value)

Numeric Types	Description
FLOAT	A single-precision floating point number
DOUBLE	A double-precision floating point number
BIT	A bit field

Other Numeric Types

Numeric Types	Description
DECIMAL	A FIXED-POINT number
BIT	A bit field

Boolean data type

MySQL does not have built-in BOOLEAN or BOOL data type. Therefore, it uses the smallest integer type, TINYINT(1) to represent Boolean. In other words, BOOLEAN and BOOL are synonyms for TINYINT(1).

String data types

In MySQL, a string can hold anything from plain text to binary data such as images and files. The string can be compared and searched based on pattern matching by using the LIKE operator, regular expression, and full-text search.

The following table shows you the string data types in MySQL:

String Types	Description
CHAR	A fixed-length nonbinary (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 supports 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 kinds of geometrical and geographical values as shown in the following table:

Spatial Data 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

In this chapter, we have learnt various MySQL data types that help you determine which data type you should use for columns when you create tables.