Menu

MySQL Data Types Overview

Browse MySQL string, numeric, date/time, binary, spatial, JSON, and Boolean types, with links to common definitions and examples.

Updated on

Choose a MySQL column type based on the values it must store and the required range, precision, and comparison behavior. The categories below link to common types and examples.

MySQL’s main data type categories include:

  • String data types
  • Numeric data types
  • Date and time data types
  • Binary string data types
  • Spatial data types
  • JSON data type

MySQL string data type

MySQL provides nonbinary character strings such as CHAR, VARCHAR, and TEXT, as well as binary byte strings such as BINARY, VARBINARY, and BLOB. Character strings use a character set and collation; binary strings are compared as bytes. The most common string types 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 fixed-length character string, right-padded with spaces when stored; trailing spaces are removed on retrieval by default.
VARBINARY A binary string with variable length.
BINARY A binary string with fixed length.
TINYTEXT Nonbinary text, up to 255 bytes; fewer characters fit with multibyte encodings.
TEXT Nonbinary text, up to 65,535 bytes; fewer characters fit with multibyte encodings.
MEDIUMTEXT Nonbinary text, up to 16,777,215 bytes; fewer characters fit with multibyte encodings.
LONGTEXT Nonbinary text, up to 4,294,967,295 bytes, subject to charset and transfer limits.
ENUM A string value chosen from a declared set of members.
SET Zero or more string values chosen from a declared set of members.

The TEXT size limits above are measured in bytes; the number of characters that fit depends on the column character set.

MySQL numeric data type

MySQL numeric types include integers, exact fixed-point values such as DECIMAL, and approximate floating-point values such as FLOAT and DOUBLE. Integer types are signed by default; UNSIGNED removes negative values and raises the nonnegative upper limit. For exact decimal values such as prices, use DECIMAL; floating-point types can have rounding error. See MySQL’s integer type ranges, numeric type rules, and fixed-point types.

The following table shows numeric types in MySQL:

Integer type Storage Signed range UNSIGNED range
TINYINT 1 byte -128 to 127 0 to 255
SMALLINT 2 bytes -32,768 to 32,767 0 to 65,535
MEDIUMINT 3 bytes -8,388,608 to 8,388,607 0 to 16,777,215
INT 4 bytes -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295
BIGINT 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615

Other numeric types have different precision and storage behavior:

Number type Choosing it
DECIMAL Exact fixed-point values; precision can be 1–65 digits and scale 0–30 digits.
FLOAT Approximate single-precision values; use when a small representation is more important than exact decimal equality.
DOUBLE Approximate double-precision values with more range and precision than FLOAT; still not exact decimal storage.
BIT A bit field from BIT(1) to BIT(64); it is not a replacement for general integer arithmetic.

MySQL date and time data types

Choose among MySQL’s date and time types by whether you need a date, a duration, or a time-zone conversion. The supported ranges and conversion behavior follow the MySQL date and time type reference.

The following table shows the main distinctions between MySQL date and time types:

Date and time type Use it for
DATE A calendar date without a time, from 1000-01-01 to 9999-12-31.
TIME A time of day or elapsed duration; values range from -838:59:59 to 838:59:59, with up to 6 fractional digits.
DATETIME A date and wall-clock time that is not converted through the session time zone; supported range is 1000-01-01 to 9999-12-31, with up to 6 fractional digits.
TIMESTAMP A date and time converted between the session time zone and UTC; range is 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC, with up to 6 fractional digits. It can also have explicit automatic initialization or update behavior.
YEAR A year from 1901 to 2155, or the special value 0000; values are displayed as four digits.

The TIME range and DATETIME/TIMESTAMP distinctions are documented in the official MySQL references for TIME, DATETIME and TIMESTAMP, and YEAR.

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 type Maximum value length
TINYBLOB 255 bytes
BLOB 65,535 bytes
MEDIUMBLOB 16,777,215 bytes
LONGBLOB 4,294,967,295 bytes

These are type limits, not a promise that a client can send a value of that size in one request. The actual transferable value is also limited by available memory and communication buffers, including max_allowed_packet on both client and server. See MySQL’s BLOB and TEXT reference.

MySQL Spatial Data Types

MySQL supports these spatial types in column definitions. GEOMETRY accepts any supported geometry; the other types restrict values to a geometry or collection:

Spatial data type Description
GEOMETRY Stores any supported geometry value.
POINT Stores a single point.
LINESTRING Stores a line made of one or more points.
POLYGON Stores a polygon with an exterior ring and optional interior rings.
GEOMETRYCOLLECTION Stores a collection of supported geometry values.
MULTIPOINT Stores a collection of points.
MULTILINESTRING Stores a collection of linestrings.
MULTIPOLYGON Stores a collection of polygons.

The OpenGIS geometry model also defines class names such as CURVE, SURFACE, MULTICURVE, and MULTISURFACE; these are not additional MySQL column types. See the MySQL spatial type reference for details.

JSON data type

The native MySQL JSON data type validates documents on insertion and stores them in an internal binary format that supports access to document elements without reparsing text. Compared with a JSON-formatted string column, it provides:

  • Validation. Invalid JSON documents are rejected when stored in a JSON column.
  • Binary storage. MySQL stores JSON in an optimized internal format for reading document elements.

MySQL Boolean data type

MySQL does not have a separate Boolean storage type: BOOLEAN and BOOL are aliases for TINYINT(1). For the difference between = TRUE and IS TRUE, and how to constrain values to 0/1, see the MySQL BOOLEAN reference.

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.

Advertisement

  1. MySQL JSON Data Type: Store and Query JSON

    Create a MySQL JSON column, insert valid documents, extract and aggregate values with JSON paths, and index a scalar path with a generated column.

  2. MySQL VARCHAR

    Understand how MySQL VARCHAR(M) counts characters, how the character set affects storage, and what happens when a value exceeds the limit.

  3. MySQL CHAR

    Learn how MySQL CHAR(M) counts characters, pads shorter values, handles trailing spaces, and treats overlength input in strict mode.

  4. MySQL INT

    Compare MySQL TINYINT through BIGINT signed and unsigned ranges, learn INT’s 4-byte limit, and see why display width does not limit values.

  5. MySQL DECIMAL

    Learn how MySQL DECIMAL and NUMERIC store exact decimal values, how precision and scale control range, and when to use them for monetary data.

  6. MySQL BIT

    Learn MySQL BIT(M)’s 1–64 bit width, write bit literals, store independent flags, and read values as numbers or binary strings.

  7. MySQL DATE

    Learn MySQL DATE’s supported range, two-digit year rules, SQL mode behavior for invalid dates, and common date functions.

  8. MySQL DATETIME

    MySQL DATETIME stores date and time with up to microsecond precision without retaining a time zone; compare it with TIMESTAMP and learn offset-input behavior.

  9. MySQL TIMESTAMP: Time Zones and Automatic Updates

    Learn MySQL TIMESTAMP’s UTC/session conversions, explicit offset literals since 8.0.19, fractional range, and automatic defaults and updates.

  10. MySQL TIME: Time of Day and Elapsed Durations

    Learn when MySQL TIME represents a clock time or duration, its range and fractional precision, and how it differs from DATETIME.

  11. MySQL YEAR

    MySQL YEAR stores 1901–2155 or 0000. Learn two-digit input conversion, the YEAR(2) limitation, and the YEAR(4) display-width deprecation.

  12. MySQL ENUM

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