Data types

A data type specifies the kind of information a column is intended to store and determines what operations can be performed on it. Choosing the smallest appropriate data type reduces storage requirements and improves query performance, since smaller rows fit more efficiently in memory and disk cache.

Data type categories

MySQL organizes data types into the following categories:

Character types

The two most common character types are CHAR and VARCHAR:

CHAR

CHAR stores fixed-length strings. All values stored in a CHAR(M) column occupy the same number of bytes regardless of actual length. Trailing spaces pad shorter values. Apply CHAR for columns with a consistent length, such as a two-character state code (CHAR(2)).

VARCHAR

VARCHAR stores variable-length strings. Storage equals the actual string length plus one byte to record the length. Apply VARCHAR for columns whose values vary in length from row to row.

Storage comparison with utf8mb4

The utf8mb4 character set uses up to four bytes per character. This affects CHAR more than VARCHAR:

Integer types

Integer types store positive or negative numbers without a decimal point. The following types are available, ordered by storage size:

INT is the most common choice because it accommodates a wide range of values with only 4 bytes of storage.

Two optional attributes modify integer behavior:

UNSIGNED

Prevents negative values from being stored, which doubles the positive range.

ZEROFILL

Sets UNSIGNED automatically and pads the displayed value with leading zeros up to the maximum display width. For example, INT has a maximum display width of 10.

BOOLEAN and BOOL are synonyms for TINYINT(1). The value 0 represents false and 1 represents true.

Fixed-point and floating-point types

DECIMAL

DECIMAL(M, D) stores exact numeric values. M specifies the total number of digits (precision, 1 to 65) and D specifies the digits to the right of the decimal point (scale, 0 to 30, cannot exceed M). Apply DECIMAL for monetary values and any calculation that requires exact precision.

Floating-point types

Floating-point numbers are approximate. Do not compare them with exact equality operators. See functions.md for how to search floating-point columns correctly.

Date and time types

Apply TIMESTAMP to track when a row was inserted or last updated. The following date and time types are available:

ENUM and SET types

Both types restrict column values to a predefined list of strings:

Apply ENUM when a column can hold exactly one value from a fixed set, such as a status field. Apply SET when a column can hold multiple values simultaneously, such as a set of permissions.

Large object types

Large object types store large amounts of character or binary data:

BLOB types

Store binary large objects such as images, audio, and video.

TEXT types

Store character large objects, sometimes called CLOBs. Apply these for long text content such as article bodies.

Data conversion

MySQL performs implicit conversions automatically when comparing or combining values of different types. To convert explicitly, apply the CAST or CONVERT functions.

CAST function

CAST(expression AS type) converts an expression to the specified type. The following cast types are available:

The following converts a date column to a string and a decimal column to an integer:

SELECT invoice_id, invoice_date, invoice_total,
   CAST(invoice_date AS CHAR(10)) AS char_date,
   CAST(invoice_total AS SIGNED) AS integer_total
FROM invoices;

To sort a string column that contains numeric values as if it were an integer column:

SELECT *
FROM string_example
ORDER BY CAST(emp_id AS SIGNED);

Alternatively, you can trigger an implicit conversion by adding zero:

SELECT *
FROM string_example
ORDER BY emp_id + 0;

CONVERT function

CONVERT provides the same functionality as CAST with an alternative syntax:

SELECT invoice_id, invoice_date, invoice_total,
   CONVERT(invoice_date, CHAR(10)) AS char_date,
   CONVERT(invoice_total, SIGNED) AS integer_total
FROM invoices;

FORMAT and CHAR functions

The following functions handle specialized conversion scenarios: