Introduction to Data Types

Discover the big five categories of data types that MySQL provides out of the box.

We'll cover the following

MySQL supports five data type categories: numeric, date and time, string, spatial, and JSON. In this lesson, we get a brief overview of these five categories using the example of a car model. Typically, we can think of several attributes to assign to a car model. For this example, we want to constrain ourselves to the model’s name, its power (in kilowatts), and the model’s year of release. In SQL, we can model these three attributes as the data types TEXT, INT, and YEAR, respectively. For simplicity, we do not want to consider fractional power. As such, modeling a car model as a table in SQL would look as follows:

Press + to interact
-- Generate a temporary table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TEMPORARY TABLE CarModel
(
    id             INT auto_increment,
    name           TEXT DEFAULT NULL,
    `power (kW)`   INT  DEFAULT NULL,
    `release year` YEAR DEFAULT NULL,
    PRIMARY KEY (id)
);

The table that represents a car model, named CarModel, consists of four columns, namely id, name, power (kW), and release year. While the first column, id, uniquely identifies a car model as the primary key, the three remaining columns correspond to the attributes that we elaborated on above.

Press + to interact
Hierarchy of data types in MySQL
Hierarchy of data types in MySQL

Numeric data types

Despite the numeric data type INT used for our car model, the numeric data types available in MySQL can be distinguished into four categories, namely bit-value, integer, fixed-point, and floating-point.

BIT(M) stores M-bit values where M ranges from 1 to 64. Literally, bit values are specified as b'value', B'value', or 0bvalue. For example, the value b'10' represents 2 in the decimal system. For integers, MySQL provides five data types, namely TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. They differ in the storage of bytes and the maximum value they provide, respectively. Like integers, the fixed-point type DECIMAL (synonymous with NUMERIC, DEC, and FIXED) stores exact numeric values preserving precision when needed, e.g., for monetary values. Conversely, the floating-point types FLOAT (synonymous with DOUBLE PRECISION) and DOUBLE (synonymous with REAL) store approximate numeric values with four bytes for single-precision and eight bytes for double-precision values.

Date and time types

In our car model, we use the data type YEAR to represent the model’s year of release. While convenient for this use case, MySQL provides several other data types enabling date and time modeling.

As YEAR only represents the year part of a date, there is DATE that enables the full representation of a date. As a date is also commonly associated with a time, DATETIME and TIMESTAMP allow us to combine a date with a concrete time. In case we are only interested in representing a point in time, MySQL also provides a data type for that use case, namely TIME.

String types

MySQL provides eight distinct data types to represent strings. We have already learned about one of them, namely TEXT. TEXT can hold a variable amount of data, similar to BLOB. In contrast, the latter data type stores binary strings, i.e., 0s and 1s. Complementary to TEXT/BLOB, there are CHAR/VARCHAR and BINARY/VARBINARY that store fixed- and maximum-length non-binary and binary strings, respectively. Both CHAR and VARCHAR are declared with a maximum length. However, CHAR enforces the maximum length as a fixed length by padding a shorter string with spaces to the right. In contrast to CHAR, VARCHAR is not padded for storage but trailing spaces are only retained during insertion to retrieve the stored value. The same holds for BINARY/VARBINARY with binary strings.

Spatial types

In our example of a car model, we have not used any spatial data type. However, we could easily extend our representation to record the geographic location of the factory where a car model is assembled by its manufacturer:

Press + to interact
-- Generate a temporary table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TEMPORARY TABLE CarModel
(
    id                  INT auto_increment,
    name                TEXT  DEFAULT NULL,
    `power (kW)`        INT   DEFAULT NULL,
    `release year`      YEAR  DEFAULT NULL,
    `assembly location` POINT DEFAULT NULL SRID 4326,
    PRIMARY KEY (id)
);
-- Create a car model with an assumed factory location at the Volkswagenwerk in Wolfsburg (Germany).
INSERT INTO CarModel (name, `power (kW)`, `release year`, `assembly location`)
VALUES ('Golf VII', 92, 2012, ST_PointFromText('POINT(52.4363146 10.7665937)', 4326));
-- Retrieving the location of a car model's assembly factor as latitude and longitude.
SELECT ST_X(`assembly location`) AS Latitude, ST_Y(`assembly location`) AS Longitude
FROM CarModel;

The snippet above consists of three parts:

  • This modeling of a car includes an assembly location at a factory as a column assembly location with the data type POINT. Particularly, we define a POINT of SRID 4326 as the assembly location. That is, we store the location as a point in the geographic reference systems known by the spatial reference identifier (SRID) 4326. That is, the geographic reference system also known as WGS 84 and commonly used in applications such as Google Maps (line 9).

  • We Insert a record into the CarModel table. This record represents a car model named “Golf VII” with specific attributes, including its assembly location. The location is defined using ST_PointFromText with coordinates pointing to the Volkswagenwerk in Wolfsburg, Germany (lines 14–15).

  • We retrieves the latitude and longitude of the car model’s assembly location by extracting the X and Y components of the POINT data, respectively. These components represent the longitude (X) and latitude (Y) of the assembly location (lines 18–19).

Apart from this example, MySQL supports eight different spatial data types for storing single values, such as LINESTRING, and collections of values such as MULTIPOLYGON.

JSON type

Out of the five categories of data types, JSON is of particular interest due to its common usage in web development. With MySQL’s native support for JSON, using the database system as part of a technology stack for web development becomes even more appealing.

The native support of JSON with the JSON data type implies that a JSON document is not stored as a string but interpreted and stored using an optimized storage format. As part of this support for JSON, we can create documents as we are used to from other programming languages using simple strings containing JSON or SQL functions such as JSON_ARRAY and JSON_OBJECT. For example, using the car model as defined in the introduction, we can also create a representation based on JSON documents:

Press + to interact
-- Generate a temporary table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TEMPORARY TABLE CarModel
(
    id  INT auto_increment,
    car JSON DEFAULT NULL,
    PRIMARY KEY (id)
);
-- Create a car model as a JSON document.
INSERT INTO CarModel (car) VALUES ('{ "name": "Golf VII", "power (kW)": 92, "release year": 2012 }');
-- Extract a car model's name from its JSON document.
SELECT car->"$.name" AS `Model Name` FROM CarModel;

In this representation of a car model, we can see that the corresponding SQL table only has two columns where car stores the JSON document with the actual attributes of a car model (line 6). We insert an exemplary car model as a JSON document into CarModel (line 11). With the selection of a car model’s name, we already get an idea about MySQL’s syntax for field selection of JSON documents (line 14).