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
The following table shows string data types in MySQL:
|A plain text string with variable length.
|A plain text string with fixed length When the actual content is less than the defined length, MySQL will fill it with blanks.
|A binary string with variable length.
|A binary string with fixed length.
|A binary string, up to 255 bytes.
|A binary string, up to 65K bytes.
|A binary string, up to 16M bytes.
|A binary string, up to 4G bytes.
|A Enumeration; each column value can be assigned a
|A Set; each column value can be assigned zero or more
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:
|A very small integer, only 1 byte.
|A small integer, up to 2 bytes.
|A medium-sized integer, up to 3 bytes.
|A standard integer, up to 4 bytes.
|A large integer, up to 8 bytes.
|A fixed-point number.
|A single-precision floating point number, up to 4 bytes.
|A double-precision floating point number, up to 8 bytes.
|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
|A date value
|A time value
|A date and time value
CCYY-MM-DD hh:mm:ss format.
|A timestamp value
CCYY-MM-DD hh:mm:ss format.
|A year value
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:
|The maximum size is 255 bytes.
|The maximum is 65K.
|The maximum is 16M.
|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
|Geometry is the root class of the hierarchy. It can be any type of spatial value.
|A Point is a geometry that represents a single location in coordinate space.
|A Curve is a one-dimensional geometry, usually represented by a sequence of points.
|A LineString is a Curve with linear interpolation between points.
|A Surface is a two-dimensional geometry.
|A Polygon is a planar Surface representing a multisided geometry.
|A GeometryCollection is a geometry that is a collection of zero or more geometries of any class.
|A MultiPoint is a geometry collection composed of Point elements.
|A MultiCurve is a geometry collection composed of Curve elements. MultiCurve is a noninstantiable class.
|A MultiLineString is a MultiCurve geometry collection composed of LineString elements.
|A MultiSurface is a geometry collection composed of surface elements.
|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
BOOL keywords, and MySQL converts
TINYINT(1). When we insert a
FALSE value, MySQL stores it as
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.
In this article, we’ll take you through the
CHARis a fixed-length string type.
In this article, we’ll take you through the integer types in MySQL, which include
In this article, we’ll introduce MySQL
DATEdata type and show you some useful date functions to work with date data efficiently.
In this tutorial, you’ll learn about MySQL
DATETIMEdata type and how to use some handy functions for
In this tutorial, we will introduce MySQL
YEARdata types, and the rules for using
In this tutorial, we will learn how to use MySQL
ENUMdata types to define columns that store enumeration values.