Summary: in this tutorial, you will learn about MySQL data types and how to use them effectively in designing database in MySQL.
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
The following table shows you the summary of numeric types in MySQL:
|TINYINT||A very 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|
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,
BOOL are synonyms for
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:
|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)|
|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 tutorial, you have learned various MySQL data types that help you determine which data type you should use for columns when you create tables.