MySQL Data Types

In this article, we briefly introduced the data types in MySQL and how to use them.

The data type defines the data form of the column in the database table, such as strings and numbers that we often use. MySQL database supports many data types.

In this article, we’ll give a brief introduction to all the data types in MySQL database, and explain how to use them briefly.

There are several main data types in MySQL:

  • String data types
  • Numberic data types
  • date and time data types
  • binary data types
  • Geolocation data type
  • JSON data type

MySQL string data type

String is the most commonly used data type, it can be used to store a piece of text. For example, we need to use the string data type to store names, addresses, etc. In MySQL, strings can be stored in plain text or binary. MySQL provides many data types to store strings, the most commonly used are VARCHAR and CHAR.

The following table shows string data types in MySQL:

String type Description
VARCHAR A plain text string with variable length.
CHAR A plain text string with fixed length When the actual content is less than the defined length, MySQL will fill it with blanks.
VARBINARY A binary string with variable length.
BINARY A binary string with fixed length.
TINYTEXT A binary string, up to 255 bytes.
TEXT A binary string, up to 65K bytes.
MEDIUMTEXT A binary string, up to 16M bytes.
LONGTEXT A binary string, up to 4G bytes.
ENUM A Enumeration; each column value can be assigned a ENUM member
SET A Set; each column value can be assigned zero or more SET members

MySQL numeric data type

Numberic data type are also commonly used data types. If we want to store age, amount, etc., we need to use the numeric data types. MySQL supports all numeric types in the SQL standard, including integers and decimals.

The following table shows numeric types in MySQL:

Number type Description
TINYINT A very small integer, only 1 byte.
SMALLINT A small integer, up to 2 bytes.
MEDIUMINT A medium-sized integer, up to 3 bytes.
INT A standard integer, up to 4 bytes.
BIGINT A large integer, up to 8 bytes.
DECIMAL A fixed-point number.
FLOAT A single-precision floating point number, up to 4 bytes.
DOUBLE A double-precision floating point number, up to 8 bytes.
BIT A bit value.

MySQL date and time data types

MySQL provides a wealth of date and time types, including date (DATE) , time (TIME) , date and time (DATETIME) , timestamp (TIMESTAMP) and year (YEAR). The timestamp (TIMESTAMP) data type can be used to track changes in a row of a table.

The following table show date and time data types in MySQL:

Date and time types Description
DATE A date value CCYY-MM-DD format.
TIME A time value hh:mm:ss format
DATETIME A date and time value CCYY-MM-DD hh:mm:ss format.
TIMESTAMP A timestamp valueCCYY-MM-DD hh:mm:ss format.
YEAR A year value CCYY or YY format.

MySQL binary data type

In addition to strings, numbers, dates, etc., MySQL also supports storing binary data, such as image files. If you want to store a file, you need to use the BLOB type. BLOB is the abbreviation of binary large object,.

MySQL supports the following BLOB types with different sizes:

Binary types Description
TINYBLOB The maximum size is 255 bytes.
BLOB The maximum is 65K.
MEDIUMBLOB The maximum is 16M.
LONGBLOB The maximum is 4G.

MySQL Spatial Data Types

MySQL has spatial data types that correspond to OpenGIS classes.

MySQL supports many spatial data types that correspond to OpenGIS classes, as shown in the following table:

Spatial data type Description
GEOMETRY Geometry is the root class of the hierarchy. It can be any type of spatial value.
POINT A Point is a geometry that represents a single location in coordinate space.
CURVE A Curve is a one-dimensional geometry, usually represented by a sequence of points.
LINESTRING A LineString is a Curve with linear interpolation between points.
SURFACE A Surface is a two-dimensional geometry.
POLYGON A Polygon is a planar Surface representing a multisided geometry.
GEOMETRYCOLLECTION A GeometryCollection is a geometry that is a collection of zero or more geometries of any class.
MULTIPOINT A MultiPoint is a geometry collection composed of Point elements.
MULTICURVE A MultiCurve is a geometry collection composed of Curve elements. MultiCurve is a noninstantiable class.
MULTILINESTRING A MultiLineString is a MultiCurve geometry collection composed of LineString elements.
MULTISURFACE A MultiSurface is a geometry collection composed of surface elements.
MULTIPOLYGON A MultiPolygon is a MultiSurface object composed of Polygon elements.

JSON data type

MySQL has supported JSON data type. The native JSON data type provides the following advantages over JSON-formatted strings:

  • Automatic verification. MySQL automatically validates JSON documents stored in JSON columns, and invalid documents generate errors.
  • Best storage format. MySQL converts JSON documents stored in JSON columns to an internal format that allows fast reading of document elements.

MySQL Boolean data type

MySQL does not have a built-in boolean data type. However, MySQL supports the BOOLEAN and BOOL keywords, and MySQL converts BOOLEAN or BOOL to TINYINT(1). When we insert a TRUE or FALSE value, MySQL stores it as 1 or 0.

Conclusion

In this article, we gave an overview of the data types in MySQL. When you are creating a table, you can choose the appropriate data type for the columns according to your needs.

  1. MySQL VARCHAR

    In this article, we’ll take you through the VARCHAR data type.

  2. MySQL CHAR

    In MySQL, CHAR is a fixed-length string type.

  3. MySQL INT

    In this article, we’ll take you through the integer types in MySQL, which include INT, SMALLINT, TINYINT, MEDIUMINT and BIGINT.

  4. MySQL DATE

    In this article, we’ll introduce MySQL DATE data type and show you some useful date functions to work with date data efficiently.

  5. MySQL DATETIME

    In this tutorial, you’ll learn about MySQL DATETIME data type and how to use some handy functions for DATETIME efficient manipulation.

  6. MySQL YEAR

    In this tutorial, we will introduce MySQL YEAR data types, and the rules for using YEAR.

  7. MySQL ENUM

    In this tutorial, we will learn how to use MySQL ENUM data types to define columns that store enumeration values.