Summary: In this tutorial, you will learn various MySQL data typesto use them effectively in database table design.
Database table contains multiple columns with specific data types such as numeric or string. MySQL provides you many more specific data types than just "numeric" or "string". Each data type in MySQL can be determined by the following characteristics:
- What kind of value it can represent.
- The space values take up and whether the values are fixed-length or variable-length.
- The values of a data type can be indexed.
- How MySQL compare values of that data types.
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 addtion, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except 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 |
| 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 LIKE clause or regular expression. The table below 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 and combination of date and time. In addition, MySQL also provide timestamp data type for tracking last change on a record. If you just want to store the year without date and month, you can use YEAR data type. Here is the table which showing MySQL date and type 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 support many spatial data types as below table which contains various kind of geometrical and geographical values.
| 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 |